Jun 212013
 

The Goal

Have all syslogs from all servers shipped to a central server so that we can query them in one spot, and review old logs in the event of a compromise using only free software.

After looking at a number of options, I settled on rsyslog for the server, standard syslog on the clients (for now), LogAnalyzer as the UI for the web, both mysql backend for rsyslog and file based backend. The configuration for rsyslog will be a little different then most of the tutorials out there. I wanted to be able to query across all servers at one time, and LogAnalyzer only allows you to configure specific endpoints. With most configuration examples you will find on the web, they show you how to either dump it all into a database (which will no doubt get huge if you don’t clean it up) or dump each server to a single file which rotates daily, which is not ideal for a LogAnalyzer end point because your config has to change daily. This solution will dump all events into a database for all servers which will be configured as one realtime endpoint. rsyslog will also be configured to dump to a file, and this file will be rotated monthly using the usual logrotate scripts. Several archival (non-compressed) files will also be configured in LogAnalyzer for historical purposes.

Install rsyslog

I’m not going to go into this in detail. I’m going to assume you can already do this. But here are some useful notes:

  • My solution was built on RHEL5 using the packages available from yum. You will need the rsyslog package, as well as apache, php, and mysql for the database. I used link 2 below for this, although I didn’t remove any of the previous syslog packages. I just turned them off. Additionally, you only need to install rsyslog on your central server for this solution, not on each client.
  • I used this template line at the end of my rsyslog config: $template DailyPerHostLogs,”/var/log/LOGHOSTS/%HOSTNAME%/%HOSTNAME%.log”

Configure DB

I’m going to assume you have already installed the DB, but if not, here is a cheat sheet:

  • yum install mysql-server

Run this command (or create the database in another way):

  • mysql -uroot -p < /usr/share/doc/rsyslog-mysql-3.22.1/createDB.sql

Then create a user and password that can access the database from the host the server is on.

Configure rsyslog to log to both

Now this is where the magic happens. Provided you took some part of a sample config from somewhere, you should have something like this is your rsyslog:

$ModLoad imuxsock.so
$ModLoad imklog.so
$ModLoad imudp.so
#load the network stuff
$UDPServerAddress 0.0.0.0
$UDPServerRun 514
#reduce any duplicates
$RepeatedMsgReduction on
# The template that wil format the message as it is writen to the file
# you can edit this line if you want to customize te message format
$template TraditionalFormat,"%timegenerated% %HOSTNAME% %syslogtag%%msg:::drop-last-lf%\n"
# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
#*.info;mail.none;authpriv.none;cron.none                /var/log/messages
if \
        $source == 'YOUR.CENTRAL.SERVER' \
        and \
               $syslogseverity <= '6' \
        and ( \
                        $syslogfacility-text != 'mail' \
                and \
                        $syslogfacility-text != 'authpriv' \
                and \
                        $syslogfacility-text != 'cron' \
        ) \
then   /var/log/messages;TraditionalFormat
#authpriv.* /var/log/secure;TraditionalFormat
# The authpriv file has restricted access.
#authpriv.*                                              /var/log/secure
if \
        $source == 'YOUR.CENTRAL.SERVER' \
                and \
        $syslogfacility-text == 'authpriv' \
then    /var/log/secure;TraditionalFormat
# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
# mail.* /var/log/maillog;TraditionalFormat
if \
        $source == 'YOUR.CENTRAL.SERVER' \
                and \
        $syslogfacility-text == 'mail' \
then    /var/log/maillog;TraditionalFormat
 
# Log cron stuff
#cron.* /var/log/cron;TraditionalFormat
if \
        $source == 'YOUR.CENTRAL.SERVER' \
                and \
        $syslogfacility-text == 'cron' \
then    /var/log/cron;TraditionalFormat
# Everybody gets emergency messages
#*.emerg *
if \
        $source == 'YOUR.CENTRAL.SERVER' \
                and \
        $syslogseverity-text == 'emerg' \
then    *
# this line creates a template that will store the messages for each host in a seperate file.
# The same file will always be used, and should be rotated using a Log Rotate script.
$template DailyPerHostLogs,"/var/log/LOGHOSTS/%HOSTNAME%/%HOSTNAME%.log"
*.* -?DailyPerHostLogs;TraditionalFormat

What we need to add for the mysql logging is:

$ModLoad ommysql.so
*.*  :ommysql:localhost OR IP OF MYSQL SERVER,Syslog,USERNAME,PASSWORD

I added the above code to the very end of the file. Then I restarted rsyslog.

Then I configured a new entry in my config file for LogAnaylzer. If you haven’t already installed it yet, you can use the install wizard to set this up. But if you have installed it, you will need to use the config.php file to make changes. The sample included in the comments seemed a little off, so this is what I used:

