Centralized log management - Syslog-ng, Phpsyslog-ng and MySQL back-end

Should I split this blogtorial in different parts and spoon feed it or are my readers man (or woman) enough to just take this blog as a whole and tear it apart? I decided my readers are hungry enough to handle the beast that centralized Syslog management really is. If you follow this blogtorial step by step you will have a fully functioning centralized Syslog management with a database back-end to store the logs, PHP web GUI front-end to view the logs and a solid Syslog server collecting all of the data.

Here is what we will cover in this blogtorial: 

• Installing and configuring MySQL - This will house the database and the necessary tables to organize the logs.  
• Installing httpd/apache and PHP libraries - This will be the webserver to house the Syslog-ng web GUI front-end (phpsyslog-ng)
• Installing phpsyslog-ng - This will be the web based GUI to view the logs.
• Installing syslog-ng - This will be the app collecting the logs and sending it to MySQL. 

Grab some chips, salsa and your favorite beer or a glass of wine because it is going to be a long one but in the end you should have a robust centralized log management system.

I am using a server with the latest CentOS image. You might have to troubleshoot a few things if you use a different flavor of Linux but it should work.

First let's install the MySQL back-end, all of its dependencies and configure MySQL. If you already have MySQL installed then you can skip this step. 

 [root@localhost ~]# yum install mysql mysql-server  
 Installing:  
  mysql              x86_64   5.1.69-1.el6_4     updates   907 k  
  mysql-server       x86_64   5.1.69-1.el6_4     updates   8.7 M  
 Installing for dependencies:  
  perl                   x86_64   4:5.10.1-131.el6_4   updates   10 M  
  perl-DBD-MySQL         x86_64   4.013-3.el6          base      134 k  
  perl-DBI               x86_64   1.609-4.el6          base      705 k  
  perl-Module-Pluggable  x86_64   1:3.90-131.el6_4     updates   39 k  
  perl-Pod-Escapes       x86_64   1:1.04-131.el6_4     updates   31 k  
  perl-Pod-Simple        x86_64   1:3.13-131.el6_4     updates   211 k  
  perl-libs              x86_64   4:5.10.1-131.el6_4   updates   577 k  
  perl-version           x86_64   3:0.77-131.el6_4     updates   50 k  
 Updating for dependencies:  
  mysql-libs             x86_64   5.1.69-1.el6_4       updates   1.2 M  

Now let's setup MySQL.

 [root@localhost arwin]# /usr/bin/mysql_secure_installation  
 <!-- Follow the instructions. It will ask you to create a root password for the MySQL installation. 
      Remember this password as you will need it later on to setup the users and the database. --!>    

 <!-- Now let's create the database and name it syslog --!>
 <!-- The password is what you setup when you ran the 'mysql_secure_installation' --!>

[root@localhost arwin]# mysql -u root -p  
 Enter password:  
 Welcome to the MySQL monitor. Commands end with ; or \g.  
 mysql> create database syslog;  
 Query OK, 1 row affected (0.00 sec)  
 mysql> exit  
 Bye  
 [root@localhost arwin]#  

