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.

[singlepic id=39 w=320 h= float=] [singlepic id=40 w=320 h= float=]

Prerequisites

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

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:

   :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.

Screenshot

Note the error, it’s normal and awaited. Click in “here” to start configuring phpLogCon.

Screenshot-1

The installer will check the OS for the prerequisites to start the program. Click “Next”.

Screenshot-2

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.

Screenshot-3

On Basic Configuration, let the default options selected. Click “Next”.

Screenshot-4

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.

Screenshot-5

Ready! All okie dokie now!

Click “Finish!” and you will be redirected to the main screen for the phpLogCon.

[singlepic id=38 w=640 float=]

Setting clients to log on Syslog server

On Linux clientes that you wanna log on a Syslog server, you must configure /etc/syslog.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.

Share this post

19 comments

Join the conversation
  • Pingback: Ardoros (7k9039) 's status on Wednesday, 30-Sep-09 09:33:54 UTC - Identi.ca

  • piavlo - 30/09/2009 reply

    How does mysql scales then there are hundreds
    of syslogs continuously streaming everything *.* to the sql database?
    I doubt that any SQL database is appropriate for text based logging.
    A flat file database should be more suitable.
    Maybe a MySQL CSV Storage Engine could be a good solution http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html

    Daniel Kühl Lima - 30/09/2009 reply

    Hi piavlo,

    You got a point. Sure MySQL CSV engine should be a very elegant solution in a case you have a huge data stream. But in most cases, this setup can handle the data.

    But with some MySQL tunning it can be good to handle such great data stream.

    Thanks for your comment! I really appreciate it.

  • pavlidis - 01/10/2009 reply

    Nice indeed, but another thing not mentioned is required too. In flat files we have logrotate, in that case there must be some sql scripts run thru cron to delete entries in mysql database, otherwise it will overwhelm the server easily. So in companion to this, there must be the procedure used to truncate the entries below the required maximum

  • Martinez.df - 19/11/2009 reply

    I followed the manual posted here, but appeared the following message:

    No syslog records found – Error Details:

    No syslog records found

    Anybody help me?

    Daniel Kühl Lima - 19/11/2009 reply

    It appears that the rSyslog haven’t any entry.

    Did you put into yours remote server the following line:

    *.* @syslog_server

    in /etc/syslog.conf and restarted the service?

    Alessandro Martinez - 01/12/2009 reply

    Obrigado pela dica. Fiz uma nova instalação e funcionou. Havia algum problema na anterior.

  • fooboo - 18/03/2010 reply

    I followed this process and it all seemed to be working right up to the finish but when it redirected me to index.php the page was blank.

    Have you any idea why this might be or what I can check to get it serving properly. It seems to be adding entries to the database but it’s not serving the webpage properly so I can’t view them.

    Daniel Kühl Lima - 19/03/2010 reply

    Hi,

    Check your Apache’s log, /var/log/httpd/error_log, it will tell you what’s wrong when you access the index.php.

    Also, did you installed the PHP prerequisites?

    fooboo - 19/03/2010 reply

    I have all the prerequisites. I actually already had them installed as this server has been running for a while now but when this site wouldn’t run I upgraded to the latest version of the prerequisites just to be sure that it wasn’t because I was running an older version of something.

    The error_log doesn’t have an errors listed for this site. I ran tail on the log and then opened the site up and it doesn’t create any new entries. The page is blank and when I view the source there’s nothing there so maybe it thinks it’s serving a blank page correctly?

    fooboo - 22/03/2010

    Could it be because I have installed it in a subdomain directory just for this app. rather than /var/html/www/syslog?

    Does it check what directory it’s in or does it expect to be in /var/html/www/syslog?

    I use ISPConfig to manage my websites on this server so nothing gets served out of the /var/html/www directory. All the site have their own root folder including this site that it served properly out of when I ran the install scripts.

    Daniel Kühl Lima - 22/03/2010

    Absolutely!

    You have to config vhost for apache to access the directory for phplogcon other than /var/www/html/syslog or make an alias for that, for example:

    Alias /syslog /usr/share/syslog

    :)

    fooboo - 22/03/2010

    I don’t have any problem accessing the folder. I created a subdomain (log..com) and put the files in that rather than a /syslog directory.

    I could access it fine for the install process it’s just post-install that the page is blank. Is it specifically looking for a folder called /syslog or is it ok as the root files of a domain?

    fooboo - 22/03/2010

    I already have a vhost for the subdomain and it’s serving fine.

  • Daniel Kühl Lima - 19/03/2010 reply

    On the last part, “setting clients to log on rsyslog server” there were a typo, where it was “/etc/sysconfig.conf” it should be “/etc/syslog.conf”.

    I apologize for that.

  • fabricio - 15/04/2010 reply

    Hi, thanks a lot for this manual, is very simple and easy, it works exelent.
    i have some question:

    1.- in the configuration on the client, can i restrict with login and password or source device ??

    2.- the trafic between client and server is in plain text, can i do this trafic encrypted ??

    thanks, i’ll be waiting for the answer, byeee

    Daniel Kühl Lima - 14/05/2010 reply

    Hi there.

    1. Sorry, I do not have this information, but I’m pretty sure that this isn’t possible.

    2. I do not need the use of encrypt capacity because I’m on a local network, but I should use a VPN solution for that case, if I got a device out of my local network for example.

    Cya

Join the conversation