Configuring syslog-ng to store logs into a MySQL database

This short article describes how to configure syslog-ng in order to store the logs into a MySQL backend. This adds more flexibility when performing log analysis, log searching and correlation.

Installing MySQL

MySQL can be compiled from source and installed using the FreeBSD ports collection:

# cd /usr/ports/databases/mysql41-server/ # make install distclean

Configuring MySQL

Use the following commands to create the MySQL database directory and install a configuration file:

# mkdir /var/db/mysql # cp /usr/local/share/mysql/my-small.cnf /var/db/mysql/my.cnf

Optionally, edit /var/db/mysql/my.cnf to adjust some parameters like:

  • Disabling networking support.

    This can be done by uncommenting the skip-networking option from the configuration file.

    This will prevent MySQL from listening on port 3306/tcp. Since we are using MySQL locally, we can use UNIX sockets instead of true networking.

  • Reducing memory usage.

    By adjusting innodb_buffer_pool_size and innodb_additional_mem_pool_size to values suited to the amount of RAM available to FreeBSD

Next, add the following lines into /etc/rc.conf so that MySQL will get launched during system startup:

# MySQL mysql_enable="YES" mysql_limits="YES" mysql_dbdir="/var/db/mysql" mysql_args=""

Starting MySQL

Use the following command to start MySQL:

# /usr/local/etc/rc.d/ start

Creating the MySQL database

The logs will get stored into a table named logs on database syslog.
To create the database and table, create a file named syslog.sql with the following SQL commands:

CREATE DATABASE syslog; USE syslog; 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, timestamp datetime default NULL, program varchar(15) default NULL, msg text, seq int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (seq), KEY host (host), KEY seq (seq), KEY program (program), KEY timestamp (timestamp), KEY priority (priority), KEY facility (facility) );

Then, process those SQL commands using MySQL client:

# mysql -u root -p <syslog .sql

Setting up the communication channel

syslog-ng will issue INSERT INTO SQL commands into a UNIX pipe for every log received and processed. Those SQL commands will be retrieved from the UNIX pipe and will be injected into MySQL.

This UNIX pipe will act as the communication channel between syslog-ng and MySQL. To create the UNIX pipe:

# mkfifo /tmp/mysql.pipe

Also, we will create a startup script used to keep feeding SQL commands sent to the UNIX pipe to MySQL called /usr/local/etc/rc.d/

