rasqlinsert -s -record doesn't seem to work
Carter Bullard
carter at qosient.com
Thu Oct 18 14:37:03 EDT 2012
Sounds like " user " doesn't have table create permissions?
It seems that it should have generated an error when the create squery failed. If you turn on debugging and run with -D6 it should reveal what the deal may be ?
Carter
On Oct 18, 2012, at 2:21 PM, Paul Schmehl <pschmehl_lists at tx.rr.com> wrote:
> When I use this:
> rasqlinsert -Z b -F rasqlinsert.conf -s -record -M cache -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/tblname_%Y_%m_%d -M time 1d
>
> I get this:
> rasqlinsert[13682]: 18 Oct 12 16:44:44.019485 mysql_real_query error Table 'dbname.tblname_2012_10_15' doesn't exist
> rasqlinsert[13682]: 18 Oct 12 16:44:44.019856 mysql_real_query error Table 'dbname.tblname_2012_10_15' doesn't exist
> rasqlinsert[13682]: 18 Oct 12 16:44:44.020193 mysql_real_query error Table 'dbname.tblname_2012_10_15' doesn't exist
>
> mysql> show grants for 'rasearch'@'buttercup5.utdallas.edu';
> +-------------------------------------------------------------------------------------------------------------------------------------+
> | Grants for rasearch at buttercup5.utdallas.edu |
> +-------------------------------------------------------------------------------------------------------------------------------------+
> | GRANT USAGE ON *.* TO 'user'@'buttercup5.utdallas.edu' IDENTIFIED BY PASSWORD '*4B86FBBF524DBBFF94B3CD641A1E895679B51444' |
> | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE VIEW, SHOW VIEW ON `dbname`.* TO 'user'@'buttercup5.utdallas.edu' |
> +-------------------------------------------------------------------------------------------------------------------------------------+
> 2 rows in set (0.00 sec)
>
> --On October 17, 2012 2:46:00 PM -0400 Carter Bullard <carter at qosient.com> wrote:
>
>> Hey Paul,
>> Not sure why you are generating the table? What happens when you let
>> rasqlinsert create the table? If you have multiple argi, you'll have to
>> add the srcid field.
>>
>> 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
>
>
>
> --
> 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