Argus Mysql - rasqlinsert suser duser
Mark Bartlett
mabartle at gmail.com
Sat Sep 12 11:36:32 EDT 2009
Here is the 'partitioning part: (Change the dates in the (TO_DAYS)
section and you'll be good to go... Make sure you are using mysql 5..
mysql 4 does NOT support partitioning... How does everyone feel about
an ARGUS "DAY".. Might be nice to meet in one location and share what
each of us are doing...
CREATE TABLE IF NOT EXISTS argus (
srcid varchar(80) NOT NULL default '',
stime time NOT NULL default '00:00:00',
sdate date NOT NULL default '2006-06-08',
ltime time NOT NULL default '00:00:00',
ldate date NOT NULL default '2006-06-08',
dur dec(30,6) unsigned NOT NULL default '0',
saddr CHAR(15) NOT NULL default '',
daddr CHAR(15) NOT NULL default '',
proto tinyint(3) unsigned NOT NULL default '0',
sport smallint(5) unsigned NOT NULL default '0',
dport smallint(5) unsigned NOT NULL default '0',
bytes int(10) unsigned NOT NULL default '0',
sbytes int(10) unsigned NOT NULL default '0',
dbytes int(10) unsigned NOT NULL default '0',
pkts int(10) unsigned NOT NULL default '0',
spkts int(10) unsigned NOT NULL default '0',
dpkts int(10) unsigned NOT NULL default '0',
dir CHAR(15) NOT NULL default '',
trans int(10) unsigned NOT NULL default '0',
seq int(10) unsigned NOT NULL default '0',
flags CHAR(15) NOT NULL default '')engine=myisam
partition by range (to_days(sdate))
(PARTITION p0 VALUES LESS THAN (to_days('2009-03-01')),
PARTITION p1 VALUES LESS THAN (to_days('2009-03-02')),
PARTITION p2 VALUES LESS THAN (to_days('2009-03-03')),
PARTITION p3 VALUES LESS THAN (to_days('2009-03-04')),
PARTITION p4 VALUES LESS THAN (to_days('2009-03-05')),
PARTITION p5 VALUES LESS THAN (to_days('2009-03-06')),
PARTITION p6 VALUES LESS THAN (to_days('2009-03-07')),
PARTITION p7 VALUES LESS THAN (to_days('2009-03-08')),
PARTITION p8 VALUES LESS THAN (to_days('2009-03-09')),
PARTITION p9 VALUES LESS THAN (to_days('2009-03-10')),
PARTITION p10 VALUES LESS THAN (to_days('2009-03-11')),
PARTITION p11 VALUES LESS THAN (to_days('2009-03-12')),
PARTITION p12 VALUES LESS THAN (to_days('2009-03-13')),
PARTITION p13 VALUES LESS THAN (to_days('2009-03-14')),
PARTITION p14 VALUES LESS THAN (to_days('2009-03-15')),
PARTITION p15 VALUES LESS THAN (to_days('2009-03-16')),
PARTITION p16 VALUES LESS THAN (to_days('2009-03-17')),
PARTITION p17 VALUES LESS THAN (to_days('2009-03-18')),
PARTITION p18 VALUES LESS THAN (to_days('2009-03-19')),
PARTITION p19 VALUES LESS THAN (to_days('2009-03-20')),
PARTITION p20 VALUES LESS THAN (to_days('2009-03-21')),
PARTITION p21 VALUES LESS THAN (to_days('2009-03-22')),
PARTITION p22 VALUES LESS THAN (to_days('2009-03-23')),
PARTITION p23 VALUES LESS THAN (to_days('2009-03-24')),
PARTITION p24 VALUES LESS THAN (to_days('2009-03-25')),
PARTITION p25 VALUES LESS THAN (to_days('2009-03-26')),
PARTITION p26 VALUES LESS THAN (to_days('2009-03-27')),
PARTITION p27 VALUES LESS THAN (to_days('2009-03-28')),
PARTITION p28 VALUES LESS THAN (to_days('2009-03-29')),
PARTITION p29 VALUES LESS THAN (to_days('2009-03-30')),
PARTITION p30 VALUES LESS THAN (to_days('2009-03-31')),
PARTITION p31 VALUES LESS THAN (to_days('2009-04-01')),
PARTITION p32 VALUES LESS THAN (to_days('2009-04-02')),
PARTITION p33 VALUES LESS THAN (to_days('2009-04-03')),
PARTITION p34 VALUES LESS THAN (to_days('2009-04-04')),
PARTITION p35 VALUES LESS THAN (to_days('2009-04-05')),
PARTITION p36 VALUES LESS THAN MAXVALUE );
On Sat, Sep 12, 2009 at 11:01 AM, <mabartle at gmail.com> wrote:
> 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
>
> ________________________________
> From: CS Lee
> Date: Sat, 12 Sep 2009 22:49:18 +0800
> To: Carter Bullard<carter at qosient.com>
> 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
>
More information about the argus
mailing list