[syslog-ng] Postgres database questions

[Balazs Scheidler]

On Wed, 2009-02-04 at 12:08 -0800, Liam Kirsher wrote:
> Hi --
> 
> I'm considering using syslog-ng to log to a postgres database, and just
> wanted to make sure I could do what I want to do before I spend a lot of
> time discovering that I can't.  So, some of these questions probably
> have obvious answers.
> 
> The client and loghost systems are running CentOS 5.2, which comes with
> syslog-ng v. 1.6.
> 
> I believe I will need to upgrade to syslog-ng 3.0 (OSE) to use the
> database logging functionality.  Correct?

I'd definitely do so, but of course I have other priorities :) 1.6 has
been unmaintained for quite some time now. The native SQL driver
capability was added to syslog-ng OSE 2.1 but if you are starting with
your project I'd definitely go with 3.0 now.

> I noticed there was a recent message regarding difficulty compiling on
> CentOS 5.2 -- I hope this is not a problem.  It looked like the
> suggested fix *should* have worked.  I just don't want to end up in the
> syslog-ng Heartbreak Hotel!

It is definitely possible to compile syslog-ng on CentOS, as we
ourselves do that regularly. Just like with other applications, to
compile syslog-ng for your platform some experience with build tools are
sometimes needed. With more and more features added to syslog-ng, it
grew a lot of build-time dependencies. Some of those are optional,
others are not.

SQL support for instance, requires the "libdbi" library
(libdbi.sourceforge.net) and libdbi-drivers (libdbi-drivers.sf.net)

If you have trouble compiling the community on this list is very helpful
(me included).

> 
> The only examples of database logging to Postgres I could find were to
> log the entire message, and/or were for older versions of syslog-ng.
> What I want to do is break the message up into columns and just log
> those columns.  Reading the documentation, it seems like what I need to
> do is create a parser which will split the message up as desired.  From
> there, I should be able to log to a database table with the desired
> fields.  Right?

If you want to do this, you definitely need 3.0, as that is the first
version that features parser capabilities. There are two options to go
from here:

1) use handcrafted regexps and use the extracted fields in SQL columns
2) use the csv-parser if the log you want to process at least
_resembles_ to comma-separated-values (e.g. has some kind of clear
"column" structure)
3) use the new db-parser() code, which is not very publicized but I
think the best way to go. db-parser parses messages based on a pattern
database where you can put application specific patterns. This is
processed by an optimized algorithm that scales very well with the
number of patterns (we measured about 50k msg/sec even with about 1000
patterns)

Once you have the "information" extracted from the log message, you have
two alternatives:

1) put these fields in separate tables per application, each application
needs a separate SQL destination

2) the patterndb supports classifying messages and it should be possible
to classify common application specific messages to the same table
structure. Then use an SQL destination per class.

If you are willing to spend time on this, I'm also willing to help you
with code if some minor changes would be needed here and there. I
definitely want to scale syslog-ng into this area.

Since the patterndb is quite undocumented, here are some pointers that
you'll probably find useful:

* We've started working on application log pattern database, you can
find this on the download page of syslog-ng Store Box:

   http://www.balabit.com/network-security/syslog-ng/storebox/

  Select the "Support" tab, and then "Upgrade", component "PatternDB".
The database here is available for free and we are definitely working
towards opening it up even more. That stuff you find here is:
    * the database converted from Debian logcheck
    * the log database converted from Cisco log documentation
    * the log database converted from Zorp log documentation

  The primary purpose so far with the database was to make it a base for
SSB classification, but the patterns are there.

* the classification chapter in the SSB manual:
http://www.balabit.com/dl/html/ssb-v1.0-guide-admin-en.html/ch07.html

* the parsing chapter in syslog-ng manual:
http://www.balabit.com/dl/html/syslog-ng-v3.0-guide-admin-en.html/ch03s08.html

* the parser reference in syslog-ng manual:
http://www.balabit.com/dl/html/syslog-ng-v3.0-guide-admin-en.html/ch08s06.html

I'm trying to write an article about the db-parser with some more
details to my blog the following days. If you have questions, just ask.



-- 
Bazsi