Re: Sguil DB table names

[Doug Burks <doug.burks () gmail com>]

Hi Y M,

Are you using MySQL 5.5?

Have you seen this?
http://marc.info/?l=sguil-devel&m=131291794011285

You can also take a look at Security Onion if you'd like something to refer
to.

Hope that helps!

Thanks,
Doug

On Sunday, May 12, 2013, Y M wrote:

> Adding error logs...
>
> As soon as an event is generated, barnyard2 outputs:
>
> Waiting for new data
> 05/12-14:51:43.476294  [**] [1:15168:12] INDICATOR-COMPROMISE Suspicious
> .ru dns query [**] [Classification: A Network Trojan was detected]
> [Priority: 1] {UDP} 192.168.10.133:58643 -> 192.168.10.2:53
> ERROR: sguil: Expected Confirm 1 and got: Failed to insert 1: mysqlexec/db
> server: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'TYPE=MERGE UNION=(`event_ids-test_20130512`)' at line 1
>
> On the Sguil server, this gets out:
>
> 2013-05-12 11:51:43 pid(19328)  Sensor Data Rcvd: BYEventRcvd sock7 0 1 1
> ids-test 1 1 {2013-05-12 14:51:43} 1 15168 12 {INDICATOR-COMPROMISE
> Suspicious .ru dns query} {2013-05-12 14:51:43} 1 trojan-activity
> 3232238213 192.168.10.133 3232238082 192.168.10.2 17 4 5 0 61 10112 0 0 128
> 32088 {} {} {} {} {} 58643 53 {} {} {} {} {} {} {} {} 41 27739
> 6BE9010000010000000000000377777708746C7367726F75700272750000010001
> 2013-05-12 11:51:43 pid(19328)  Creating event table
> event_ids-test_20130512.
> 2013-05-12 11:51:43 pid(19328)  Creating tcphdr table
> tcphdr_ids-test_20130512.
> 2013-05-12 11:51:43 pid(19328)  Creating udphdr table
> udphdr_ids-test_20130512.
> 2013-05-12 11:51:43 pid(19328)  Creating icmphdr table
> icmphdr_ids-test_20130512.
> 2013-05-12 11:51:43 pid(19328)  Creating data table data_ids-test_20130512.
> 2013-05-12 11:51:43 pid(19328)  Creating event MERGE table.
> 2013-05-12 11:51:43 pid(19328)  ERROR: While inserting event info:
> mysqlexec/db server: You have an error in your SQL syntax; check the manual
> that corresponds to your MySQL server version for the right syntax to use
> near 'TYPE=MERGE UNION=(`event_ids-test_20130512`)' at line 1
> 2013-05-12 11:51:43 pid(19328)  Sent sock15: Failed sock7 1 {mysqlexec/db
> server: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'TYPE=MERGE UNION=(`event_ids-test_20130512`)' at line 1}
> 2013-05-12 11:51:43 pid(19328)  Sensor Data Rcvd: SystemMessage {Barnyard
> disconnected.}
>
> If I delete the tables (event_ids-test_20130512, icmp_ids-test_20130512,
> etc.) and run barnyard2 again with Sguil sensor and server running, the
> following gets generated:
>
> Error: mysqlsel/db server: Table 'sguildb.event' doesn't exist
> mysqlsel/db server: Table 'sguildb.event' doesn't exist
>     while executing
> "mysqlsel $MAIN_DB_SOCKETID $query -flatlist"
>     (procedure "FlatDBQuery" line 5)
>     invoked from within
> "FlatDBQuery "SELECT MAX(cid) FROM event WHERE sid=$sid""
>     (procedure "GetMaxCid" line 7)
>     invoked from within
> "GetMaxCid $sid"
>     (procedure "AgentLastCidReq" line 3)
>     invoked from within
> "AgentLastCidReq $socketID [lindex $data 1] [lindex $data 2] "
>     (procedure "SensorCmdRcvd" line 38)
>     invoked from within
> "SensorCmdRcvd sock15"
> SGUILD: killing child procs...
> SGUILD: Exiting...
>
> ------------------------------
> From: snort () outlook com <javascript:_e({}, 'cvml', 'snort () outlook com');>
> To: snort-users () lists sourceforge net <javascript:_e({}, 'cvml',
> 'snort-users () lists sourceforge net');>
> Date: Sun, 12 May 2013 05:40:04 +0000
> Subject: [Snort-users] Sguil DB table names
>
>  This is not strictly a Snort question, it is more related to Sguil and
> hoping that someone may have insight into this.
>
> I have a Sguil sensor setup to only use the snort_agent. My understanding
> of Sguil is that as soon as a sensor reports an alert to the server for the
> first time, it will create the event, data, icmphdr, iphdr, tcphdr, and
> udphdr tables into the Sguil DB on the server.
>
> That said, my setup does create the tables, however, it appends the sensor
> name and a date stamp to table names. For example, my sensor name is
> "ids-test" and the date is 12 May 2013. In this case, the tables created
> will have the following naming convention: tablename_sensorname_datestamp
> --> event_ids-test_20130512. This happens to all tables that get created.
>
> This results in the following error:
> ERROR: sguil: Expected confirm 1 and got: Failed to insert 1: mysqlexec/db
> server: You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use near
> 'TYPE=MERGE UNION=('event_ids-test_20130512')' at line 1
>
> If I rename the tables to the proper names, and run Sguil again, I get the
> following error:
> ERROR: sguil: Expected confirm 1 and got: Failed to insert 1: mysqlexec/db
> server: Table 'sguildb.event_ids-test_20130512' does not exist.
>
> If I drop the created tables and run the process, the same error (first
> one) occurs again.
>
> Am I missing something in the configurations (Barnyard2 or
> snort_agent.conf) or the entire setup?
>
> Thanks
> YM
>
>
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is
> the definitive new guide to graph databases and their applications. This
> 200-page book is written by three acclaimed leaders in the field. The early
> access version is available now. Download your free book today!
> http://p.sf.net/sfu/neotech_d2d_may
> _______________________________________________ Snort-users mailing list
> Snort-users () lists sourceforge net <javascript:_e({}, 'cvml',
> 'Snort-users () lists sourceforge net');> Go to this URL to change user
> options or unsubscribe: https://lists.sourceforge.net/lists/listinfo/snort-users
> Snort-users<https://lists.sourceforge.net/lists/listinfo/snort-usersSnort-users>list archive:
> http://sourceforge.net/mailarchive/forum.php?forum_name=snort-usersPlease visit
> http://blog.snort.org to stay current on all the latest Snort news!


-- 
Doug Burks
http://securityonion.blogspot.com

------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and 
their applications. This 200-page book is written by three acclaimed 
leaders in the field. The early access version is available now. 
Download your free book today! http://p.sf.net/sfu/neotech_d2d_may

_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://sourceforge.net/mailarchive/forum.php?forum_name=snort-users

Please visit http://blog.snort.org to stay current on all the latest Snort news!