Argus Mysql - rasqlinsert suser duser

[mabartle at gmail.com]

Hello everyone.  I have been saving the argus data to a mysql db for about 2 years now.  I "partition" my database by "date".  I'll send my db schema script in a few so you can see how I did it.  Haven't messed with the indexing yet.  But l was 'playing' with the idea of 2 db's.  One for the current day and one for "reporting" which would 'hold' the last X days.  The current day DB is partitioned by HOUR and rolled on a 26 hour basis.  Both db have queries run against them on the 'backend' and saved to a "reporting" table for faster report generation.

Bartlett
Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: CS Lee <geek00l at gmail.com>

Date: Sat, 12 Sep 2009 22:49:18 
To: Carter Bullard<carter at qosient.com>
Cc: Argus<argus-info at lists.andrew.cmu.edu>
Subject: Re: [ARGUS] Argus Mysql - rasqlinsert suser duser


hi Carter and all,

Yes I do see the record field in my argus db, and that's where the blob
located and I can retrieve them with rasql, currently it works very well for
me, I don't hit performance issue yet as I'm testing it on limited set of
data only, and since we may use a lot of SELECT statement in mysql, and to
increase performance maybe we can use index so that we can handle millions
and millions of data entry. Currently I checked out the db schema -

mysql> EXPLAIN select * from argusRT_2009_09_12 WHERE dport = '80';
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table              | type | possible_keys | key  |
key_len | ref  | rows  | Extra       |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | argusRT_2009_09_12 | ALL  | NULL          | NULL |
NULL    | NULL | 35332 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.15 sec)

The type is ALL which is no good, maybe we should change the strategy to
index some of the fields, anyway I'm not database expert and hopefully
someone with more experience in db stuffs can offer their help in db
improvement.

I really love the idea of putting the data into the db, as it ease the
report creation, layer2/3/4 endpoint report, hourly/daily/monthly report.
and good for graphing as well.

I may have more questions and thinking coming along, big thanks!

On the other hand, I have uploaded argus binary for MIPS platform(OpenWRT
Kamikaze 8.09)

http://gutizz.com/scripts/argusbinary/argus3-mips.tar.bz2
http://gutizz.com/scripts/argusbinary/argus3-mips.tar.bz2.md5.txt

I have also written few new posts about argus client tools in my blog in
case it may help others, cheers ;)


On Sat, Sep 12, 2009 at 10:17 PM, Carter Bullard <carter at qosient.com> wrote:

> Hey CS Lee,So, there is a set of mysql logs that may have the error in it.I
> suspect that the error comes from some text pattern in the user
> data buffer itself, like a comma or a quote in the buffer.
> I'll have to look at the "escaping" code to see that it covers the
> user data (I've never inserted the user data buffers into the database).
>
> OK, so a few things to consider.   By inserting the records itself,
> which is the default schema behavior,  you have a binary form of
> every field available in the database, but MySQL supports a limited
> set of operations on binary blob data.
>
> By "exposing" fields in the MySQL schema, you can have MySQL
> operate on it, like sorting, selecting based ranges, patterns etc, but
> you don't really want to "expose"  all the fields.  The message here
> is to expose only the fields that you want MySQL to do operations on.
>
> And you let ra* programs do the operations on the other fields.
>
> Carter
>
> On Sep 11, 2009, at 10:11 PM, CS Lee wrote:
>
> hi Carter,
>
> rasqlinsert is running smooth now, and I'm happy with it, I'm testing
> adding user data to the db, but I hit this error, here's the command I use
>
> rasqlinsert -n -S localhost -w
> mysql://root@localhost/argusdb/argusRT_%Y_%m_%d -M cache -m srcid proto
> saddr sport daddr dport -s stime srcid flgs proto saddr sport dir daddr
> dport spkts dpkts sbytes dbytes pkts bytes state suser duser - ip
>
> This error shows up
>
> ArgusInfo: 10:03:17.116854 mysql_real_query error You have an error in your
> SQL syntax; check the manual that corresponds to your MySQL server
>
> Here's the database schema that automatically created when I run
> rasqlinsert -
>
> echo 'desc argusRT_2009_09_12' | mysql -u root argusdb
> Field    Type    Null    Key    Default    Extra
> stime    double(18,6) unsigned    NO        NULL
> srcid    varchar(64)    YES        NULL
> flgs    varchar(32)    YES        NULL
> proto    varchar(16)    NO        NULL
> saddr    varchar(64)    NO        NULL
> sport    varchar(10)    NO        NULL
> dir    varchar(3)    YES        NULL
> daddr    varchar(64)    NO        NULL
> dport    varchar(10)    NO        NULL
> spkts    bigint(20)    YES        NULL
> dpkts    bigint(20)    YES        NULL
> sbytes    bigint(20)    YES        NULL
> dbytes    bigint(20)    YES        NULL
> pkts    bigint(20)    YES        NULL
> bytes    bigint(20)    YES        NULL
> state    varchar(32)    YES        NULL
> suser    varbinary(2048)    YES        NULL
> duser    varbinary(2048)    YES        NULL
> record    blob    YES        NULL
>
>
> If I don't add suser and duser, everything is running great.
>
> Thanks!
>
> --
> Best Regards,
>
> CS Lee<geek00L[at]gmail.com>
>
> http://geek00l.blogspot.com
> http://defcraft.net
>
>
>


-- 
Best Regards,

CS Lee<geek00L[at]gmail.com>

http://geek00l.blogspot.com
http://defcraft.net

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20090912/cdfe1c91/attachment.html>