Saturday, September 14, 2013

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.

9 comments:

  1. Hi,
    I am the author of PHP-syslog-ng. The version you have is extremely old and not able to scale beyond a million or so messages. A few years ago, I set out to rewrite it for better scalability and give the Web UI a much needed facelift. The result of that rewrite is now called LogZilla (http://www.logzilla.pro) and is able to scale to *billions* of events. There's still a free version available for smaller networks as well.
    I stopped supporting php-syslog-ng many years ago to focus on LogZilla, so I would ask that anyone wanting a much better tool as the result of many year of work to please consider using LogZilla instead of this old version of php-syslog-ng.
    Incidentally, I renamed it because it was no longer just a php-based interface. The UI is now mainly jQuery based :-)

    ReplyDelete
    Replies
    1. Hi

      First of all thank you for taking the time to write a wonderful product.

      I did check out your paid version of the product and it is very easy to use, intuitive and very well written.

      Readers,

      Whether you decide to use the free version which is limited to 10 devices, or the paid version which has unlimited functionality, or the free older version mentioned above -- consider donating because this is a great product.

      The free version of Php-Syslog-ng used in this blog has a button conveniently located on the top right of the web GUI to donate.

      Clayton - Thank you for your comment and keep up the great work!!

      Delete
  2. Hey Guys

    This looks great, is there a limit to the number of devices on the version in this blog? or only in the latest logzilla version?

    Thanks

    ReplyDelete
  3. No device limit on the version that was used on this blog.

    Thanks.

    ReplyDelete
  4. How add windows hosts in phpsyslog-ng can u explain me..

    ReplyDelete
  5. How to add windows hosts in physiology-ng in cantos

    ReplyDelete
    Replies
    1. You can try using NTSYSLOG for windows or http://www.intersectalliance.com/projects/BackLogNT/

      Delete
  6. Centralised log storing is certainly useful to maintain audit trails, but I would like to make a point that logs are more valuable when monitored as part of daily ops. The task of monitoring events from syslog is tedious: the larger the infrastructure the more overwhelming the volume of messages that are received.

    ReplyDelete
  7. Thank you for spending time on writing about Cisco Active Buffer Monitoring (especially the syslog alerts part) on a more recent post. I took the time to write you and your readers about my quest in Centralised Logging...

    Centralised log storing is great for keeping audit trails, but I would like to make a point here: Logs are even more valuable when monitoring of system/application events is part of daily operations.

    Frequent inspection of log messages creates better awareness of infrastructure activity, hardware failures, software bugs, misconfigurations, security threats, etc., all of which are usually captured by log mechanisms, long before any further implications take place.

    Nevertheless, this can be a tedious task to accomplish: Bear in mind, that the messages can be about anything related to activity in the infrastructure (normal or abnormal) and, inevitably, abnormal/critical messages are surrounded by large amounts of "noise" from normal messages, especially on busy systems.The bigger the infrastructure, the larger the volume of "noise" messages, making log monitoring a pretty time-consuming and monotonous task.

    In my efforts to solve the problem of logs going unnoticed on my network, I have come across a concept log processing method called "artificial ignorance" - a process whereby you throw away the log entries you know aren't interesting. If there's anything left after you've thrown away the stuff you know isn't interesting, then the leftovers must be interesting. That can certainly be applied to filter-out messages that are just the audit trail of normal operations and/or don't require administrator attention.

    - Tools that can run periodically and produce summary reports like logwatch and SEC (Simple Event Correlator) sure helped, but real-time metrics, alerting and monitoring was more fit to my needs, so I went on looking for other solutions, featuring a web interface with realtime views of my logs.
    - Syslog-ng can log filter messages through patterndb and log to a relational database, but there is no easy way of creating patterns to filter messages. I haven't tried the Logzilla web-ui commercial solution, as a limit of 10 is way low, but php-syslog-ng I can only imagine it as a centralised store (not efficient for monitoring busy servers with noisy logs).
    - Fortunately there is Echofish, a web interface that provides much of the desired functionality, although in a far simpler way. Patterns for artificial ignorance ("whitelists") are created from within the web-ui, using actions on the actual messages. After creating your initial set of patterns, messages are filtered in the database and all the messages that match your patterns end up in the logs archive. The rest of the messages (that weren't caught by the pattern filter) are queued for acknowledgement by the administrator. What's more: it comes with configuration templates for both syslog-ng and rsyslog.
    - ELSA (Enterprise Log Search And Archive) is definitely a good to have, especially when investigating old/archived logs. My plans are to add it back to the picture, but resources are limited ATM. In the near future, we see ELSA added as an extra log destination in syslogng.conf, to provide a web-ui to our logs for auditing purposes only, but daily ops monitoring is infeasible without employing modern log processing methods like artificial ignorance.

    regards,
    J

    ReplyDelete