Let's now setup the apache/httpd webserver and PHP libraries so we can install the web front-end GUI. If you already have apache/httpd running then you can skip this step. I will not get into securing the httpd server as it is outside the scope of this blogtorial however, please do spend time on securing your web server.

 [root@localhost arwin]# yum install httpd httpd-tools  
 Installing:  
  httpd           x86_64    2.2.15-29.el6.centos    updates    821 k  
  httpd-tools     x86_64    2.2.15-29.el6.centos    updates    73 k  
 Installing for dependencies:  
  apr             x86_64    1.3.9-5.el6_2        base     123 k  
  apr-util        x86_64    1.3.9-3.el6_0.1      base      87 k  
  apr-util-ldap   x86_64    1.3.9-3.el6_0.1      base      15 k  
  mailcap         noarch    2.1.31-2.el6         base      27 k  
 [root@localhost arwin]# service httpd start  
 Starting httpd: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain for ServerName  
                               [ OK ]  
 [root@localhost arwin]# chkconfig httpd on  
 [root@localhost arwin]# yum install php php-gd http httpd-tools php-mysql.x86_64  
 Installing:  
  httpd           x86_64    2.2.15-29.el6.centos    updates    821 k  
  httpd-tools     x86_64    2.2.15-29.el6.centos    updates    73 k  
  php             x86_64    5.3.3-23.el6_4          updates    1.1 M  
  php-gd          x86_64    5.3.3-23.el6_4          updates    106 k  
  php-mysql       x86_64     5.3.3-23.el6_4         updates     81 k  
 Installing for dependencies:  
  apr              x86_64    1.3.9-5.el6_2             base       123 k  
  apr-util         x86_64    1.3.9-3.el6_0.1           base       87 k  
  apr-util-ldap    x86_64    1.3.9-3.el6_0.1           base       15 k  
  mailcap          noarch    2.1.31-2.el6              base       27 k  
  libedit          x86_64    2.11-4.20080712cvs.1.el6  base       74 k  
  php-cli          x86_64    5.3.3-23.el6_4            updates    2.2 M  
  php-common       x86_64    5.3.3-23.el6_4            updates    524 k  
  php-pdo          x86_64    5.3.3-23.el6_4            updates     75 k  
  freetype         x86_64    2.3.11-14.el6_3.1         updates    359 k  
  libX11           x86_64    1.5.0-4.el6               base       584 k  
  libX11-common    noarch    1.5.0-4.el6               base       192 k  
  libXau           x86_64    1.0.6-4.el6               base       24 k  
  libXpm           x86_64    3.5.10-2.el6              base       51 k  
  libjpeg-turbo    x86_64    1.2.1-1.el6               base       174 k  
  libpng           x86_64    2:1.2.49-1.el6_2          base       182 k  
  libxcb           x86_64    1.8.1-1.el6               base       110 k  

Time to get phpsyslog-ng installed. phpsyslog-ng is very 'customi-zable' but this should get you on your way.

If for some reason the file is not available on the FTP you can download it from here.

 [root@localhost arwin]# wget ftp://ftp.uwsg.indiana.edu/linux/gentoo/distfiles/php-syslog-ng-2.9.8m.tar.gz  
 [root@localhost arwin]# tar -xvf php-syslog-ng-2.9.8m.tar.gz  

Once you untar the file you will have 3 folders and I would recommend moving them into a one folder.

 [root@localhost arwin]# ll  
 total 6628  
 drwxr-xr-x. 9 root root  4096 Apr 14 2009 html  
 drwxr-xr-x. 3 root root  4096 Apr 14 2009 scripts  
 drwxr-xr-x. 2 root root  4096 Apr 14 2009 upgrades  
 [root@localhost arwin]#  
 [root@localhost arwin]# mkdir phpsyslog  
 [root@localhost arwin]# mv html/ phpsyslog/  
 [root@localhost arwin]# mv scripts/ phpsyslog/  
 [root@localhost arwin]# mv upgrades phpsyslog/  
 [root@localhost arwin]# ll  
 total 6620  
 drwxr-xr-x. 5 root root  4096 Sep 11 18:16 phpsyslog  
 [root@localhost arwin]# ll phpsyslog  
 total 12  
 drwxr-xr-x. 9 root root 4096 Apr 14 2009 html  
 drwxr-xr-x. 3 root root 4096 Apr 14 2009 scripts  
 drwxr-xr-x. 2 root root 4096 Apr 14 2009 upgrades  
 [root@localhost arwin]# mv phpsyslog/ /var/www/html/  
 <!-- Then set the permissions --!>  
 [root@localhost arwin]# chown apache:apache -R /var/www/html/phpsyslog/  

Now that we got it placed under httpd root, we can now configure it. Follow the screenshots and once finished we'll move to the syslog-ng configurations.

Open a browser and type http://<IPADDRESS>/phpsyslog/html/install/
IP ADDRESS or HOSTNAME of the phpsyslog-ng server.

 Then on to step 2 to accept the license agreement.

Step 3 is to fill out the necessary details for the MySQL back-end. The root password is the one we created earlier when we installed MySQL. We are also creating two more users on this page (syslogadmin and sysloguser). Please remember the password you assign to these users because we will need them later on.

