Database design concerns

[Paul Schmehl]

--On October 26, 2012 12:21:58 AM -0400 Carter Bullard <carter at qosient.com> 
wrote:

> Hey Paul,
> All the rasql* tools know how to cross day boundaries to find data, so it
> works well for ra*. But, that doesn't mean that there aren't better ways
> of doing things.
>
> No reason, ..., nobody has asked for column indexing with rasql*
> programs.  Probably because it does slow down record insertion.  You have
> decided to not insert the binary argus record into the database, so you
> don't need any of the rasql* tools to do your database operations.
>
> What kind of queries are you doing ?
>

Typically the question we're trying to answer is something like this:

Show me what saddr's connected to unique daddr on port num between one time 
and another.

Specific example would be for Torpig.

Which IPs connected to 8.8.8.8 on port 53 between 8AM and 12 Noon yesterday.

We might occasionally want to search for all connections for one host over 
several days.

The problem we're trying to solve is that searches on the data using ra 
take a very long time.  We're archiving the logs in four hour increments, 
bzipped, and a search of one four hour period will take 20 minutes or so. 
A search through a whole day's logs (6) would take several hours.  A 
typical day is between 50 and 75 GB compressed - 90 to 120GB uncompressed.

By putting the critical information into a database we can locate the 
connections more quickly, then use ra with the -t switch to hopefully find 
the data more quickly.

A typical query would be something like this:

SELECT * from table where saddr between x and y and daddr=z and stime 
between z and a.

I just ran that query and it took 25 seconds.  Dropping the saddr range and 
just searching for all connections to that destination dropped the time to 
1 second.  That same query on a four hour argus file would taken 20 minutes 
or so.

Paul Schmehl, Senior Infosec Analyst
As if it wasn't already obvious, my opinions
are my own and not those of my employer.
*******************************************
"It is as useless to argue with those who have
renounced the use of reason as to administer
medication to the dead." Thomas Jefferson
"There are some ideas so wrong that only a very
intelligent person could believe in them." George Orwell