I have been experimenting with ways to better manage the logs my servers generate. Depending on who you ask, folks will recommend sending your logs to a remote syslog server that writes the logs to disk, some may recommend sending it to a log analysis tool similar to splunk, and others would recommend feeding it to a SQL database. I’ve talked before about setting up syslog-ng for remote logging, and in this case I wanted to experiment with something new. I also didn’t have money to buy a tool like splunk, so I decided to start experimenting with funneling syslog data into a MyQSL database.
Setting up syslog to write messages to a MySQL database is crazy easy to do on CentOS 6. The built-in syslog daemon (rsyslog) has database plug-ins for several opensource databases, which can be installed with the yum package manager:
$ yum install rsyslog-mysql
Once the plug-in is installed you can run the provided createDB.sql script (this is part of the rsyslog-mysql package) to create a database (the default database will be named Syslog, though you can edit the createDBL.sql file if you want to call it something else) as well as the tables the log entries will be stored in:
$ rpm -q -l rsyslog-mysql-4.6.2-12.el6.x86_64 | grep createDB.sql
/usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql
$ mysql -u root -h localhost --password
mysql> **source /usr/share/doc/rsyslog-mysql-4.6.2/createDB.sql**
If this completes successfully you should have two brand spanking new
tables:
mysql> use Syslog;
mysql> show tables;
+------------------------+
| Tables_in_Syslog |
+------------------------+
| SystemEvents |
| SystemEventsProperties |
+------------------------+
The SystemEvents table is where log data is stored and has the following
structure:
mysql> desc SystemEvents;
+--------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| CustomerID | bigint(20) | YES | | NULL | |
| ReceivedAt | datetime | YES | | NULL | |
| DeviceReportedTime | datetime | YES | | NULL | |
| Facility | smallint(6) | YES | | NULL | |
| Priority | smallint(6) | YES | | NULL | |
| FromHost | varchar(60) | YES | | NULL | |
| Message | text | YES | | NULL | |
| NTSeverity | int(11) | YES | | NULL | |
| Importance | int(11) | YES | | NULL | |
| EventSource | varchar(60) | YES | | NULL | |
| EventUser | varchar(60) | YES | | NULL | |
| EventCategory | int(11) | YES | | NULL | |
| EventID | int(11) | YES | | NULL | |
| EventBinaryData | text | YES | | NULL | |
| MaxAvailable | int(11) | YES | | NULL | |
| CurrUsage | int(11) | YES | | NULL | |
| MinUsage | int(11) | YES | | NULL | |
| MaxUsage | int(11) | YES | | NULL | |
| InfoUnitID | int(11) | YES | | NULL | |
| SysLogTag | varchar(60) | YES | | NULL | |
| EventLogType | varchar(60) | YES | | NULL | |
| GenericFileName | varchar(60) | YES | | NULL | |
| SystemID | int(11) | YES | | NULL | |
+--------------------+------------------+------+-----+---------+----------------+
The column descriptions are pretty much self explanatory, and you can reference the rsyslog documentation to get more specifics on the purpose of each column. In order for rsyslog to be able to write to the database, you will need to create a user (using root is not recommended) and grant the privileges to allow the user to INSERT new data. For my purposes I created an rsyslog user and restricted them to INSERT’ing data into just the Syslog database:
mysql> grant INSERT on Syslog.* to rsyslog identified by "PASSWORD_HERE";
This completes the MySQL configuration. To tell rsyslog to start sending messages to the Syslog database, you will need to add directives similar to the following to /etc/rsyslog.conf. You will also need to restart the rsyslog service:
$ ModLoad ommysql
The first line loads the MySQL plug-in, and the second line tells rsyslog to send all of the log entries (you can pair this down to specific facilities and priorities) to the Syslog database on rsyslogdb.prefetch.net. It will use the provided user (rsyslog in this example) and password (PASSWORD_HERE in this example) to login to the database. If everything worked as expected you should be able to view the log entries in the SystemEvents table:
$ mysql -u root -h localhost --password
mysql> use Syslog;
mysql> select blog blog-posts blog-posts.orig cleanup drafts prefetch.net from SystemEvents limit 5;
+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
| ID | CustomerID | ReceivedAt | DeviceReportedTime | Facility | Priority | FromHost | Message | NTSeverity | Importance | EventSource | EventUser | EventCategory | EventID | EventBinaryData | MaxAvailable | CurrUsage | MinUsage | MaxUsage | InfoUnitID | SysLogTag | EventLogType | GenericFileName | SystemID |
+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
| 1 | NULL | 2012-02-11 15:42:01 | 2012-02-11 15:42:01 | 0 | 6 | centos6-1 | imklog 4.6.2, log source = /proc/kmsg started. | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | kernel: | NULL | NULL | NULL |
| 2 | NULL | 2012-02-11 15:42:01 | 2012-02-11 15:42:01 | 5 | 6 | centos6-1 | [origin software="rsyslogd" swVersion="4.6.2" x-pid="3891" x-info="http://www.rsyslog.com"] (re)start | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 3 | NULL | 2012-02-11 15:42:25 | 2012-02-11 15:42:25 | 0 | 6 | centos6-2 | imklog 4.6.2, log source = /proc/kmsg started. | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | kernel: | NULL | NULL | NULL |
| 4 | NULL | 2012-02-11 15:42:25 | 2012-02-11 15:42:25 | 5 | 6 | centos6-2 | [origin software="rsyslogd" swVersion="4.6.2" x-pid="5932" x-info="http://www.rsyslog.com"] (re)start | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | rsyslogd: | NULL | NULL | NULL |
| 5 | NULL | 2012-02-11 15:42:22 | 2012-02-11 15:42:22 | 1 | 5 | centos6-1 | test | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | matty: | NULL | NULL | NULL |
+----+------------+---------------------+---------------------+----------+----------+-----------+--------------------------------------------------------------------------------------------------------+------------+------------+-------------+-----------+---------------+---------+-----------------+--------------+-----------+----------+----------+------------+-----------+--------------+-----------------+----------+
5 rows in set (0.00 sec)
Awesome! Once you have all of your hosts pointing to your database you can use the power of SQL to sift through the data and correlate events (I wonder how useful this would be when applied to digital computer forensics). You can also use tools like LogAnalyzer to visualize your log data. I’ve thought of hundreds of things I can do with my log data, I just need to spend some time coding them up! :)