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

[Rainer Gerhards]

> 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)

I think the main issue here is the normalization. In rsyslog (http://www.rsyslog.com), I already follow a property-based approach. There are already many properties to choose from, including

> timestamp
> syslogClient (hostname, ip)
> recordCategory (process name)
> recordType (facility, priority)
> fulltext (raw syslog msg)

What I do NOT have is

> sourceAddress (hostname, ip)
> destAddress (hostname,ip)
> sourceUser(username or email)
> destUser (username or email)
> objects (filenames, URLs)
> uniqueIDs (PID, message-id, etc)

(with source and destAddr I assume you mean FW/router message content, not the origin of the message itself). I do not have this yet, because I do not know how to pull it reliably. HOWEVER, rsyslog is just now getting a plug-in interface. Full completion will probably take some time, but we are on a very good path. One thing I could envision is that when you create plugins to parse these entities out of a message, it would be *very* easy to use the properties in whatever action (be it file, database or whatever).

Also, if you have some ideas what could be parsed, let me know. When I am done with the output module interface, I may have some time to try to code the parsers (it always helps if you have some actual use case before defining a plugin interface).

Rainer