How to configure syslog-ng so that logged events are transformed into SQL queries that are then immediately executed in MySQL. Or... How to store syslog messages in a database.
At home I wanted to setup a centralized log repository with a nice web interface that would allow me to search through the logs. Since I have many different types of systems (a dedicated firewall box, a Windows XP workstation, a Debian Linux server and some virtual machines), the choice of log repository was easily made: syslog! To have a nice interface, I tweaked my syslog server so that all logs are also logged to a MySQL database. By using a simple PHP website, I can query the logs.
The rest of this document explains how I set up the central syslog server, the logging to the database, and the interface.
The syslog server
On my Debian Linux server, I decided to use syslog-ng. I choose this one because it allows me to tweak the configuration to log events to MySQL. In order to install syslog-ng, just use apt-get. apt-get will make sure that all other unneeded logging services are removed from your system (like syslogd and klogd): $ sudo apt-get install syslog-ng
In order to make your syslog-ng service also listening on your network interface, open the /etc/syslog-ng/syslog-ng.conf configuration file and find the definition of source s_all. Within this definition, uncomment the following line to listen on the default UDP syslog port 514: udp();
In order to have syslog-ng listening on another port or on only some of the IP addresses of the Linux server, add or change the following: udp(ip(192.168.20.4) port(1486));
tcp(ip(192.168.20.4) port(1486));
The previous lines will cause that your syslog-ng service is only listening for both udp as tcp connections on IP address 192.168.20.4 on port 1486 (port 1486 is the default port to which a Cisco PIX is sending syslog events). You can define multiple udp or tcp lines to listen on multiple IPs, ports or protocols. In order to listen on all IP addresses, us 0.0.0.0 as IP address.
Now restart the syslog-ng service in order to apply the changes: sudo /etc/init.d/syslog-ng restart
Logging to MySQL
In order to log syslog events to a MySQL table, you need 3 things:
- a MySQL database and table and an account who has write access in that table
- a syslog server that transforms the syslog events to SQL INSERT queries
- a script that runs the SQL queries on the database
In short we will create a FIFO datastructure in the Linux filesystem. We will configure the syslog-ng service to transform all events to SQL queries and to push these queries in the FIFO. We will then have a script that takes the FIFO as input and executes the queries on the database.
1. Creating the database, table and user
Use the following SQL commands to create a new database syslogng, with a table logs and a new user syslogng with password SyS10g-Ng!CREATE USER 'syslogng'@ 'localhost' IDENTIFIED BY 'SyS10g-Ng!';
GRANT USAGE ON * . * TO 'syslogng'@ 'localhost' IDENTIFIED BY 'SyS10g-Ng!'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE 'syslogng';
GRANT ALL PRIVILEGES ON `syslogng` . * TO 'syslogng'@ 'localhost';
CREATE TABLE `logs` (
`host` varchar(32) default NULL,
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`level` varchar(10) default NULL,
`tag` varchar(10) default NULL,
`date` date default NULL,
`time` time default NULL,
`program` varchar(15) default NULL,
`msg` text,
`seq` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `time` (`time`),
KEY `date` (`date`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
);
2. Creating the FIFO
Creating a FIFO in Linux is very easy. Just run the following command: $ sudo mkfifo /tmp/mysql.pipe
When the server is rebooted, the FIFO will not be automatically recreated. However, when the syslog-ng service will be restarted, it will try to write to the FIFO, but this FIFO will not exist. To solve this, just add the following line to somewhere in the beginning of the /etc/init.d/syslog-ng file: if [ ! -e /tmp/mysql.pipe ]; then mkfifo /tmp/mysql.pipe; fi
The previous command will check whether the FIFO already exists. If it doesn't, then the FIFO will be created. It's important to check whether the FIFO already exists, since we don't want to recreate the FIFO when you're only restarting your syslog-ng service.
3. Transforming events to SQL queries
In order to have syslog-ng transform syslog events into SQL queries, add the following to your syslog-ng configuration file:destination d_mysql {
pipe("/tmp/mysql.pipe"
template("INSERT INTO logs
(host, facility, priority, level, tag, date, time, program, msg)
VALUES
('$HOST', '$FACILITY', '$PRIORITY', '$LEVEL','$TAG', '$YEAR-$MONTH-$DAY'
, '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG');\n")
template-escape(yes));};
log { source(s_all); destination(d_mysql); };
Then restart your syslog-ng service: $ sudo /etc/init.d/syslog-ng restart
4. Executing the SQL queries
In order to automatically read queries from the FIFO and to execute them on the database, create the following script as /usr/local/bin/FIFOtoMYSQL.sh (or any other directory or name):#!/bin/bash
if [ ! -e /tmp/mysql.pipe ]
then
mkfifo /tmp/mysql.pipe
fi
while [ -e /tmp/mysql.pipe ]
do
mysql -u syslogng --password=SyS10g-Ng! syslogng < /tmp/mysql.pipe
done
You then need to run this script in the background, because it will not return the control to you: $ sudo sh /usr /local/bin/FIFOtoMYSQL.sh &
In order to have this script automatically started at boot time, I'm referring you to the following guide: Making scripts run at boot time with Debian (http://www.debian-administration.org/articles/28).
From now on, all the events sent to the syslog-ng service (either locally or over the network) will be logged into the MySQL database.
Different devices have different configuration options for logging to a syslog server, so I won't list them all here. For example, for a Cisco PIX, you need to use the 'logging host' directive. I advise you to consult the (online) manual or your product or to look for help on the internet.
For Windows, I can advise you the following tool: evtsys. This tool will run as a service and will sent all Windows events to your syslog server as syslog events. An installation manual is provided on the tools website (see https://engineering.purdue.edu/ECN/Resources/Documents/UNIX/evtsys ).
Analyzing the logs
To have a nice view on your logs, you can now create a simple PHP site that queries your database.
I have created a simple PHP page that show you an overview of the logs in your database (warning: if you have many logs and/or a slow machine it might take quite some seconds to compute the page!).
To use this site, download the ZIP file and extract it to a directory in you web server file structure. Open the file in your favorite editor and change the parameters to connect to your database (host, username, password and database). Then just open the page in your web browser and you'll see a result similar to the screenshots below.
The pages are easy adaptable, so you can adapt the view to your environment.