rasqlinsert -s -record doesn't seem to work
Dave Edelman
dedelman at iname.com
Mon Oct 22 21:47:47 EDT 2012
Sorry,
MySQL needs
C - alter table playpen add column ts timestamp FIRST;
--Dave
From: argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu
[mailto:argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu] On
Behalf Of Dave Edelman
Sent: Monday, October 22, 2012 9:24 PM
To: 'Carter Bullard'; 'Paul Schmehl'
Cc: 'Argus List'
Subject: Re: [ARGUS] rasqlinsert -s -record doesn't seem to work
I think that there are three things that may make it easier to figure this
out.
1 - What are the contents of /home/pauls/tablename.sql (you can obscure the
field names)
2 - What is the value for the time format string RA_TIME_FORMAT in
rasqlinsert.conf also for RA_PRINT_UNIX_TIME
3 - What version of MySQL is Paul running?
Paul, I think that you want to try something like this:
1 - Drop the table and do not source the file that creates it.
2 - Run rasqlinsert as below but without the -s -record option
3 - Invoke MySQL and
A - CREATE TABLE playpen LIKE tablename;
B - ALTER TABLE playpen DROP COLUMN record;
(I'll stop the all uppercase nonsense)
C - alter table playpen add column ts timestamp before
nameoffirstcolumngoeshere;
D- rename table tablename to tablename_X, playpen to
tablename;
4 - Run rasqlinsert this time with the -s -record option and the -m none (if
you have an RA_TIME_FORMAT string, comment it out and use
RA_PRINT_UNIX_TIME=yes)
5 - Tell us what happened. It would be nice to see the results of:
show full columns from tablename_X;
show full columns from tablename;
select * from tablename limit 3;
--Dave
From: argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu
[mailto:argus-info-bounces+dedelman=iname.com at lists.andrew.cmu.edu] On
Behalf Of Carter Bullard
Sent: Monday, October 22, 2012 3:41 PM
To: Paul Schmehl
Cc: Argus List
Subject: Re: [ARGUS] rasqlinsert -s -record doesn't seem to work
> Old - S/MIME Signed by an unverified key: 10/22/2012 at 3:41:20 PM
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
<mysql://root@localhost/ratest/testDb_%25Y_%25m_%25d> -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
<mysql://root@localhost/ratest/testDb_%25Y_%25m_%25d> -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
* Carter Bullard <carter at qosient.com>
* Issuer: "VeriSign - Unverified
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://pairlist1.pair.net/pipermail/argus/attachments/20121022/4bc8a2cb/attachment.html>
More information about the argus
mailing list