Now we need to fill out the site details. So give the site a name and the rest of the httpd details. See below.

Once you click next you maybe asked to install CEMDB. This is up to you, but at this point you should be able to log into the web front-end with admin and the password. In my case the password was test123.

Obviously syslog-ng is not yet setup to send to the database so there are no data in the logs table. 

So now we move on to setting up syslog-ng to send to MySQL. Thanks to my colleague "PQizzle" :) I learned that syslog-ng natively supports sending logs to the database. I always thought you had to use fifo and data pipes, apparently that is no longer the case.

Install the repo needed to get syslog-ng which is in elrepo.

 [root@localhost ~]# wget http://linux.mirrors.es.net/fedora-epel/6/i386/epel-release-6-8.noarch.rpm  

If the link is down in the future you can download it from here.

 [root@localhost ~]# rpm -i epel-release-6-8.noarch.rpm  
 warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY  
 [root@localhost ~]# yum install --enablerepo=epel syslog-ng syslog-ng-libdbi.x86_64 libdbi-drivers libdbi-dbd-mysql  
 Installing:  
  syslog-ng                       x86_64                3.2.5-3.el6                  epel                440 k  
  syslog-ng-libdbi                x86_64                3.2.5-3.el6                  epel                31 k  
  libdbi-drivers                  x86_64                0.8.3-5.1.el6                base                372 k  
  libdbi-dbd-mysql                x86_64                0.8.3-5.1.el6                base                14 k  
 Installing for dependencies:  
  eventlog      x86_64     0.2.12-1.el6               epel      17 k  
  libnet        x86_64     1.1.5-1.el6                epel      54 k  
  libdbi        x86_64     0.8.3-4.el6                base      39 k  

Disable rsyslog and enable syslog-ng/start on bootup

 [root@localhost ~]# chkconfig rsyslog off  
 [root@localhost ~]# chkconfig syslog-ng on  
 [root@localhost ~]# service rsyslog stop  

Edit /etc/syslog/syslog-ng-conf and add these following lines. You can get pretty fancy here so read up on documentation.

 source s_sys {  
     file ("/proc/kmsg" program_override("kernel: "));  
     unix-stream ("/dev/log");  
     internal();  
     udp(ip(0.0.0.0) port(514));  
 };  
 # START CONFIG TO SEND TO DATABASE  
 destination d_mysql {  
 sql(  
 type(mysql)  
 ## User we created in the phpsyslog-ng web gui initial setup  
 username("syslogadmin")  
 ## Password we set in the phpsyslog-ng web gui initial setup  
 password("test123")  
 ## Database we created in the mysqld setup  
 database("syslog")  
 host("localhost")  
 table("logs")  
 columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")  
 values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")  
 indexes("datetime", "host", "program", "pid", "message")  
 );  
 };  
 log {source(s_sys); destination(d_mysql); };  
 # END CONFIG TO SEND TO DATABAE  

Now let's start syslog-ng and once you send some syslogs to the server you can view it the web front-end.

 [root@localhost syslog-ng]# service syslog-ng start  
 Starting syslog-ng:                    [ OK ]  
 [root@localhost syslog-ng]#  

I sent some syslogs to the servers from a router and as you can see it is showing up.

Few things you should consider setting up are logrotate (log retention period) and reloadcache.

By default log retention is configured for 30 days in /var/www/html/phpsyslog/html/config/config.php. Make sure to edit/modify the config.php if need be with the right database credentials.

Now set up a cron job to run every 30 days and reloadcache.php to run everyday at midnight.

Reloadcache.php is for reloading the hosts on the web-gui that send syslogs to it.

[root@localhost #] crontabe -e -u root

0 0 */30 * * php /var/www/html/phpsyslog/scripts/logrotate.php

00 00 * * * php /var/www/html/phpsyslog/scripts/reloadcache.php

There you have it -- MySQL back-end, phpsyslog-ng to view the logs and syslog-ng to collect the logs. Please let me know if you run into any issues.

Many more articles to come so stay tuned.

Please reshare/subscribe/comment/+1 if you like my posts as it keeps me motivated to write more and spread the knowledge.