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.<br>
<br>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 (<a href="http://1.1.1.0">1.1.1.0</a>). 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."<br>
<br>--Martin<br><br><div class="gmail_quote">On Wed, Oct 29, 2008 at 1:52 PM, Michael Scheidell <span dir="ltr"><<a href="mailto:scheidell@secnap.net">scheidell@secnap.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div bgcolor="#ffffff" text="#000000">
look at mynetwatchman.. it was an early attempt do do both.<br>
<br>
<br>
<br>
Andre Ludwig wrote:
<blockquote type="cite"><div><div></div><div class="Wj3C7c">
<pre>-----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:
</pre>
<blockquote type="cite">
<pre>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:
</pre>
<blockquote type="cite">
<pre>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 <<a href="mailto:jonkman@jonkmans.com" target="_blank">jonkman@jonkmans.com</a>
<a href="mailto:jonkman@jonkmans.com" target="_blank"><mailto:jonkman@jonkmans.com></a>> 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
> <a href="http://www.emergingthreats.net" target="_blank">http://www.emergingthreats.net</a>
>
>
> --------------------------------------------
>
> PGP: <a href="http://www.jonkmans.com/mattjonkman.asc" target="_blank">http://www.jonkmans.com/mattjonkman.asc</a>
>
>
>
>
>
--
--------------------------------------------
Matthew Jonkman
Emerging Threats
Phone 765-429-0398
Fax 312-264-0205
<a href="http://www.emergingthreats.net" target="_blank">http://www.emergingthreats.net</a>
--------------------------------------------
PGP: <a href="http://www.jonkmans.com/mattjonkman.asc" target="_blank">http://www.jonkmans.com/mattjonkman.asc</a>
_______________________________________________
Discussion mailing list
<a href="mailto:Discussion@openinfosecfoundation.org" target="_blank">Discussion@openinfosecfoundation.org</a>
<a href="mailto:Discussion@openinfosecfoundation.org" target="_blank"><mailto:Discussion@openinfosecfoundation.org></a>
<a href="http://lists.openinfosecfoundation.org/mailman/listinfo/discussion" target="_blank">http://lists.openinfosecfoundation.org/mailman/listinfo/discussion</a>
</pre>
</blockquote>
<pre>
</pre>
</blockquote>
</div></div><pre><div><div></div><div class="Wj3C7c">
-----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 list
<a href="mailto:Discussion@openinfosecfoundation.org" target="_blank">Discussion@openinfosecfoundation.org</a>
</div></div><div class="Ih2E3d"><a href="http://lists.openinfosecfoundation.org/mailman/listinfo/discussion" target="_blank">http://lists.openinfosecfoundation.org/mailman/listinfo/discussion</a>
</div></pre>
</blockquote>
<br>
<div>-- <br>
Michael Scheidell, CTO<br>
Phone: 561-999-5000, x 1259<br>
<font color="#999999">></font><font color="#cc0000"> <b>| </b></font>SECNAP
Network Security Corporation
<ul>
<li>Certified SNORT Integrator</li>
<li>King of Spam Filters, SC Magazine 2008</li>
<li>Information Security Award 2008, Info Security Products Guide</li>
<li>CRN Magazine Top 40 Emerging Security Vendors</li>
</ul>
</div>
<br>
<div><hr>
<p>This email has been scanned and certified safe by SpammerTrap®.
<br>For Information please see
<a href="http://www.spammertrap.com" target="_blank">www.spammertrap.com</a></p>
<hr></div>
<br>
</div>
<br>_______________________________________________<br>
Discussion mailing list<br>
<a href="mailto:Discussion@openinfosecfoundation.org">Discussion@openinfosecfoundation.org</a><br>
<a href="http://lists.openinfosecfoundation.org/mailman/listinfo/discussion" target="_blank">http://lists.openinfosecfoundation.org/mailman/listinfo/discussion</a><br>
<br></blockquote></div><br>