Having a centralized logging is a prerequisite if you want to have your logs intacts. But having the events recorded in plain files is virtually impossible for queries.
Because of this, I will show you how to configure rsyslog to write events in MySQL DB and a web interface for queries with filters to facilitate viewing of the logs without have to access the console for such task.
The focus of this post is on CentOS / Red Hat, but it can be used in other distributions with a few modifications in the process.
It may be interesting for you to use a web-based repository for Yum for this follow the instructions in this post
yum install rsyslog.i386 rsyslog-mysql.i386 mysql-server php php-mysql php-gd httpd mod_ssl
Making sure that the services will start at boot
chkconfig --levels 35 rsyslog on chkconfig --levels 35 httpd on chkconfig --levels 35 mysqld on
As syslog comes by default with CentOS 5 / Red Hat 5, it is necessary to disable it because both of them uses the same port to listen for write request (UDP 514). rSyslog won’t start if syslog is active.
service syslog stop chkconfig --del syslog
To rSyslog have access to MySQL it’s necessary to load the ommysql plugin, add this line at the begin of the /etc/rsyslog.conf file.
Before everything, it’s needed to start MySQL service
service mysqld start
As root, lets create the access for the user from rSyslog to connect do database
mysql> CREATE USER 'rsyslog'@'localhost' IDENTIFIED BY 'P45sword'; mysql> GRANT ALL PRIVILEGES ON Syslog.* TO 'rsyslog'@'localhost' WITH GRANT OPTION;
Now, create the database that will be used by rSyslog
As root, run
mysql < /usr/share/doc/rsyslog-mysql-2.0.6/createDB.sql
Back to the file, /etc/rsyslog.conf add this line before the rsyslog directives
This is the syntax:
At the end of this process, the file /etc/rsyslog.conf will look like
$ModLoad ommysql.so *.* :ommysql:localhost,Syslog,rsyslog,P45sword *.info;mail.none;authpriv.none;cron.none /var/log/messages authpriv.* /var/log/secure mail.* -/var/log/maillog cron.* /var/log/cron *.emerg * uucp,news.crit /var/log/spooler local7.* /var/log/boot.log
Edit the /etc/sysconfig/rsyslog file and add the parameter -r at line 6
SYSLOGD_OPTIONS="-r -m 0"
It’s possible to start rsyslog right now, so you can see if everything gonna ok
service rsyslog start
Watch the log /var/log/messages, if everything is ok, a message from rsyslog starting may look like this one, without error
Sep 22 18:42:23 master rsyslogd: [origin software="rsyslogd" swVersion="2.0.6" x -pid="2779" x-info="http://www.rsyslog.com"][x-configInfo udpReception="Yes" udp Port="514" tcpReception="No" tcpPort="0"] restart Sep 22 18:42:23 master kernel: rklogd 2.0.6, log source = /proc/kmsg started.
With the rsyslog service ok, comes the part where we configure the web interface to make de filters, queries etc.
Download the frontend phpLogCon to your server
Extract to /usr/src
tar zxvf phplogcon-2.6.4.tar.gz -C /usr/src
Go to directory where you extracted it, in this case /usr/src/phplogcon-2.6.4 and copy the directory src to /var/www/html
cp -R src /var/www/html/syslog cd /var/www/html/syslog touch config.php chown apache config.php
If you haven’t started apache yet, you can do it now
service httpd start
Starting httpd: [ OK ]
Access your web browser pointing to server address http://172.20.32.205/syslog, replace the address with your server.
Note the error, it’s normal and awaited. Click in “here” to start configuring phpLogCon.
The installer will check the OS for the prerequisites to start the program. Click “Next”.
The test was done on the phpLogCon has write access to config.php file that we created and defined the permission. Click “Next” to continue.
On Basic Configuration, let the default options selected. Click “Next”.
This is the most important part, it’s here that will be configured the data sources from Syslog.
Change the field “Source Type” to “MySQL” and fill the fields on block “Database Type Options”, like the figure above.
Ready! All okie dokie now!
Click “Finish!” and you will be redirected to the main screen for the phpLogCon.
On Linux clientes that you wanna log on a Syslog server, you must configure /etc/syslog.conf and add the following line:
Where, “syslog_server” is the hostname or IP Address from syslog server.
With this done, you need to restart Syslog service on host client
service syslog restart
Those procedures are for Red Hat / CentOS servers clients. You should adapt to you distribution.
Please, leave a comment showing the procedure for your client! It will be very helpful.