[Snort-devel] last_cid in new database scheme v106
cm at ...1206...
Mon Sep 23 04:10:03 EDT 2002
[This is a late answer, I've been to busy implementing what I'll
On Fri, Sep 13, 2002 at 08:19:34PM -0400, Ian Macdonald wrote:
> I would love to see a different database structure, as long as we keep the
> existing spo for compatibility. For databases that support views it would
> also be possible to build one that maps back to the original db design.
We're doing a managed IDS service based on snort and started out with ACID
and a few local hacks to it. However, when the number of alerts stored
grew, performance degraded terribly (when we got to the point when
the DB couldn't be cached in RAM anymore).
We decided to completely ditch the snort DB schema and ACID and build our
own. The system is proprietary, but the important points I think should
be considered are:
- simpler table structure: the snort DB schema is normalized, which
IMO complicates the building of queries and doesn't help performance
(at least not with postgresql, we tried mysql too, wasn't a big performance
win). We ended with a a pair of event and packet tables, where the event
table has an eid (event id) which is a serial, sensor_id, timestamp,
signature ID, source and destination IP addresses and ports (which
duplicate as ICMP type/code), IP protocol and an incident ID
(as we assign alerts to incidents); the packet table only has the
eid and the packet data.
This brings a simple table structure that has a fixed and small
record size for the "event" table and variable size for the seldomly-
accessed packet table.
- build a "multi-stage" table "pipeline". There's a pair of input tables
where a simple perl script imports the data from unified log files,
"active" tables for stuff that's to-be-examined, and "archive" tables
for stuff that is already assigned to incidents. This means the most
accessed tables, "active", stay small and fast.
- build a helper table that has statistics on the events on some
key columns, like IP addresses, ports, signatures. I recognize this
may make you database people cringe, but it allows the type of queries
that were extremely slow with ACID and a lot of data (i.e. "show me
the top N signatures in the last N hours) very fast. The helper table
is updated when adding/moving/deleting alerts by means of a perl
function -- we tried to use triggers in postgres, but performance
- Note that we didn't rewrite spo_database, as the idea of directly importing
into the DB didn't seem too smart to me anyways -- we're writing unified
alert files, transfer those and import them, thereby achieving more
resilience in case of DB downtime, network outages and similiar events.
- As we decided to go with postgresql (for the transaction support),
we're using non-portable features like the 'inet' (ip address)
data type, referential integrity etc.
The new system seems to work OK so far, the response times in the analyst
interface are short, and it seems to hold up to floods of alerts and
Christian Mock Wiedner Hauptstrasse 15
Senior Security Engineer 1040 Wien
CoreTEC IT Security Solutions GmbH +43-1-5037273
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 232 bytes
Desc: not available
More information about the Snort-devel