[Discussion] not a db schema

Thu Oct 30 01:43:24 UTC 2008

Matt: As long as the schema obeys (or approaches obeying) basic third normal
form, then scalability will not be a major issue.  The Snort schema is a
decent example of this.  I think that the key difference when talking big
db/small db is in making sure that you can join the tables together to get
the larger picture when necessary, but keeping the individual tables with as
few columns as possible so that you can query them independently very
efficiently.  A good general rule would be that if we make sure that there's
always a path to join tables together (maybe even through an intermediate
table), then it makes the schema very extensible.  This is probably the most
important quality to me in a potential schema.

Andre: Hypertable looks pretty cool, but I think it would be overkill for a
lot of situations.  A tiny amount of app-level coding can allow for very
conventional databases to do some amazing things.  Breaking one big query
into two small and quick queries can sometimes make a huge difference.
There are also a lot of arithemetical tricks to play in network-based
queries.  For instance, I keep the entire MaxMind GeoIP database in MySQL
and do 1:1 joins on it against flows.  This means I can efficiently sort my
traffic by country, which is very cool.  I do this by storing the country
table not as individual IP address, but by class C address (1.1.1.0).  Then,
when I query through the traffic, I do this: JOIN geoip ON
flows.srcip-MOD(flows.srcip, 256)=geoip.subnet.  I can sort a 50 million row
flows table by country in under a minute that way because the join is an "eq
ref."

--Martin

On Wed, Oct 29, 2008 at 1:52 PM, Michael Scheidell <scheidell at secnap.net>wrote:

>  look at mynetwatchman.. it was an early attempt do do both.
>
>
>
> Andre Ludwig wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA256
>
> What is being discussed on this list in regards to technology and
> requirements for data rentetion and access just dont mesh well with
> current "standard RDBMS".  I would suggest looking at distributed file
> systems and some of the databases that sit on top of them (hypertable,
> hbase, etc come to mind).
>
> Andre Ludwig
>
> Matt Jonkman wrote:
>
>
>  Agreed. I don't want to get deep into the schema itself. Just want to
> explore the idea of not being tied to one schema.
>
> A question for the sql guru's though: Is there a large enough difference
> in how you'd structure a scheme for large scale vs small/speedy to
> justify thinking about this?
>
> Matt
>
>
> Martin Holste wrote:
>
>
>
>  I think that schema discussions are extremely premature since we still
> haven't decided what we're even recording.
>
> --Martin
>
> On Tue, Oct 28, 2008 at 11:54 AM, Matt Jonkman <jonkman at jonkmans.com<mailto:jonkman at jonkmans.com> <jonkman at jonkmans.com>> wrote:
>
>     I wonder if we ought to build several db schema's for different goals.
>
>     I'm not a deep-down sql expert (we'll get one involved when we get
>     here), but I suspect if your goal is different a different schema will
>     do best.
>
>     For instance, if you intend to keep every alert ever generated for
>     historical comparison you need one schema to keep it snappy. If you
>     intend to have a smaller number of alerts but a massive number of
>     sensors inserting frequently then you may need a different structure.
>
>     So perhaps we should build several schemas and let you choose at setup.
>     Then the engine just reads the version tag and inserts in that form.
>
>     Any sql guru's that could speak more to this?
>
>     Matt
>
>     James McQuaid wrote:
>     > I'm *still* listening... please continue.
>     >
>     > Message: 3
>     > Frank Knobbe wrote:
>     >> No, that's a bad idea (at least if you talk about Snort). If you
>     create
>     >> new/different message texts, Snort will create a new entry in the
>     >> signature table (unique to msg+gid+sid+rev). Also, you would not
>     get the
>     >> same text with barnyard or in barnyard (and probably flop) based
>     >> installs since BY only reports the sid (the msg is pulled from the
>     >> sid-msg.map file).
>     >
>     > We are not talking snort. This is totally different.
>     >
>     > And we'll definitely not use a db schema with this issue.
>     >
>     > Matt
>     >
>     >> While you could of course fork barnyard, my concern would be the
>     bloat
>     >> of the signature table due to unique msg texts.
>     >>
>     >
>     > No forking here, all new.
>     >
>     > Everything from the pattern matcher on up. :)
>     >
>     > Matt
>     >
>     >
>     > --
>     > --------------------------------------------
>     > Matthew Jonkman
>     > Emerging Threats
>     > Phone 765-429-0398
>     > Fax 312-264-0205
>     > http://www.emergingthreats.net
>     >
>     >
>     > --------------------------------------------
>     >
>     > PGP: http://www.jonkmans.com/mattjonkman.asc
>     >
>     >
>     >
>     >
>     >
>
>     --
>     --------------------------------------------
>     Matthew Jonkman
>     Emerging Threats
>     Phone 765-429-0398
>     Fax 312-264-0205
>     http://www.emergingthreats.net
>     --------------------------------------------
>
>     PGP: http://www.jonkmans.com/mattjonkman.asc
>
>
>     _______________________________________________
>     Discussion mailing list
>     Discussion at openinfosecfoundation.org
>     <mailto:Discussion at openinfosecfoundation.org> <Discussion at openinfosecfoundation.org>
>     http://lists.openinfosecfoundation.org/mailman/listinfo/discussion
>
>
>
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP Desktop 9.9.0 (Build 397)
> Charset: ISO-8859-1
>
> wsBVAwUBSQivBcjAfVnRK9hXAQjszAgAuo00m4GoBrroLxj6zcRjpEyiNP/S831u
> TWPwiF5YjEoDMpGDBlVqJiOowJ2LvO2bAJCPvR9dXnQPK7pOVgRy1ODt0cdeAV5a
> 18ALVe6P9SNCQXpt3Qnay8sSHC7fNcUxvLpyVwlRXm+2OnC/UKEjtGtBlrbXn0eO
> Aapm8i4IH53QysFuuWAEFq3tPH6T1XbrnAHEdXrUiC972gqFFy2G8i2UqxN92076
> KX0OhdeWS7QPJOTXiBRBftK9ZLvLQ42zXUtW/I7/Cmh2+hMT6/ime+QblCwX3k9q
> 9+8YOLiVTG1/KRtXSJakZ/2f4h7dK55j7az4UoD3hdzbgzLUNUagZQ==
> =6IAU
> -----END PGP SIGNATURE-----
> _______________________________________________
> Discussion mailing listDiscussion at openinfosecfoundation.org
> http://lists.openinfosecfoundation.org/mailman/listinfo/discussion
>
>
> --
> Michael Scheidell, CTO
> Phone: 561-999-5000, x 1259
> > *| *SECNAP Network Security Corporation
>
>    - Certified SNORT Integrator
>    - King of Spam Filters, SC Magazine 2008
>    - Information Security Award 2008, Info Security Products Guide
>    - CRN Magazine Top 40 Emerging Security Vendors
>
>
> ------------------------------
>
> This email has been scanned and certified safe by SpammerTrap(R).
> For Information please see www.spammertrap.com
> ------------------------------
>
>
> _______________________________________________
> Discussion mailing list
> Discussion at openinfosecfoundation.org
> http://lists.openinfosecfoundation.org/mailman/listinfo/discussion
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openinfosecfoundation.org/pipermail/discussion/attachments/20081029/8b6fd77c/attachment-0002.html>