Help with rasql queries
Jesse Bowling
jessebowling at gmail.com
Fri May 3 22:05:04 EDT 2013
Hi Carter,
Well that's just sillly. I copy pasted from example text and didn't notice
that it was using backticks rather than single quotes. After correcting the
quotes it's all good...
2m:38s to check 2.5 million IP's over three days...Not bad! :)
Cheers,
Jesse
On Fri, May 3, 2013 at 9:02 PM, Carter Bullard <carter at qosient.com> wrote:
> Hey Jesse,
> Use mysql() to try the same queries rasql() is using, to see why they
> aren't giving you a return.
>
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.029297 SQL Query SELECT
> record from ipAddrs_2013_04_29 WHERE saddr=’66.249.76.186’
>
>
> Try that literally, but change record to something like saddr.
>
> Carter
>
>
> On May 3, 2013, at 7:52 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
>
> Thanks Carter; I missed the configure step after making .debug...
>
> # rasql -t -7d+7d -M time 1d -M sql="saddr=’66.249.76.186’" -r
> mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d -D3
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:30.971502
> ArgusParseInit(0x120ee010, NULL)
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.009019 RaMySQLInit ()
> RaSource (null) RaArchive (null) RaFormat (null)
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.019209 SQL Query SELECT
> record from ipAddrs_2013_04_27 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.023303 SQL Query SELECT
> record from ipAddrs_2013_04_28 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.029297 SQL Query SELECT
> record from ipAddrs_2013_04_29 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.032263 SQL Query SELECT
> record from ipAddrs_2013_04_30 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.036089 SQL Query SELECT
> record from ipAddrs_2013_05_01 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.039221 SQL Query SELECT
> record from ipAddrs_2013_05_02 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.043198 SQL Query SELECT
> record from ipAddrs_2013_05_03 WHERE saddr=’66.249.76.186’
> rasql[19804.c0272312b07f0000]: 05/03/13 19:49:31.048043 ArgusShutDown (2)
>
> What next? I see that 3.0.7.7 is available; should I upgrade to those?
>
> Thanks for the assistance,
>
> Jesse
>
>
> On Fri, May 3, 2013 at 7:35 PM, Carter Bullard <carter at qosient.com> wrote:
>
>> Hey Jesse,
>> You have to compile in the debug support....in the clients root directory.
>> % touch .debug
>> % ./configure
>> % make clean; make
>>
>> Carter
>>
>> Carter Bullard, QoSient, LLC
>> 150 E. 57th Street Suite 12D
>> New York, New York 10022
>> +1 212 588-9133 Phone
>> +1 212 588-9134 Fax
>>
>> On May 3, 2013, at 4:03 PM, Jesse Bowling <jessebowling at gmail.com> wrote:
>>
>> On Fri, May 3, 2013 at 2:23 PM, Carter Bullard <carter at qosient.com>wrote:
>>
>>> Hey Jesse,
>>> Your tables took too long to build, What version are you using ???
>>>
>>
>> RaSqlInsert Version 3.0.7.3
>> RaSql Version 3.0.7.3
>>
>>
>>> I suspect, because you used the -R option, that you tickled a bug
>>> where we end up using huge amounts of memory, and you started
>>> to page. The bug was the -R option seemed to not want to deallocate
>>> the primitive flow records between files, but the -r worked fine. That
>>> bug should have been fixed.
>>>
>>> If you had used:
>>> > rasqlinsert -M time 1d -M rmon -m saddr -r 2013/04/29/* -r
>>> 2013/04/30/* -w mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d - ip
>>>
>>> I believe it would have gone much quicker. Could you give that a try ?
>>> I removed the " -M cache " option, as that is really intended for real
>>> time data
>>> processing, using the " -S argus.source " rather than files.
>>>
>>> I'll give that a shot with some new data and let you know how that
>> goes...It should be noted that this is an over-the-wire insert; thus
>> network comes into play... :)
>>
>>
>>> OK, to best understand what a particular query is doing, use the " -D3 "
>>> debug option. It should printout the actual mysql calls, so you can
>>> figure out
>>> what is up.
>>>
>>> Try your query:
>>> rasql -t -7d+7d -M time 1d -M sql="saddr=’66.249.76.186’" -r
>>> mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d -D3
>>>
>>> What did you get?
>>>
>>
>> Unfortunately I get....nothing. The prompt simply returns with no
>> messages. I also tried with -D9 with the same results...
>>
>> I ensured that there is a .debug file in the source directory and
>> re-comiled, but it's still not giving a response...
>>
>>
>>> Carter
>>>
>>>
>>>
>>> On May 3, 2013, at 12:16 PM, Jesse Bowling <jessebowling at gmail.com>
>>> wrote:
>>>
>>> > Hi,
>>> >
>>> > Again I'm trying to follow through some of the APT examples. I'm very
>>> interested in the IP address SQL backing tables to speed searches, as this
>>> is a common search for my purposes.
>>> >
>>> > I've built tables with two days of data like:
>>> >
>>> > rasqlinsert -M time 1d -M rmon -m saddr -R 2013/04/29 -R 2013/04/30 -w
>>> mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d -M cache - ip
>>> >
>>> > This took a while (29 hours) but did build tables:
>>> >
>>> > mysql> use argus_ip;
>>> > Database changed
>>> > mysql> show tables;
>>> > +--------------------+
>>> > | Tables_in_argus_ip |
>>> > +--------------------+
>>> > | ipAddrs_2013_04_29 |
>>> > | ipAddrs_2013_04_30 |
>>> > +--------------------+
>>> > 2 rows in set (0.00 sec)
>>> >
>>> > mysql> describe ipAddrs_2013_04_29;
>>> > +--------+-----------------------+------+-----+---------+-------+
>>> > | Field | Type | Null | Key | Default | Extra |
>>> > +--------+-----------------------+------+-----+---------+-------+
>>> > | stime | double(18,6) unsigned | NO | | NULL | |
>>> > | flgs | varchar(32) | YES | | NULL | |
>>> > | proto | varchar(16) | NO | | NULL | |
>>> > | saddr | varchar(64) | NO | PRI | NULL | |
>>> > | sport | varchar(10) | NO | | NULL | |
>>> > | dir | varchar(3) | YES | | NULL | |
>>> > | daddr | varchar(64) | NO | | NULL | |
>>> > | dport | varchar(10) | NO | | NULL | |
>>> > | pkts | bigint(20) | YES | | NULL | |
>>> > | bytes | bigint(20) | YES | | NULL | |
>>> > | state | varchar(32) | YES | | NULL | |
>>> > | record | blob | YES | | NULL | |
>>> > +--------+-----------------------+------+-----+---------+-------+
>>> > 12 rows in set (0.00 sec)
>>> >
>>> > mysql> select count(*) from ipAddrs_2013_04_29;
>>> > +----------+
>>> > | count(*) |
>>> > +----------+
>>> > | 715059 |
>>> > +----------+
>>> > 1 row in set (0.02 sec)
>>> >
>>> > mysql> select count(*) from ipAddrs_2013_04_30;
>>> > +----------+
>>> > | count(*) |
>>> > +----------+
>>> > | 1362479 |
>>> > +----------+
>>> > 1 row in set (0.00 sec)
>>> >
>>> > mysql> select
>>> stime,flgs,proto,saddr,sport,dir,daddr,dport,pkts,bytes,state from
>>> ipAddrs_2013_04_29 limit 1;
>>> >
>>> +-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
>>> > | stime | flgs | proto | saddr | sport | dir |
>>> daddr | dport | pkts | bytes | state |
>>> >
>>> +-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
>>> > | 1367208000.000000 | | ip | 66.249.76.186 | | <-> |
>>> 0.0.0.0 | | 2465 | 1964858 | CON |
>>> >
>>> +-------------------+------+-------+---------------+-------+------+---------+-------+------+---------+-------+
>>> > 1 row in set (0.00 sec)
>>> >
>>> > ...but now I can't seem to get any data back out using the
>>> incantations I've seen:
>>> >
>>> > # time rasql -t -7d+7d -M time 1d -M sql="saddr=’66.249.76.186’" -r
>>> mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
>>> >
>>> > real 0m0.073s
>>> > user 0m0.008s
>>> > sys 0m0.021s
>>> >
>>> > # time rasql -t 2013/04/29 -M time 1d -M sql="saddr=’66.249.76.186’"
>>> -r mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
>>> >
>>> > real 0m0.045s
>>> > user 0m0.010s
>>> > sys 0m0.017s
>>> >
>>> > This does work, but much more slowly that I expected:
>>> >
>>> > # time rasql -t 2013/04/29 -M time 1d -r mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
>>> - host 66.249.76.186
>>> > StartTime Flgs Proto SrcAddr
>>> Sport Dir DstAddr Dport TotPkts TotBytes State
>>> > 04/29/13 00:00:00.000000 0 66.249.76.186
>>> <-> 0.0.0.0 2465 1964858 CON
>>> >
>>> > real 0m46.419s
>>> > user 0m4.245s
>>> > sys 0m0.991s
>>> >
>>> > # time rasql -t 2013/04/29+1d -M time 1d -r mysql://user@host/argus_ip/ipAddrs_%Y_%m_%d
>>> - host 66.249.76.186
>>> > ^C
>>> > real 26m18.117s
>>> > user 0m0.037s
>>> > sys 0m0.165s
>>> >
>>> >
>>> > I'm guessing that the second form (using a filter to rasql) is pulling
>>> back ALL records from mySQL and filtering that, whereas the first format
>>> should be using mySQL to do the filtering (the preferred method). So I
>>> suppose my questions are "Is this last statement correct?" and "What am I
>>> doing wrong with the first form of query?"
>>> >
>>> > Is the query speed for the working format inline with expectations?
>>> Seems a bit slow...
>>> >
>>> > Cheers,
>>> >
>>> > Jesse
>>> > --
>>> > Jesse Bowling
>>> >
>>>
>>>
>>
>>
>> --
>> Jesse Bowling
>>
>>
>
>
> --
> Jesse Bowling
>
>
--
Jesse Bowling
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130503/83742405/attachment.html>
More information about the argus
mailing list