syslog-ng is my hero. No, really.

No doubt that syslog-ng is cool when used to concentrate all logs from your IT infrastructure in the same place. But having just a bunch of ASCII files is not the most practical system.

I know what you are thinking right now: “It would be cool to log to a database and have some kind of front end to visualize it”. Yes, it would be… actually it IS.

Just a quick description of how to do it (the details are boring :))

The trick is to modify the later configuration (check syslog-ng is my hero) and add a new destination clause:

# Destination MySQL

destination d_mysql {
pipe(“/tmp/mysql.syslog-ng.pipe”
template(“INSERT INTO logs
(host, facility, priority, level, tag, datetime, program, msg)
VALUES ( ‘$HOST’, ‘$FACILITY’, ‘$PRIORITY’, ‘$LEVEL’, ‘$TAG’, ‘$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC’,
‘$PROGRAM’, ‘$MSG’ );n”) template-escape(yes));
};

This associates a named pipe, in our example located in /tmp/mysql.syslog-ng.pipe but the name and location are not important, to a MySQL statement. This SQL sentence has been crafted in this particular way for reasons that will be apparent later. This new destination must be used for every logging source we want to include in our database, modifying the configuration like this:

log{
source(src);
filter(f_MUNICH);
destination(d_mysql);
};

That says to syslog-ng to redirect the log stream to this pipe which, in turn, will insert the data into this MySQL table using the forementioned INSERT statement. Just a hint about this, we need to create the pipe under Linux with this command:

mkfifo /tmp/mysql.syslog-ng.pipe

and then connect it to the MySQL database as follows:

mysql –user=myuser –password=mypasswordhere logs < /tmp/mysql.syslog-ng.pipe

that is, just a regular flow redirection.
Even better, and recommended is to setup a script to do this everytime you boot your system, something like:

#!/bin/sh
#
# File: syslogng-mysql-pipe.sh
#
# Take input from a FIFO and run execute it as a query for
# a mysql database.
#
# IMPORTANT NOTE: This could potentially be a huge security hole.
# You should change permissions on the FIFO accordingly.
#

if [ -e /tmp/mysql.syslog-ng.pipe ]; then
while [ -e /tmp/mysql.syslog-ng.pipe ]
do
mysql -usyslog –password=mypasswordhere logs < /tmp/mysql.syslog-ng.pipe
done
else
mkfifo /tmp/mysql.syslog-ng.pipe
fi

After having this running we can begin to install the PHP frontend that will read and interpret the records in this database. After all, if plain text files weren’t very practical, a SQL database is even worse!

I won’t talk here about it because there’s an online guide much better than any explanation I could write here.
In case you want to check it out before going into all this process, there’s a live demo here. The user/pass are “demo/demo”.

Happy logging! ;)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s