$CFG['Sources']['Source2']['ID'] = "Source2";
$CFG['Sources']['Source2']['Name'] = "All Servers Realtime";
$CFG['Sources']['Source2']['Description'] = "";
$CFG['Sources']['Source2']['SourceType'] = SOURCE_DB;
$CFG['Sources']['Source2']['MsgParserList'] = "";
$CFG['Sources']['Source2']['DBTableType'] = "winsyslog";
$CFG['Sources']['Source2']['DBType'] = DB_MYSQL;
$CFG['Sources']['Source2']['DBServer'] = "localhost OR YOUR DB HOST";
$CFG['Sources']['Source2']['DBName'] = "Syslog";
$CFG['Sources']['Source2']['DBUser'] = "YOUR DB USERNAME";
$CFG['Sources']['Source2']['DBPassword'] = "YOUR DB PASSWORD";
$CFG['Sources']['Source2']['DBTableName'] = "SystemEvents";
$CFG['Sources']['Source2']['ViewID'] = "SYSLOG";

You can optionally configure a mysql database to store the configuration settings and manage user accounts. I eventually did this for our install, and it did make configuration much simpler.

Log Rotate and mysql Purge Script

It is a good idea to rotate the logs regularly and update the database to purge older entries.

Place this file in /etc/logrotate.d/ and call it rsyslog:

