Wednesday, November 4, 2009

Making rsyslog output to an sqlite3 database using libdbi

I had discovered that there are no thorough instructions online explaining how one gets rsyslog’s sqlite3 support to work. Solving the problem myself, with some help from forum posts and Rainer Gerhards’ syslog blog and documentation, I was inspired to start a blog to document all the little things I’m doing that other system administrators could learn from.

The first thing to do is make sure libdbi is installed. I installed version 0.8.3 of libdbi and libdbi-drivers from source. If you use Ubuntu or Debian you can install ‘libdbi0′ and ‘libdbd-sqlite3′ packages with aptitude.

Next, download rsyslog 5.2.0 or later. Ubuntu doesn’t have an ‘rsyslog-libdbi’ package like it should, so you might as well just download the latest version of rsyslog and compile omlibdbi.so yourself.

The next step, thanks to noggin143 in this forum thread, is to actually modify the source code to the omlibdbi plugin before you run the rsyslog Makefile. Mr. Gerhards never did put anything in contrib or fix this in the source code of the project like he said he would.

In ‘rsyslog-5.2.0/plugins/omlibdbi/omlibdbi.c’, immediately preceding line 189:

 ...
 dbi_conn_set_option(pData->conn, "host",     (char*) pData->host);

the following line is inserted:

 dbi_conn_set_option(pData->conn, "sqlite3_dbdir", "/var/log");
 ...

Copy the example rsyslog.conf found in the root of the source folder, ie. ‘rsyslog-5.2.0/rsyslog.conf’, to /etc using sudo and add the following snippet to it:

$ModLoad omlibdbi.so
$ActionLibdbiDriver sqlite3
$ActionLibdbiDBName rsyslog.log
	
*.* :omlibdbi:

What will this do? ModLoad loads the plugin omlibdbi, and the only two properties requiring a value to be set to get this plugin to work with the hard-coded sqlite3 database path are the $ActionLibdbiDriver and $ActionLibdbiDBName. The :omglibdbi: is described as a “selector line” and it needs no arguments when using the sqlite3 driver. *.* :omlibdbi: forwards all messages to the sqlite3 database configured to be stored at ‘/var/log/rsyslog.log’.

We still have yet to create a schema for the tables that rsyslog will store its messages in. Thanks in part to noggin143, here are some working SQL CREATE TABLE statements:

CREATE table SystemEventsProperties
(
   ID serial primary key ,
   SystemEventID int NULL ,
   ParamName varchar(255) NULL ,
   ParamValue text NULL
); 
	
CREATE TABLE SystemEvents
(
ID serial primary key,
CustomerID bigint,
ReceivedAt timestamp without time zone NULL,
DeviceReportedTime timestamp without time zone NULL,
Facility smallint NULL,
Priority smallint NULL,
FromHost varchar(60) NULL,
Message text,
NTSeverity int NULL,
Importance int NULL,
EventSource varchar(60),
EventUser varchar(60) NULL,
EventCategory int NULL,
EventID int NULL,
EventBinaryData text NULL,
MaxAvailable int NULL,
CurrUsage int NULL,
MinUsage int NULL,
MaxUsage int NULL,
InfoUnitID int NULL ,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
SystemID int NULL
);

You’ll want to initialize rsyslog’s database by pasting that SQL into a file called ~/rsyslog.sql and running the following commands:

user@host:~$ sqlite3 rsyslog.log
SQLite version 3.6.14.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read rsyslog.sql
sqlite> .quit
	
user@host:~$ sudo cp rsyslog.log /var/log

And don’t forget to make sure the permissions are such that rsyslogd can write to it!

You’re ready to compile rsyslog with ‘./configure –enable-libdbi && make && make install’. When you start it, it should be logging to the specified log file.

Let me know if you have any problems. Email sysadminblog09@mailinator.com or leave a comment.