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

[David Corlette]

Hi Jason,

I imagine pretty much any SIEM product has gone through this.  I personally work with Sentinel from Novell and the schema we use today is in many ways has yours as a subset.  We actually normalize all event data, whether it be syslog, SNMP, JDBC, WMI, etc into our single standard schema.

Longer term, the plan is to move to XDAS (a standard event schema maintained by The Open Group) as a standard.  You can download the specification from here:
http://www.opengroup.org/bookstore/catalog/p441.htm

There's actually an open-source implementation of the standard called OpenXDAS, although you'd have to build parsers to translate from the native format into the XDAS format.

>>> On Sun, Jul 29, 2007 at  8:15 PM, in message <46aABE5CD.505 at trimble.co.nz>,
Jason Haar <Jason.Haar at trimble.co.nz> wrote: 
> 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
> _______________________________________________
> LogAnalysis mailing list
> LogAnalysis at loganalysis.org
> http://www.loganalysis.org/mailman/listinfo/loganalysis