Syslog to MySQL Update

I am in the process of updating some machines around here.
I am installing Mageia 4 Linux on one machine and it will be my syslog repository.

Mageia 4 is running journalctl and does not use syslog natively by default.
I had to install rsyslog, rsyslog-mysql, mariadb, and other required packages.

The first step is to modify the 00_ common.conf. Most of the lines matched what I needed but we needed to enable the UDP port to listen for other devices logging to this one.
 Uncomment the following two lines.

$ModLoad imudp.so
$UDPServerRun 514




Restart rsyslog to use the new settings.

systemctl rsyslog restart

Now run netstat -a and verify the the UDP port *.syslog or UDP port 514 is open.

Force some device that is configured to log to this server to do something to create and entry. I logged into a switch to get it to log. Then check the log file.

tail /var/log/syslog

If you have entries from the correct device you know that remote logging is working. Now to get the database part done.

rsyslog come with a default SQL schema in  /usr/share/doc/rsyslog-mysql/.
The file is called createDB.sql.

To setup the default database I ran:

mysql < createDB.sql

 This creates a database called Syslog with two tables.

Then I created a new user and gave it privileges to the Syslog database:

GRANT ALL PRIVILEGES ON Syslog.* To 'Syslogusername'@'localhost' IDENTIFIED BY 'password';

The rsyslog config file /etc/rsyslog.conf calls config files in two other locations.

One location is /etc/rsyslodg.d/ and the file 01_mysql.conf needs to have two lines:

$ModLoad ommysql.so
 

*.*     :ommysql:127.0.0.1,Syslog,Syslogusername,password

The first line loads the mysql library and the second line has the database name and the user credentials.

Another restart of the rsyslog service and your entries should now start being logged into the database.

SELECT * FROM Syslog.SystemEvents; and check for the entries. 

Syslog to MySql on openSUSE

I have been experimenting with openSUSE lately as I am not impressed with the direction Mandriva Linux is going. Mandriva has been my main distribution since early versions of Mandrake back around 1998.

I followed the steps in my earlier post but I had to fiddle with a few more things to get it running on openSUSE.

In /etc/rsyslog.conf I added:

$ModLoad imudp         # load udp server
$UDPServerRun 514      # start udp server

the UDP server was not running by default so I added it and poked a hole in the firewall so that I could log the router messages to the database.

Syslog to MySQL

I had a need to put Syslog data into a database for further analysis.
Using one of my linux boxes this turned out to be much easier than I thought, at least for basic functionality.

Basically I followed the instructions at http://www.rsyslog.com/doc/rsyslog_mysql.html
 and everything just worked.

I already had MySQL installed and Rsyslog was the default syslog on my Linux distribution so I used the included script to create the database, then I created the account.

mysql -u root -p < /usr/share/doc/rsyslog-mysql/createDB.sql

grant all privileges on Syslog.* to 'Syslog'@'%' identified by 'password' with grant option;

Then I modified the rsyslog config file.

#put this line as the first line in /etc/rsyslog.conf
$ModLoad ommysql

#this line logs everything
*.*       :ommysql:127.0.0.1,Syslog,Syslog,password
#*.*       :ommysql:127.0.0.1,db-name,db-user,db-password

Then I restarted the Rsyslog process.
/etc/rc.d/init.d/rsyslogd restart

Then I issued a 'SELECT * FROM Syslog.SystemEvents;'


I saw that the local machine was logging all of its syslog data and the other devices that were logging to the syslog server were also showing up in the database. As a point of interest, all of the data was still being put in the regular syslog file as well.

Future work on this will be to tailor what gets logged and some possible customizations.