Argus Mysql - rasqlinsert suser duser

[Carter Bullard]

Hey CS Lee,
If you are using the "-m ...." option, you are specifying keys for  
your schema.

Looking at your table description and your call of rasqlinsert(),
you need to drop the argusRT_2009_09_12 table, so rasqlinsert() can  
recreate
it withthe key definitions "-m ......" you are specifying on the  
commandline.
I suspect that you generated the table with a call that didn't have  
the "-m "
option set?

Once you do that, then the EXPLAIN will show the correct output.

Carter

On Sep 12, 2009, at 10:49 AM, CS Lee wrote:

> 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/4d6d1456/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3815 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20090912/4d6d1456/attachment.bin>