updates for argus-2.x compatbility and database support

[Carter Bullard]

Hey Mark,
Best to keep these discussions on the mailing list.

I think there are a number of different strategies that will need to be
thought about, and using the database to its fullest is really the key.
Do we use mysql partitioning?  I say YES!!!!! especially to support
federated distributed database tables.  I think that is very important.
How we do it, though, will take some discussion.

So putting the data into separate tables is A form of partitioning
(i'm starting to read about db partitioning, and it may take a little
time for me to come to speed).  This mysql specific partitioning
strategy, seems to have its own limits in terms of configuration.
How do I extend the database partition description for another
month, as time is not going to wait for me ;o)   I'm not sure that I
can do that on the fly?  You seem to have monthly tables, that are
partitioned by day?

The best question on your archive and collection strategy is,
"does it work?" and  if so, then you're doing great!!!!   The tool  
specifically
designed to generate your compressed periodic files is rastream().
I would hold off on shifting to that, though until this new release of
client software, so you're doing fine.

You are not, however aggregating any records before you put
them in the database, so you are storing the "primitive" data into
mysql.  That is fine, but there is other processing you can do to
make the data a bit easier to chew, so to speak.  BUT you need
to keep the primitive data around somewhere for a while, as that
is eventually what is needed to investigate the really bad cases.

rasqlinsert(), only creates the database or table if they are
needed.  If the table already exists, regardless of how it was
generated, rasqlinsert() just pokes data into it.


Carter

On Feb 26, 2009, at 8:44 AM, Mark Bartlett wrote:

> Looks good.. Only problem I would have is this:
>
> You create a different table for 'everyday'...  I cannot 'query' the
> database without hitting every single argus.%Y.%m.%d table
> individually..  If we us the "Partitioning option then it takes care
> of the 'date' stuff for us..  So I can write a query that says
> something like, select saddr, daddr, sum(bytes) from argus_tbl where
> (saddr = 'a source IP') group by saddr, daddr; and it would return
> that data, under your 'schema' I would have to write it like this:
> select saddr, daddr, sum(bytes) from argus_tbl.(DATE) where (saddr =
> 'a source IP') group by saddr, daddr; And I would have to repeat that
> query for 'every' date table that is created... (argus_tbl.2009-02-24,
> argus_tbl.2009-02-23)...  Which would make it difficult for me to
> query over the last week of data to 'see' what a specific SADDR did.
> (who they 'talk to', what protocols 'they' use, etc)..  Essentially I
> would like to be able to 'baseline' my network using the Argus data,
> and have my Analysts be able to 'see' patterns from graphs using the
> argus data..
>
> Thoughts??
>
> There is def. nothing wrong with your rasqlinsert tool, I could just
> write my database 'schema' using a partition and my problem is solved.
> On that note, is there any 'flag' that can be set that will NOT create
> the table, just insert?
>
> [snip]
>
>
> On Wed, Feb 25, 2009 at 9:33 PM, Carter Bullard <carter at qosient.com>  
> wrote:
>> Hey Mark,
>> There is no fixed schema, you generate whatever schema you wish.
>> So to do something like your tables, you would use something like:
>>    rasqlinsert -r file -w mysql://localhost/db/argus -s srcid stime  
>> dur \
>>         saddr daddr proto ......
>> because there is no key, rasqlinsert will just append to the table.
>> If you want an auto incrementing identifier for the row, you can
>> add "autoid" to the -s list and rasqlinsert will create an autoid  
>> column.
>> I just ran this here:
>>     ../bin/rasqlinsert -S localhost -w
>> mysql://root@localhost/ratop/etherHosts \
>>                  -M rmon nodrop cache -m srcid smac \
>>                  -s stime dur srcid smac spkts dpkts sbytes dbytes  
>> state
>> and it created this table in the ratop database (which it also  
>> created):
>> mysql> desc etherHosts;
>> +--------+-----------------------+------+-----+---------+-------+
>> | Field  | Type                  | Null | Key | Default | Extra |
>> +--------+-----------------------+------+-----+---------+-------+
>> | stime  | double(18,6) unsigned | NO   |     | NULL    |       |
>> | dur    | double(18,6)          | NO   |     | NULL    |       |
>> | srcid  | varchar(64)           | NO   | PRI |         |       |
>> | smac   | varchar(24)           | NO   | PRI |         |       |
>> | spkts  | bigint(20)            | YES  |     | NULL    |       |
>> | dpkts  | bigint(20)            | YES  |     | NULL    |       |
>> | sbytes | bigint(20)            | YES  |     | NULL    |       |
>> | dbytes | bigint(20)            | YES  |     | NULL    |       |
>> | state  | varchar(32)           | YES  |     | NULL    |       |
>> | record | blob                  | YES  |     | NULL    |       |
>> +--------+-----------------------+------+-----+---------+-------+
>> 10 rows in set (0.00 sec)
>> the record is the binary argus record that holds the actual data.
>>
>> Not sure about your partitioning,  so I'm thinking something like
>> this could work:
>>    rasqlinsert -r file -w mysql://localhost/db/argus.%Y.%m.%d
>> which would automatically create tablenames  using the timestamp
>> in the argus data.  As the data comes in, rasqlinsert would create
>> the table needed to hold the data, based on the strftime() format
>> you provide (just like rasplit).
>> Does that buy us anything>?
>> Carter
>>
>>