Help with rasql queries

[Carter Bullard]

Hey Jesse,
Your tables took too long to build, What version are you using ???
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.

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?
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
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 6837 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20130503/c08c5842/attachment.bin>