Log file to sql database

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:

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

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

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.

--

������������������������������������