# Rotate each log monthly
/var/log/LOGHOSTS/*/*.log {
    monthly
    sharedscripts
    nocompress
#    extension .1
    rotate 4
    create 644 root root
    postrotate
         restart rsyslog >/dev/null 2>&1 || true
    endscript
}
#/var/log/LOGHOSTS/*/*.log.1 {
#    monthly
#    extension .2
#}
#/var/log/LOGHOSTS/*/*.log.2 {
#    monthly
#    extension .3
#}
/var/log/LOGHOSTS/*/*.log.3 {
    rotate 8
    monthly
    delaycompress
    maxage 365
}

EDIT: My log rotate script didn’t work as expected. Try this instead. Another option could be to reverse the order of the files above, handle .3 first, then .2…etc It seemed to work ok for the first month, then started added extra .# extensions and messed things up a bit.

To purge the database and keep your logs clean, just set up this script to run as a cronjob:

#!/bin/sh
# Special account to run this query only.
#MYSQL queries
RECORDSTODELETE=`mysql -u{MYSQL USERNAME} -p{MYSQL PASSWORD} --silent -e "SELECT COUNT(*) FROM Syslog.SystemEvents WHERE Syslog.SystemEvents.ReceivedAt &lt; DATE_SUB(NOW(), INTERVAL 30 DAY);&quot;`
RESULTS=`mysql -u{MYSQL USERNAME} -p{MYSQL PASSWORD} --silent -e &quot;use Syslog; DELETE from Syslog.SystemEvents WHERE Syslog.SystemEvents.ReceivedAt &lt; DATE_SUB(NOW(), INTERVAL 30 day);&quot;`
`logger -i Purged ${RECORDSTODELETE} records from logging database`

Odds and Ends

If you got this all to work, you may notice that your database based sources show more information than your file based sources. If you want facility and severity to display for text based entries, you will need to setup rsyslog to include this information in the file.

First, I’m using version 3.22.1 of rsyslog from the RHEL5 repos. Which is very old. I used this template to change what is logged to the file:

$template TraditionalFormatWithPRI,"%syslogfacility%.%syslogpriority%: %timegenerated% %HOSTNAME% %syslogtag%%msg:::drop-last-lf%\n"

I also added a new database template that separates the process ID from the syslog tag:

$template SQLWithProcessID,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag, ProcessID) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag:R,ERE,1,FIELD:([a-zA-Z\/]+)(\[[0-9]{1,5}\])*:--end%', '%syslogtag:R,ERE,1,BLANK:\[([0-9]{1,5})\]--end%')",sql

and then make a slight change to our database logging line:

*.*  :ommysql:localhost OR IP OF MYSQL SERVER,Syslog,USERNAME,PASSWORD;SQLWithProcessID

But you then have to change LogAnalyzer in order to read the extra data.

In the $APPROOT$/classes/logstreamlineparsersyslog.class.php your first four entries in the if block should look like this:

                // Standard syslog with process and facility/severity. TGH Apr. 4, 2013
                if ( preg_match("/(.*)\.(.*): (...)(?:.|..)([0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}) ([a-zA-Z0-9_\-\.]{1,256}) ([A-Za-z0-9_\-\/\.]{1,32})\[(.*?)\]:(.*?)$/", $szLine, $out ) )
                {
                        // Copy parsed properties!
                        $arrArguments[SYSLOG_FACILITY] = strtoupper($out[1]);
                        $arrArguments[SYSLOG_SEVERITY] = strtoupper($out[2]);
                        $arrArguments[SYSLOG_DATE] = GetEventTime($out[3] . " " . $out[4]);
                        $arrArguments[SYSLOG_HOST] = $out[5];
                        $arrArguments[SYSLOG_SYSLOGTAG] = $out[6];
                        $arrArguments[SYSLOG_PROCESSID] = $out[7];
                        $arrArguments[SYSLOG_MESSAGE] = $out[8];
                }
                // Standard syslog with process.
                elseif ( preg_match("/(...)(?:.|..)([0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}) ([a-zA-Z0-9_\-\.]{1,256}) ([A-Za-z0-9_\-\/\.]{1,32})\[(.*?)\]:(.*?)$/", $szLine, $out ) )
                {
                        // Copy parsed properties!
                        $arrArguments[SYSLOG_DATE] = GetEventTime($out[1] . " " . $out[2]);
                        $arrArguments[SYSLOG_HOST] = $out[3];
                        $arrArguments[SYSLOG_SYSLOGTAG] = $out[4];
                        $arrArguments[SYSLOG_PROCESSID] = $out[5];
                        $arrArguments[SYSLOG_MESSAGE] = $out[6];
                }
                // Sample (Syslog): syslog with facility.priority and no process - TGH Apr. 4, 2013
                else if ( preg_match("/(.*)\.(.*): (...)(?:.|..)([0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}) ([a-zA-Z0-9_\-\.]{1,256}) ([A-Za-z0-9_\-\/\.]{1,32}):(.*?)$/", $szLine, $out ) )
                {
                        // Copy parsed properties!
                        $arrArguments[SYSLOG_FACILITY] = strtoupper($out[1]);
                        $arrArguments[SYSLOG_SEVERITY] = strtoupper($out[2]);
                        $arrArguments[SYSLOG_DATE] = GetEventTime($out[3] . " " . $out[4]);
                        $arrArguments[SYSLOG_HOST] = $out[5];
                        $arrArguments[SYSLOG_SYSLOGTAG] = $out[6];
                        $arrArguments[SYSLOG_MESSAGE] = $out[7];
                }

                // Sample (Syslog): Mar 10 14:45:39 debandre syslogd 1.4.1#18: restart
                else if ( preg_match("/(...)(?:.|..)([0-9]{1,2} [0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2}) ([a-zA-Z0-9_\-\.]{1,256}) ([A-Za-z0-9_\-\/\.]{1,32}):(.*?)$/", $szLine, $out ) )
                {
                        // Copy parsed properties!
                        $arrArguments[SYSLOG_DATE] = GetEventTime($out[1] . " " . $out[2]);
                        $arrArguments[SYSLOG_HOST] = $out[3];
                        $arrArguments[SYSLOG_SYSLOGTAG] = $out[4];
                        $arrArguments[SYSLOG_MESSAGE] = $out[5];
                }

Once you put it all together, you should have solid log viewer for all of your servers at once, as well as access to historical logs.

Tutorials

Here are some helpful tutorials that I found useful:

  1. Install docs for LogAnalyzer: http://loganalyzer.adiscon.com/doc/install.html
  2. RHEL Install and config of rsyslog: http://www.howtoforge.com/building-a-central-loghost-on-centos-and-rhel-5-with-rsyslog
  3. rsyslog and mysql: http://www.dawnstar.id.au/linux-documentation/configuring-rsyslog-log-mysql-rhel-52/

  3 Responses to “Rsyslog + LogAnalyzer + Logging to MySQL and Files”

  1. My logrotate script didn’t work as expected. I edited the above post to try and correct it.

  2. Thank you for your post, it was a good help. I had some problems with the cronjob cleaning the database, so I changed some things and ended up with this(almost the same as yours):

    #!/bin/sh
    #MYSQL queries
    RECORDSTODELETE=`mysql -u {user} -p{password} –silent -e “SELECT COUNT(*) FROM Syslog.SystemEvents WHERE Syslog.SystemEvents.ReceivedAt < DATE_SUB(NOW(), INTERVAL 120 DAY);"`
    RESULTS=`mysql -u {user} -p{password} –silent -e "DELETE from Syslog.SystemEvents WHERE Syslog.SystemEvents.ReceivedAt < DATE_SUB(NOW(), INTERVAL 120 day);"`
    `logger -i Purged ${RECORDSTODELETE} records from logging database`

  3. Whenever i try to install rsyslog log analyzer “Could not find the configured table, maybe misspelled or the table names are case sensitive” this error will occur,even i corrected the table name.Although showing same error,can you assist me.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)