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.ht... * the parser reference in syslog-ng manual: http://www.balabit.com/dl/html/syslog-ng-v3.0-guide-admin-en.html/ch08s06.ht... 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