[Snort-devel] Snort DB schema v0 => v100/101 conversion

roman at ...49... roman at ...49...
Wed May 9 10:16:38 EDT 2001

A number of users have asked about the differences in database
schema v0 (Snort 1.7) and schema v100/101 (Snort 1.8) 
specifically related a conversion process.  Below are some
notes on what would be required to do such a conversion.
NOTE: There is currently no script which will automate this 


Two major additions are of concern when migrating data from
schema v0 to v101: signature normalization and references.

1. Signature normalization

In schema v0, signature were stored as text strings in the
the event.signature table. This schema was poor from the
performance (as speed) and storage perspective. Any query
which searched for signatures was required to do a large number
of expensive string comparisons. Likewise, there was 
duplicate text stored when multiple instances of the same alert
occured whereby wasting space. Hence the strong motivation
in v100 to migrate the schema to 3 Normal Form (3NF).

v101 created a seperate table "signature" which stores the
text strings of all the possible alerts. Therefore, now the 
"event" table was only required to store the key (a numeric value)
of a particular alert in event.signature. This key could be used
to find the associated alert name in the signature table.

Conceptually to migrate from v0 to v101 is straightforward:
- determine all the different alerts in the v0 event table
(SELECT DISTINCT signature from event)
- insert each of these alert text strings into the new v101
signature table.
(NOTE: read the info about the references because the name
 will have to be mangled a bit more)
- go through the v0 event table and replace the text string
alert name in the field event.signature, with its corresponding key
(signature.sig_id) in the signature table. 

2. References

The notion of storing external pointers describing a particular
signature/alert directly in the actual alert name was eliminated
in Snort 1.8. Instead, enhanced support for referencing
external information related to a particular signature is now
done with the "reference" option. Schema v0 has no 
conception of this "reference" option, and relies on these
external pointer to be embedded directly in the signature name.

Schema v101 introduced a number of new tables to support

sig_reference: links a signature with a reference
     - signature (1) -- sig_reference (M)
     - sig_reference (1) -- reference (1)

reference: stores a particular reference
     - reference (1) -- sig_reference (1)
     - reference (1) -- reference_system (1)

reference_system: stores the names of all the possible reference
types (e.g. CVE, whitehats)
     - reference_system (1) -- reference (1)

Rules used prior to this "reference" option have reference info
inlined with the alert name. Thus, in parallel to normalizing the
storage of signature, the corresponding reference info must
be stripped from the old style signature name 
(e.g. CVE/1234 Bad Guy Attacking => "CVE" ,"1234", "Bad ..." )
and populated into the proper tables.

One errata to point out is that many new references where added
for the signatures. Unfortunately, Snort current only adds 
references once; that is to say, when a particular signature/rule
is detected for the first time, its name and references are 
written to the DB. Subsequent occurences of the same signature
will not update the references even if they have changed.


This message was sent using Voicenet WebMail.

More information about the Snort-devel mailing list