[logs] anyone willing to share proper syslog SQL schema & data typing?

[Jason Haar]

I'm looking at putting a proper SQL backend on our current centralized
syslog resource. The money involved with going "top shelf" commercial is
way beyond anything I can currently argue the ROI of, but our current
file-based structure is really quite slow - so SQL sounds like the way
to go.

I've seen a few syslog schemas around - but they all look pretty
low-end. I mean , single table doesn't imply much normalization :-)

However, good normalization will also require strongly typing the data
as it comes in - so the two go hand-in-hand. I'm thinking I'm going to
have to normalize all syslog records to contain the following fields,
then create a SQL schema to match (I'm not a SQL-guru, so I don't know
the correct terminology - but I hope I've grasped some of the correct
concepts).

timestamp
syslogClient (hostname, ip)
recordCategory (process name)
recordType (facility, priority)
sourceAddress (hostname, ip)
destAddress (hostname,ip)
sourceUser(username or email)
destUser (username or email)
objects (filenames, URLs)
uniqueIDs (PID, message-id, etc)
fulltext (raw syslog msg)


This can be mapped pretty cleanly to firewall/email/PAM/etc events - and
there are tonnes it doesn't work well with.

So has anyone else gone through this already?

Thanks!

-- 
Cheers

Jason Haar
Information Security Manager, Trimble Navigation Ltd.
Phone: +64 3 9635 377 Fax: +64 3 9635 417
PGP Fingerprint: 7A2E 0407 C9A6 CAF6 2B9F 8422 C063 5EBB FE1D 66D1