Re: anyone willing to share proper syslog SQL schema & data typing?

Fairly basic, but I use this with SQLite3 and PostgreSQL.

CREATE TABLE IF NOT EXISTS events ( ts TIMESTAMP, id REAL, host TEXT,
proc TEXT, data TEXT NOT NULL );
CREATE TABLE IF NOT EXISTS stats ( min TIMESTAMP, max TIMESTAMP, idmin
REAL, idmax REAL, cnt NUMERIC );
CREATE TABLE IF NOT EXISTS notable ( ts TIMESTAMP, id REAL, key TEXT,
value TEXT );

CREATE INDEX IF NOT EXISTS ev_ts ON events (ts);
CREATE INDEX IF NOT EXISTS ev_id ON events (id);
CREATE INDEX IF NOT EXISTS ev_tshost ON events (ts, host);
CREATE INDEX IF NOT EXISTS ev_tsproc ON events (ts,proc);

-- CREATE INDEX IF NOT EXISTS no_ts ON notable (ts);
CREATE INDEX IF NOT EXISTS no_id ON notable (id);

CREATE view last1hour AS SELECT * FROM events WHERE ts >=
datetime('now', '-1 hour');
CREATE VIEW allstats AS SELECT min(min), max(max), min(idmin),
max(idmax), sum(cnt) from stats;

All data goes into the events table (self explanatory).  Since I have
many, many sqlite db's, especially over time, I use the stats table to
perform lookups as to which file contains the data for the time I'm
needing (as well as event counts).  The notable table contains keypair
values associated with event IDs, as in ('user', 'nbates') or ('ip',
'10.10.10.10').

As I said, fairly basic.  However, I store around 500 events per
second into this setup and have one years of data at hand.  Lookups
are really quick on an dual-opteron.

Also, I have software to support this, which I will release shortly.
If anybody would like to test, please let me know.
<http://nbates.googlepages.com/logic>

Regards,
Nathan