[Snort-devel] last_cid in new database scheme v106

Christian Mock 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 
describe lately...]

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
  was terrible.

- 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 
scale well.



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...
Name: signature.ng
Type: application/pgp-signature
Size: 232 bytes
Desc: not available
URL: <https://lists.snort.org/pipermail/snort-devel/attachments/20020923/8a55692b/attachment.sig>

More information about the Snort-devel mailing list