[Snort-devel] last_cid in new database scheme v106

Ian Macdonald secsnortdev at ...1490...
Fri Sep 13 17:21:02 EDT 2002

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.

Based on my exprience build large dataload systems we should think think
about a couple things.

What would the goals for a new database structure be?
Improve logging speed,
Improve ability to pull out records quicker from db the front end,
Move rule sets and snort configs to the database rather than text files.?

One of the systems I built in the past brought streaming stock data from a
satallite feed into a database. The data was written to a text file and
every 5000 lines it would be bulkloaded in a MS SQLServer database using
bcp. BCP worked a lot faster than doing individual inserts because it didn't
log the transaction and didn't check for a lot of other things while loading
the data. Inserting data in tables with indexes is slow since you have to
update the indexes as well as doing the insert of the data.

For snort we could build a load table that has no indexes, we just insert
the data then snort is done. You would then need a second application that
would be responsible for populating the tables used for the front end
tables. This is almost what barnyard does.

For the front end tables it would be nice to have consistent column names,
and data types.
All table joins should be possible using numerical values, this is generally
faster and indexes as easier and cheaper to build compared to text fields.

It would also be nice to build in a way to keep table sizes smaller so that
we that the database doesn't get slower as the row count in events gets
large. Maybe break out data in to tables by week and a then have a table
that relates all the data?

There is an interesting article at

I tend to build what they call the Dimensional Design.

It may also be possible to speed up inserts if rules sets are loaded into
the database as part of start up processes of snort rather than having to
check on every insert.

What would happen if we loaded all the rule sets into the DB and used that
as the master rule set that snort uses rather than than loading from text
files. In the long run this would make front end design easier because the
structure for storing rules would be set. You could also but a time stamp
column that contains the last time the data was updated. Every so often so
could compare the database to see if it had been updated and reload the rule
set if needed.
I found it very confusing when I first started using snort when I tried to
work out where the rules in the database came from and what they were used
We could also do the same thing for the snort.conf. This would mean that a
simple snort.conf could be distributed to the sensors then the rest of the
snort.conf is stored in the database. This would make rule and configuration
issues a lot easier to handle from a front end interface.

This is just a collection of my thoughts from using snort for a about 6


----- Original Message -----
From: "Dirk Geschke" <Dirk_Geschke at ...802...>
To: "Kreimendahl, Chad J" <Chad.Kreimendahl at ...1167...>
Cc: "Dirk Geschke" <dirk at ...972...>;
<snort-devel at lists.sourceforge.net>; <Dirk_Geschke at ...802...>
Sent: Thursday, September 12, 2002 6:11 AM
Subject: Re: [Snort-devel] last_cid in new database scheme v106

> Hi Chad,
> > I'm in the process of trying to write a new database plugin
> > (database2?), that will use this new strucutre, as well as provide more
> > efficient means of insert/update/select statements.  The use of prepared
> > statements with bound placeholders will prevent the rebuilding of query
> > strings, and in the vast majority of databases, increase response
> > times... While only staying the same in others.
> yes that would be a good thing to do. But first of all we need
> a new design of the database. Without this a new plugin does not
> make too much sense (ok, it could be coded nicer and some of my
> previous ideas of pre-inserting of the rules could be performed).
> On the other hand, with a new design we have to ensure that all
> the available tools like ACID (and I guess there are a lot of
> similar tools out there) are running with this new desgin.
> So if this concept should not end in a dead project we have
> to involve a lot of people. Otherwise you will be the only
> user and this is disappointing and discouraging.
> > it was a positive.  This makes a large number of alerts pointless since
> > they are often overflows that are greater than the max length allowed in
> > a query string.  Placeholders/bound vars fix this.
> What is the maximum query length on oracle? Maybe you should set
> the constant in src/output-plugins/spo_database.c
> #define MAX_QUERY_LENGTH 8192
> to the right value?
> Best regards
> Dirk
> --
> +------------------------------------------------------------+
> | Dr. Dirk Geschke            | E-mail: geschke at ...802...     |
> | Gesellschaft fuer Netzwerk  | Tel.  : +49-(0)-89-991950-31 |
> | und Unix Administration mbH | Fax   : +49-(0)-89-991950-99 |
> | 85551 Kirchheim / Germany   | Raeter Stra/3e 26            |
> +------------------------------------------------------------+
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Snort-devel mailing list
> Snort-devel at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/snort-devel

More information about the Snort-devel mailing list