rasqlinsert -s -record doesn't seem to work
Carter Bullard
carter at qosient.com
Mon Oct 22 15:41:20 EDT 2012
Hey Paul,
OK, so I've tested your situation on 5 different platforms and cannot replicate your report.
Also, my rasqlinsert() won't run with your options, so I'm not sure what is going on.
I'm using either argus-clients-3.0.6.x or argus-clients-3.0.7.2.
/tmp/rarc contains:
% cat /tmp/rarc
RA_FIELD_SPECIFIER="seq stime saddr daddr sport dport sbytes dbytes state:16 proto"
When I run rasqlinsert() with your rarc fields, I get this error message:
% ../../bin/rasqlinsert -Zb -F /tmp/rarc -s -record -r /tmp/argus.2012.10.20.14.25.00 -w mysql://root@localhost/ratest/testDb_%Y_%m_%d -M time 1d
Oct 22 15:12:45 thoth.newyork.qosient.com rasqlinsert[5367] <Error>: 2012/10/22.15:12:45.928595 key field 'srcid' not in schema (-s option)
This is correct, and is what I would expect.
In order to remove the dependency on "srcid" as a KEY field, I ran with the "-m none" option.
Now, when running rasqlinsert(), it runs fine without any errors of any kind:
thoth:ramysql carter$ ../../bin/rasqlinsert -m none -F /tmp/rarc -r /tmp/argus.2012.10.20.14.25.00 -w mysql://root@localhost/ratest/testDb_%Y_%m_%d -M time 1d -s -record
thoth:ramysql carter$
So I'll log into mysql and sniff around:
thoth:ramysql carter$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.37 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ratest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_ratest |
+-------------------+
| testDb_2012_10_20 |
+-------------------+
1 row in set (0.00 sec)
mysql> desc testDb_2012_10_20;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| seq | int(10) unsigned | YES | | NULL | |
| stime | double(18,6) unsigned | NO | | NULL | |
| saddr | varchar(64) | NO | | NULL | |
| daddr | varchar(64) | NO | | NULL | |
| sport | varchar(10) | NO | | NULL | |
| dport | varchar(10) | NO | | NULL | |
| sbytes | bigint(20) | YES | | NULL | |
| dbytes | bigint(20) | YES | | NULL | |
| state | varchar(32) | YES | | NULL | |
| proto | varchar(16) | NO | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
Notice, NO fields are Key fields. Now looking at some of the data.
mysql> select * from testDb_2012_10_20;
+----------+-------------------+---------------+----------------+-------+-------+--------+--------+-----------+-------+
| seq | stime | saddr | daddr | sport | dport | sbytes | dbytes | state | proto |
+----------+-------------------+---------------+----------------+-------+-------+--------+--------+-----------+-------+
| 20371908 | 1350757498.365249 | 207.237.36.98 | 208.73.181.200 | 19277 | 443 | 66 | 66 | A_A | tcp |
| 20371908 | 1350757508.449375 | 207.237.36.98 | 208.73.181.200 | 19277 | 443 | 66 | 66 | A_A | tcp |
| 20424389 | 1350757510.993207 | 207.237.36.98 | 17.172.208.43 | 21259 | 443 | 1792 | 4993 | FSPA_FSPA | tcp |
| 20424402 | 1350757516.547110 | 66.39.3.162 | 207.237.36.98 | 993 | 6652 | 119 | 66 | PA_A | tcp |
| 20424404 | 1350757516.559853 | 66.39.3.162 | 207.237.36.98 | 993 | 45385 | 119 | 66 | PA_A | tcp |
| 20424405 | 1350757516.626816 | 66.39.3.162 | 207.237.36.98 | 993 | 20388 | 119 | 66 | PA_A | tcp |
| 20371908 | 1350757518.535000 | 207.237.36.98 | 208.73.181.200 | 19277 | 443 | 66 | 66 | A_A | tcp |
| 20371908 | 1350757528.625373 | 207.237.36.98 | 208.73.181.200 | 19277 | 443 | 66 | 66 | A_A | tcp |
What is different from your output and mine ? Yours makes the sequence number as a DB KEY?
Mine also has real time values for stime, and your's has 0000-00-00 00:00:00.
Neither of these differences are correct.
Seems that things are not standard, so something is a miss. Have you made changes to rasqlinsert() ?
Carter
On Oct 17, 2012, at 10:36 AM, Paul Schmehl <pschmehl_lists at tx.rr.com> wrote:
> --On October 16, 2012 9:13:25 PM -0400 Carter Bullard <carter at qosient.com> wrote:
>
>> Hey Paul,
>> Hmmm, what is the RA_FIELD_SPECIFIER set to in your rasqlinsert.conf?
>>
>
> # grep RA_FIELD rasqlinsert.conf
> #RA_FIELD_SPECIFIER="stime flgs proto saddr sport dir daddr dport state suser:1500 duser:1500"
> RA_FIELD_SPECIFIER="seq stime saddr daddr sport dport sbytes dbytes state:16 proto"
>
>> If you've run rasqlinsert() a bunch of times, with different fields
>> defined, you may need to drop your table so that rasqlinsert() will
>> generate the correct schema, once you get your fields the way you want
>> them.
>>
>
> I've dropped the table numerous times testing out various things.
>
> I just did it again (I substituted tablename for the actual table name in this response):
>
> mysql> drop table tablename;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> source /home/pauls/tablename.sql
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> show columns from tablename;
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
> | seq | int(12) unsigned | NO | PRI | NULL | |
> | stime | datetime | NO | | NULL | |
> | saddr | varchar(15) | YES | | NULL | |
> | daddr | varchar(15) | YES | | NULL | |
> | sport | smallint(5) unsigned | NO | | NULL | |
> | dport | smallint(5) unsigned | NO | | NULL | |
> | sbytes | smallint(4) unsigned | NO | | NULL | |
> | dbytes | smallint(4) unsigned | NO | | NULL | |
> | state | tinytext | NO | | NULL | |
> | proto | varchar(15) | YES | | NULL | |
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> 11 rows in set (0.00 sec)
>
> Then I ran this command (I've substituted generic terms for the actual names):
> # rasqlinsert -Z b -F rasqlinsert.conf -s -record -r /path/to/argus/files/2012-10-15/argus.log.2012-10-15.16\:00\:00.bz2 -w mysql://user:pass@buttercup4.utdallas.edu/dbname/tablename
>
> And got this result (one for each row):
>
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
> rasqlinsert[3137]: 2012-10-16 18:51:47 mysql_real_query error Unknown column 'record' in 'field list'
>
> The data is in the db:
>
> mysql> select * from tablename limit 20;
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+
> | ts | seq | stime | saddr | daddr | sport | dport | sbytes | dbytes | state | proto |
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+
> | 2012-10-17 14:23:02 | 1438262760 | 0000-00-00 00:00:00 | 10.200.22.138 | 23.21.220.59 | 60396 | 443 | 65535 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:03 | 1438262662 | 0000-00-00 00:00:00 | 10.40.128.34 | 10.110.7.64 | 52719 | 2532 | 65535 | 65535 | FSA_FSPA | tcp |
> | 2012-10-17 14:23:03 | 1438262475 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59445 | 7777 | 30268 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:03 | 1438262561 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59446 | 7777 | 28004 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:03 | 1438262499 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59448 | 7777 | 27830 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262624 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62500 | 443 | 5508 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:03 | 1438262522 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59447 | 7777 | 27723 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:03 | 1438262489 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59440 | 7777 | 29599 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262775 | 0000-00-00 00:00:00 | 10.200.201.43 | 69.171.234.21 | 46093 | 443 | 65535 | 5499 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262940 | 0000-00-00 00:00:00 | 213.8.52.92 | 129.110.19.38 | 51043 | 80 | 4732 | 65535 | FSPA_FSPAC | tcp |
> | 2012-10-17 14:23:02 | 1438262526 | 0000-00-00 00:00:00 | 76.185.173.239 | 129.110.10.68 | 50514 | 443 | 22869 | 14229 | PA_PA | tcp |
> | 2012-10-17 14:23:04 | 1438262925 | 0000-00-00 00:00:00 | 10.21.13.1 | 198.185.19.46 | 64765 | 80 | 10905 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262785 | 0000-00-00 00:00:00 | 10.21.13.1 | 198.81.200.2 | 64864 | 80 | 29792 | 65535 | FSPA_FSPA | tcp |
> | 2012-10-17 14:23:03 | 1438262766 | 0000-00-00 00:00:00 | 99.101.126.204 | 129.110.10.68 | 53529 | 443 | 39552 | 25344 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262679 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.253.98 | 62516 | 443 | 8657 | 65535 | SPA_SPA | tcp |
> | 2012-10-17 14:23:02 | 1438262595 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62502 | 443 | 5238 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262950 | 0000-00-00 00:00:00 | 125.16.180.5 | 129.110.10.36 | 17759 | 80 | 2717 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:02 | 1438262678 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.253.98 | 62517 | 443 | 5785 | 58786 | SPA_SPA | tcp |
> | 2012-10-17 14:23:02 | 1438262596 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62501 | 443 | 4608 | 65535 | PA_PA | tcp |
> | 2012-10-17 14:23:04 | 1438262687 | 0000-00-00 00:00:00 | 10.176.80.248 | 38.100.128.106 | 65389 | 554 | 2340 | 65535 | A_PA | tcp |
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+
> 20 rows in set (0.00 sec)
>
> So it's throwing errors on the console but not affecting the input to the db.
>
> Then I added a record field to the schema, dropped the table and recreated it:
>
> mysql> show columns from tablename;
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
> | seq | int(12) unsigned | NO | PRI | NULL | |
> | stime | datetime | NO | | NULL | |
> | saddr | varchar(15) | YES | | NULL | |
> | daddr | varchar(15) | YES | | NULL | |
> | sport | smallint(5) unsigned | NO | | NULL | |
> | dport | smallint(5) unsigned | NO | | NULL | |
> | sbytes | smallint(4) unsigned | NO | | NULL | |
> | dbytes | smallint(4) unsigned | NO | | NULL | |
> | state | tinytext | NO | | NULL | |
> | proto | varchar(15) | YES | | NULL | |
> | record | blob | YES | | NULL | |
> +--------+----------------------+------+-----+-------------------+-----------------------------+
> 12 rows in set (0.00 sec)
>
> And ran the command again. This time I get no errors on the console, and there's also no data in the record field.
>
> mysql> select * from tablename limit 20;
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+--------+
> | ts | seq | stime | saddr | daddr | sport | dport | sbytes | dbytes | state | proto | record |
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+--------+
> | 2012-10-17 14:32:06 | 1438262760 | 0000-00-00 00:00:00 | 10.200.22.138 | 23.21.220.59 | 60396 | 443 | 65535 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262662 | 0000-00-00 00:00:00 | 10.40.128.34 | 10.110.7.64 | 52719 | 2532 | 36726 | 65535 | SA_SA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262475 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59445 | 7777 | 15851 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262561 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59446 | 7777 | 14148 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262499 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59448 | 7777 | 13797 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262624 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62500 | 443 | 5508 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262522 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59447 | 7777 | 14175 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262489 | 0000-00-00 00:00:00 | 10.200.201.33 | 99.37.203.177 | 59440 | 7777 | 14620 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262775 | 0000-00-00 00:00:00 | 10.200.201.43 | 69.171.234.21 | 46093 | 443 | 65535 | 5499 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262940 | 0000-00-00 00:00:00 | 213.8.52.92 | 129.110.19.38 | 51043 | 80 | 4732 | 65535 | FSPA_FSPAC | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262526 | 0000-00-00 00:00:00 | 76.185.173.239 | 129.110.10.68 | 50514 | 443 | 22869 | 14229 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262925 | 0000-00-00 00:00:00 | 10.21.13.1 | 198.185.19.46 | 64765 | 80 | 8549 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262785 | 0000-00-00 00:00:00 | 10.21.13.1 | 198.81.200.2 | 64864 | 80 | 29792 | 65535 | FSPA_FSPA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262766 | 0000-00-00 00:00:00 | 99.101.126.204 | 129.110.10.68 | 53529 | 443 | 19842 | 13134 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262679 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.253.98 | 62516 | 443 | 8657 | 65535 | SPA_SPA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262595 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62502 | 443 | 5238 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262950 | 0000-00-00 00:00:00 | 125.16.180.5 | 129.110.10.36 | 17759 | 80 | 2717 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262678 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.253.98 | 62517 | 443 | 5785 | 58786 | SPA_SPA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262596 | 0000-00-00 00:00:00 | 10.110.11.241 | 23.67.221.177 | 62501 | 443 | 4608 | 65535 | PA_PA | tcp | NULL |
> | 2012-10-17 14:32:06 | 1438262687 | 0000-00-00 00:00:00 | 10.176.80.248 | 38.100.128.106 | 65389 | 554 | 900 | 65535 | A_PA | tcp | NULL |
> +---------------------+------------+---------------------+----------------+----------------+-------+-------+--------+--------+------------+-------+--------+
> 20 rows in set (0.00 sec)
>
> So the switch *is* preventing insertion of the record field but throws a console error if the field doesn't exist in the db. I can live with that, but it seems like a bug.
>
> --
> Paul Schmehl, Senior Infosec Analyst
> As if it wasn't already obvious, my opinions
> are my own and not those of my employer.
> *******************************************
> "It is as useless to argue with those who have
> renounced the use of reason as to administer
> medication to the dead." Thomas Jefferson
> "There are some ideas so wrong that only a very
> intelligent person could believe in them." George Orwell
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121022/5068f9f1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4367 bytes
Desc: not available
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121022/5068f9f1/attachment.bin>
More information about the argus
mailing list