rasqlinsert -s -record doesn't seem to work
Paul Schmehl
pschmehl_lists at tx.rr.com
Wed Oct 17 10:36:22 EDT 2012
--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
More information about the argus
mailing list