Thread: Log file to sql database
Anyone care to share suggestions on getting data from a running syslog into postgresql database? respectfully, Joseph
From: "Joseph" <lters@mrtc.com> > > Anyone care to share suggestions on getting data from a running syslog into > postgresql database? I'd start with Perl and File::Tail - I think there's actually a different module that handles log rotation etc too, but I can't remember what. Might be worth looking at swatch and see if there's anything useful in there. HTH - Richard Huxton
> Anyone care to share suggestions on getting data from a running syslog > into > postgresql database? One way would be adding something like this to syslog.conf: <facility>.<priority> |/path/to/insert/routine However that might impose unwanted burden. Also, IIRC syslog keeps `routine' loaded so making sure it doesn't waste precious cycles while idle is up to you. This pipe would be always ready for reading, so select() might fail to work as desired. Another approach is to enhance log rotation program to add the records to the database. Depends on log rotation program :) YMMV, as always and good DB design for syslog is yet to be developed IMHO. On one hand it has to be normalised enough to eliminate repetitions, on the other hand, it needs to be simple and flexible to be able to adopt to various not-so-standard programs that use syslog. At least, facility, priority, timestamp, hostname and process (name and PID) are quite common. -- ������������������
What kind of logs are you trying to dump to postgres? I recently used firelogd to enter logs for iptables-based firewalls, and it's working great. On Mon, 2 Apr 2001, Joseph wrote: > > Anyone care to share suggestions on getting data from a running syslog into > postgresql database? > > > respectfully, > Joseph > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
I would like to log cisco pix entries and then use that to report on intrusion activities and monitor traffic patterns. respectfully, Joseph -----Original Message----- From: Ben [mailto:bench@silentmedia.com] Sent: Monday, April 02, 2001 11:22 AM To: Joseph Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Log file to sql database What kind of logs are you trying to dump to postgres? I recently used firelogd to enter logs for iptables-based firewalls, and it's working great. On Mon, 2 Apr 2001, Joseph wrote: > > Anyone care to share suggestions on getting data from a running syslog into > postgresql database? > > > respectfully, > Joseph > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Thanks for the great anwsers. I recieved good answers. I had not even started thinking about the normalization part of it yet. I am not sure I understand the implications of the piping it to the insert routine. That sure would sound like a simple effective method. respectfully, Joseph > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of KuroiNeko > Sent: Monday, April 02, 2001 9:10 AM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Log file to sql database > > > > Anyone care to share suggestions on getting data from a running > syslog > > into > > postgresql database? > > One way would be adding something like this to syslog.conf: > > <facility>.<priority> |/path/to/insert/routine > > However that might impose unwanted burden. Also, IIRC syslog > keeps > `routine' loaded so making sure it doesn't waste precious cycles while > idle > is up to you. This pipe would be always ready for reading, so > select() > might fail to work as desired. > Another approach is to enhance log rotation program to add the records > to > the database. Depends on log rotation program :) > YMMV, as always and good DB design for syslog is yet to be developed > IMHO. > On one hand it has to be normalised enough to eliminate repetitions, on > the > other hand, it needs to be simple and flexible to be able to adopt > to > various not-so-standard programs that use syslog. > At least, facility, priority, timestamp, hostname and process (name > and > PID) are quite common. > > > -- > > LD$/G-$OAM$rJa$i$L > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> I am not sure I understand the implications of the piping it to the > insert > routine. It's a double-edged sword. Invoking it is as simple as adding a line to a conf file, while developing it may turn out to be tricky. At least make sure you examine output of ps ax to see how much CPU time it uses. And if you pipe it to a script, you'll have an interpreter loaded and sitting there wasting your RAM. -- ������������������
KuroiNeko <evpopkov@carrier.kiev.ua> writes: > > I am not sure I understand the implications of the piping it to the > > insert > > routine. > > It's a double-edged sword. Invoking it is as simple as adding a line to a > conf file, while developing it may turn out to be tricky. At least make > sure you examine output of ps ax to see how much CPU time it uses. > And if you pipe it to a script, you'll have an interpreter loaded and > sitting there wasting your RAM. Also, if your pipe reader slows down too much, syslogd will start blocking on the pipe, which will slow down the whole system (syslogd generally isn't multi-threaded). So I think it'd be much safer to have a flat text logfile and have your program read from that (using similar code to 'tail -f', as was suggested earlier). That way if you fall behind during a busy period you can catch up once things ease up, and syslogd doesn't kill your whole system. As for the interpreter issue, well, I'd rather spend the memory and use a language that is designed for string-slinging. Using Perl or Python to parse things is *so* much easier than C (even with a good regex library available). As always, just MHO... -Doug
> Also, if your pipe reader slows down too much, syslogd will start > blocking on the pipe Indeed. > So I think it'd be much safer to > have a flat text logfile and have your program read from that Or send the data to DB backend only when the logs are rotated. As long as the logs are kept short, grep'ing should be enough. > As for the interpreter issue, well, I'd rather spend the memory and > use a language that is designed for string-slinging. Depends on revenue/RAM price ratio :) > Using Perl or > Python to parse things is *so* much easier than C (even with a good > regex library available). Well, regex is simple, just 4 functions. It doesn't do replacements but that's not what one needs in this case. But this is off-topic, anyway. -- ������������������