( while [ -e /tmp/mysql.pipe ] do /usr/local/bin/mysql -u root --password= syslog

This script will get invoked at startup and will keep feeding the SQL commands generated by the mysql syslog-ng destination into the MySQL database.

However, we must make sure this startup script is invoked after MySQL has been started. Thus, in FreeBSD I recommend renaming the MySQL startup script:

# mv /usr/local/etc/rc.d/ 

Setting up syslog-ng

Modify /usr/local/etc/syslog-ng/syslog-ng.conf to add a new source called net used to retrieve logs via the network:

source net { udp(); };

Next, add a new destination for MySQL:

destination mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, timestamp, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PRORAM', '$MSG' );n") template-escape(yes) ); };

Finally, configure syslog-ng so all logging is sent to the MySQL destination. Since syslog-ng allows multiple destinations, this makes perfectly possible to keep locally-generated log events stored in text files alongside the MySQL database.

log { source(net); destination(mysql); }; log { source(src); destination(mysql); };

Finally, we send the SIGHUP signal to syslog-ng to instruct it to re-read its configuration file and reconfigure accordingly:

# pkill -HUP syslog-ng

Remote logging with Linksys WRT54G

Enabling remote syslog logging with Linksys WRT54G and OpenWRT White Russian RC3 is as simple as storing the IP of the remote syslog server into the log_ipaddr NVRAM variable:

nvram set log_ipaddr=A.B.C.D
nvram commit

log_ipaddr is used by /etc/init.d/rcS startup script to launch a local syslog daemon with option “-R “:

syslog_ip=$(nvram get log_ipaddr)
ipcalc -s "$syslog_ip" || syslog_ip=""
syslogd -C 16 ${syslog_ip:+-L -R $syslog_ip}

syslog-ng replacement for FreeBSD

FreeBSD uses syslog by default. However, syslog is very old and inflexible, so I decided to replace it with syslog-ng. syslog-ng syntax is far more easier to read and understand than syslog’s one.

syslog-ng uses the following elements to determine what to log and where to log it:

  • Source: Defines where log entries do come from. For example, syslog-ng can read log entries from the /var/run/log local socket, from the network via UDP port 514, via TCP, and so on.

    I just decided to split the sources in two:

    1. A local source, called src
    2. A network source, called net

    This allows easily to distinguish between locally generated log entries and log entries generated elsewhere by a network device or host machine.

  • Destination: Defines where do log entries will get logged into. For example, log entries can be written to a file, can be sent to another syslog-compatible server, sent to a socket, and so on.

    I have kept the default destinations, and added a new one called airport, pointing to file /var/log/airport.log. All log events generated by my AirPort Express Wireless Access Point will get logged into this destination.

  • Filter: Defines a matching criteria for log entries. Allows to distinguish log entries by some common attributes, like the source host, facility, logging level, a regular expression matching the entry description, and so on.

    I have kept the default filters, but added a new one called f_airport, which matches all log entries whose source is my Wireless Access Point.

Finally, log entries combine sources, filters and destinations. When a log entry is received, it is matched against every log rules until a source and a filter matches. Then, the log entry is sent to the destination or destinations for that matching rule.

Since I wanted to centralize some logs into my FreeBSD server across the network, concretely my AirPort Express logs, this is the /usr/local/etc/syslog-ng/syslog-ng.conf file I used to achieve it:

# options
options { long_hostnames(off); sync(0); };

# sources
source src { unix-dgram("/var/run/log");
             unix-dgram("/var/run/logpriv" perm(0600));
             internal(); file("/dev/klog"); };

source net { udp(); };

# destinations
destination messages { file("/var/log/messages"); };
destination security { file("/var/log/security"); };
destination authlog { file("/var/log/auth.log"); };
destination maillog { file("/var/log/maillog"); };
destination lpd-errs { file("/var/log/lpd-errs"); };
destination xferlog { file("/var/log/xferlog"); };
destination cron { file("/var/log/cron"); };
destination debuglog { file("/var/log/debug.log"); };
destination consolelog { file("/var/log/console.log"); };
destination all { file("/var/log/all.log"); };
destination newscrit { file("/var/log/news/news.crit"); };
destination newserr { file("/var/log/news/news.err"); };
destination newsnotice { file("/var/log/news/news.notice"); };
destination console { file("/dev/console"); };
destination allusers { usertty("*"); };
#destination loghost { udp("loghost" port(514)); };
destination airport { file("/var/log/airport.log"); };
destination linksys { file("/var/log/linksys.log"); };

# log facility filters
filter f_auth { facility(auth); };
filter f_authpriv { facility(authpriv); };
filter f_not_authpriv { not facility(authpriv); };
filter f_console { facility(console); };
filter f_cron { facility(cron); };
filter f_daemon { facility(daemon); };
filter f_ftp { facility(ftp); };
filter f_kern { facility(kern); };
filter f_lpr { facility(lpr); };
filter f_mail { facility(mail); };
filter f_news { facility(news); };
filter f_security { facility(security); };
filter f_user { facility(user); };
filter f_uucp { facility(uucp); };
filter f_local0 { facility(local0); };
filter f_local1 { facility(local1); };
filter f_local2 { facility(local2); };
filter f_local3 { facility(local3); };
filter f_local4 { facility(local4); };
filter f_local5 { facility(local5); };
filter f_local6 { facility(local6); };
filter f_local7 { facility(local7); };

# log level filters
filter f_emerg { level(emerg); };
filter f_alert { level(alert..emerg); };
filter f_crit { level(crit..emerg); };
filter f_err { level(err..emerg); };
filter f_warning { level(warning..emerg); };
filter f_notice { level(notice..emerg); };
filter f_info { level(info..emerg); };
filter f_debug { level(debug..emerg); };
filter f_is_debug { level(debug); };

# airport filter
filter f_airport { host("airport"); };

# linksys filter
filter f_linksys { host("linksys"); };

# *.err;kern.warning;auth.notice;mail.crit              /dev/console
log { source(src); filter(f_err); destination(console); };
log { source(src); filter(f_kern); filter(f_warning); destination(console); };
log { source(src); filter(f_auth); filter(f_notice); destination(console); };
log { source(src); filter(f_mail); filter(f_crit); destination(console); };

# *.notice;authpriv.none;kern.debug;;mail.crit;news.err /var/log/messages
log { source(src); filter(f_notice); filter(f_not_authpriv); destination(messages); };
log { source(src); filter(f_kern); filter(f_debug); destination(messages); };
log { source(src); filter(f_lpr); filter(f_info); destination(messages); };
log { source(src); filter(f_mail); filter(f_crit); destination(messages); };
log { source(src); filter(f_news); filter(f_err); destination(messages); };

# security.*                                            /var/log/security
log { source(src); filter(f_security); destination(security); };

#;                               /var/log/auth.log
log { source(src); filter(f_auth); filter(f_info); destination(authlog); };
log { source(src); filter(f_authpriv); filter(f_info); destination(authlog); };

#                                             /var/log/maillog
log { source(src); filter(f_mail); filter(f_info); destination(maillog); };

#                                              /var/log/lpd-errs
log { source(src); filter(f_lpr); filter(f_info); destination(lpd-errs); };

#                                              /var/log/xferlog
log { source(src); filter(f_ftp); filter(f_info); destination(xferlog); };

# cron.*                                                /var/log/cron
log { source(src); filter(f_cron); destination(cron); };

# *.=debug                                              /var/log/debug.log
log { source(src); filter(f_is_debug); destination(debuglog); };

# *.emerg                                               *
log { source(src); filter(f_emerg); destination(allusers); };

# airport logging
log { source(net); filter(f_airport); destination(airport); };

# linksys logging
log { source(net); filter(f_linksys); destination(linksys); };