Managing logs with Sqlite and syslogd

Update Nov 26, 2010

Looking at the code for current version of syslogd from opensolaris. The exact sources can be obtained from this link which is what I will be working from as of tonight. The source code for sqlite can be obtained from sqlite.org, but I will not be getting into how to build that or anything but where I am injecting the library calls at this point. The current version of sqlite from the site is 3.7.3 and I will most likely not be using this version, but will be using a compatible one ( version 3 ). A look at this version of syslogd specific to solaris suggests a multithreaded architecture on a per file basis. We will however hopefully be using one file and possibly many tables or something to that effect. The entry points I have found thus far are in the conf_init function on line 3339 of syslogd.c and line 1396 of syslogd in the writemsg function. The conf_init function looks like a suitable sqlite init and open it is the location where all of the log files are opened. Line 5190 might be a possible location to close the sqlite database inside the reconfigure function and conf_init should bring it back up again. Line 1687 looks like the shutdown procedure location when not reconfiguring. The Schema will look like this: CREATE TABLE logs ( id  BIGINT AUTO, loglines TEXT, logtime TIMESTAMP, remotehost BIGINT ) ; or something similar ( this is not the actuall create statement but more of a psuedo create )

Managing logs with Sqlite and syslogd

I have often wondered why we as a society of techies create all these wonderful new technologies and fancy gadgets, but we never seem to get the logging thing right. I am sure that people have thought of using an interface to logging before, after all a text editor is a form of interface, sort of. Lets face it though, looking at log files through a console, xterm, a notepad editor or web interface that parses on the fly is just plain rediculous. I know that there are those who use automated tools to parse and split logs day by day an zip up the contents and back up the zip files and do all sorts of wonderful things, but I think we are missing a very important thing here. Why dont we try to encorporate the database straight into the logging system.

Benefits of database logging

There are many benefits to logging to a database, wether or not you are using a web interface.

• The first being the sql query language which allows us to select a set of records.

• The second, which is not any less important than the first is the ability to split the data into records and fields that are easily separated and parsed.

• The third is most notable is the amount of data that you can put in a database without taking out the client.
• The fourth is the ability to selectively retrieve, delete, manipulate.

Drawbacks

Allthough this may seem like the ultimate of ultimates to log to a database, there are some drawbacks even at first glance.

• The first is the same as the last f=benefit, you can manipulate at will and effect a lot of data at once. This is not necessarily that that different from a regular file situation which you can merely delete, but more in the realm of corrupting or impersonating data in the form of date manipulation or otherwords
• The System needs to be protected
• There are potential problems with stability if modifying the core of syslog

Implementation

Although I have not actually made a patch or schema yet for this situation, I am looking into the code and plannng a project to implement a suitable patch to syslog whereas I can use sqlite as the database backend and syslogd as the front end. It may be possible to also do this with apache, postgres, mysql or other services that are not tied to syslog. The base framework would, however, be to simply get syslogd working correctly

Happy Coding and anyone interested in helping or suggesting on this project, drop us a line.