Using a sqlite backend for syslogd

I wanted to make my server's system logs a bit more structured, in order to mine their data from python scripts, so I switched to a SQL backend.

Now I have a single /var/log/system.db with log rotation support, and can do complex SQL queries on it such as:

sqlite> select distinct(program) from logs;
cron
lighttpd
syslog-ng
sqlite> select msg from logs where program='lighttpd' and date=date('now','localtime') and time>time('now','-1 minutes','localtime');
lighttpd[8369]: 216.240.132.119 - - [09/Mar/2008:18:09:29 +0100] \"GET /w00tw00t.at.ISC.SANS.DFind:) HTTP/1.1\" 400 349 \"-\" \"-\"

So, how did I make it work? Here is my syslog-ng.conf:

options { chain_hostnames(off); sync(0); stats(43200); };
source src { unix-stream("/dev/log"); internal(); };
source kernsrc { file("/proc/kmsg"); };
destination system_db {
        program("/usr/bin/sqlite3 /var/log/system.db"
        template("INSERT INTO logs VALUES(NULL,'$HOST','$FACILITY','$PRIORITY','$LEVEL','$TAG','$YEAR-$MONTH-$DAY','$HOUR:$MIN:$SEC','$PROGRAM','$MSG');\n")
        template-escape(yes)
        );
};
destination console_all { file("/dev/tty12"); };
log { source(src); destination(console_all); };
log { source(src); destination(system_db); };

I also had to tell lighttpd to use syslog instead of log files, by modifying lighttpd.conf (don't forget to add "mod_accesslog" to the server.modules list!):

server.errorlog-use-syslog = "enable"
accesslog.use-syslog       = "enable"
# you can safely delete the following declarations:
# server.errorlog=
# accesslog.filename=

Then, I created the database file:

sqlite3 -list /var/log/system.db "CREATE TABLE logs (id integer primary key,\
host text 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 CURRENT_DATE,\
time time default CURRENT_TIME,program varchar(15) default NULL,\
msg text);"

..and reloaded both lighttpd and syslog-ng (here for Gentoo Linux):

/etc/init.d/syslog-ng reload
/etc/init.d/lighttpd reload

This is it. Additionally, here is the script called once in a while by cron for rotating logs:

#!/bin/bash

SQLITE=$(which sqlite3)
SYSTEMLOG="/var/log/system.db"
TMPLOG="/var/log/tmp.db"
BACKUP="/var/log/$(date +"%Y%m%d-%H%M%S.sql")"

rm -f "$TMPLOG" >/dev/null 2>&1

"$SQLITE" -list "$TMPLOG" "CREATE TABLE logs (id integer primary key,\
host text 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 CURRENT_DATE,\
time time default CURRENT_TIME,program varchar(15) default NULL,\
msg text);" && chmod 600 "$TMPLOG" && (
        "$SQLITE" -list "$SYSTEMLOG" ".dump" | bzip2 >"${BACKUP}.bz2"
) && mv "$TMPLOG" "$SYSTEMLOG"

# The following is Gentoo Linux specific, you must replace it
# by what is provided in your distro for reloading syslog-ng
/etc/init.d/syslog-ng reload

EDIT:Don't forget to clean things a bit after you're done. First, we can remove lighttpd-specific options passed to logrotate. Do not just delete this file, since a lighttpd update will likely recreate it:

if [ -f /etc/logrotate.d/lighttpd ]; then
echo > /etc/logrotate.d/lighttpd
fi

You may want to uninstall logrotate, but keep in mind that some binary logs are not handled by syslogd (lastlog and wtmp are the most common ones) so logrotate might still be of some use.

After a while, and if you did not feel confident enough to delete them when you switched to your new SQL syslog backend, logrotate will rotate all the now-unused logs. If you find empty files in /var/log, then it means you can safely delete them!

EDIT:I added a chmod 600 in the sql rotation cronjob above since -rw------- was the default access mode for my box's logs.