Centralized LOG with rSyslog recording in MySQL DB and Web UI for queries

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.

Prerequisites

• Instalação mínima do CentOS e RedHat 5.3

It may be interesting for you to use a web-based repository for Yum for this follow the instructions in this post

• CentOS 5: Utilizando repositório Yum da internet

Installing the necessary packages

# 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


Setting rSyslog

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

Note rSyslog uses the same syntax as syslog.conf. So, if you have a customized configuration you can copy syslog.conf over rsyslog.conf, just remember to rename the original rsyslog.conf to rsyslog.conf.orig just in case something goes wrong then you can rollback to the original one.

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.

# $ModLoad ommysql.so


Creating a MySQL database for rSyslog

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
# 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

*.*    :ommysql:localhost,Syslog,rsyslog,P45sword

This is the syntax:

1.:ommysql:database-server,database-name,database-userid,database-password

Where

• ommysql: is the plugin name
• database-server: MySQL db server address (tipicaly localhost)
• database-name: database
• database-userid: the user allowed to connect to MySQL
• database-password: user password

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


Allowing remote connections to rSyslog

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.


Installing the Web UI client for rSyslog

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

# wget http://www.virtualxp.org/downloads/phplogcon-2.6.4.tar.gz

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.
Click Next.



Ready! All okie dokie now!
Click “Finish!” and you will be redirected to the main screen for the phpLogCon.



Setting clients to log on Syslog server
On Linux clientes that you wanna log on a Syslog server, you must configure /etc/sysconfig.conf and add the following line:

*.*                   @syslog_server

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.