Wednesday, December 21, 2005

Changes Coming in Sguil 0.6.1

Sguil 0.6.0p1 introduced the use of MERGE tables in MySQL to improve database performance.

Sguil 0.6.1, in development now, will bring UNION functionality to database queries. This will also improve performance.

Consider the following standard event or alert query in Sguil. This query says return Snort alerts where 151.201.11.227 is the source IP OR the destination IP. OR is a slow operation compared to UNION. Sguil 0.6.1 will use a new query.

Here we look for Snort alerts where 220.98.198.35 is the source IP address, and use UNION to return those results with alerts where 220.98.198.35 is the destination IP address.

UNION functionality was not available in MySQL 3.x, but it appeared in 4.x. Many Sguil users are running MySQL 5.x now.

Those screen shots just show the WHERE portions of the database queries. Here is each version of similar queries look like in their entirety:

Sguil 0.5.3 and older:

SELECT sensor.hostname, sancp.sancpid, sancp.start_time as datetime, sancp.end_time,
INET_NTOA(sancp.src_ip), sancp.src_port, INET_NTOA(sancp.dst_ip), sancp.dst_port,
sancp.ip_proto, sancp.src_pkts, sancp.src_bytes, sancp.dst_pkts, sancp.dst_bytes
FROM sancp
IGNORE INDEX (p_key)
INNER JOIN sensor ON sancp.sid=sensor.sid
WHERE sancp.start_time > '2005-08-02' AND ( sancp.src_ip = INET_ATON('82.96.96.3') OR
sancp.dst_ip = INET_ATON('82.96.96.3') )

EXPLAIN
+----+-------------+--------+--------+--------------------------+---------+---------+-------------------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+--------------------------+---------+---------+-------------------+-----------+-------------+
| 1 | SIMPLE | sancp | ALL | src_ip,dst_ip,start_time | NULL | NULL | NULL | 100458818 | Using where |
| 1 | SIMPLE | sensor | eq_ref | PRIMARY | PRIMARY | 4 | sguildb.sancp.sid | 1 | |
+----+-------------+--------+--------+--------------------------+---------+---------+-------------------+-----------+-------------+

The actual query returns an empty set after 5mins 29.14secs on Bamm's database.

Sguil 0.6.0p1:

(
SELECT sensor.hostname, sancp.sancpid, sancp.start_time as datetime, sancp.end_time,
INET_NTOA(sancp.src_ip), sancp.src_port, INET_NTOA(sancp.dst_ip), sancp.dst_port,
sancp.ip_proto, sancp.src_pkts, sancp.src_bytes, sancp.dst_pkts, sancp.dst_bytes
FROM sancp
IGNORE INDEX (p_key)
INNER JOIN sensor ON sancp.sid=sensor.sid
WHERE sancp.start_time > '2005-08-02' AND sancp.src_ip = INET_ATON('82.96.96.3')
) UNION (
SELECT sensor.hostname, sancp.sancpid, sancp.start_time as datetime, sancp.end_time,
INET_NTOA(sancp.src_ip), sancp.src_port, INET_NTOA(sancp.dst_ip), sancp.dst_port,
sancp.ip_proto, sancp.src_pkts, sancp.src_bytes, sancp.dst_pkts, sancp.dst_bytes
FROM sancp
IGNORE INDEX (p_key)
INNER JOIN sensor ON sancp.sid=sensor.sid
WHERE sancp.start_time > '2005-08-02' AND sancp.dst_ip = INET_ATON('82.96.96.3')
)

EXPLAIN
+----+--------------+------------+--------+-------------------+---------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+-------------------+---------+---------+-------------------+------+-------------+
| 1 | PRIMARY | sancp | ref | src_ip,start_time | src_ip | 5 | const | 108 | Using where |
| 1 | PRIMARY | sensor | eq_ref | PRIMARY | PRIMARY | 4 | sguildb.sancp.sid | 1 | |
| 2 | UNION | sancp | ref | dst_ip,start_time | dst_ip | 5 | const | 108 | Using where |
| 2 | UNION | sensor | eq_ref | PRIMARY | PRIMARY | 4 | sguildb.sancp.sid | 1 | |
|NULL| UNION RESULT | union1,2 | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+-------------------+---------+---------+-------------------+------+-------------+

The actual query returns an empty set in .33secs on Bamm's database.

3 comments:

Anonymous said...

This seems more complex on the human end. I would rather offload that work. But my db is not so taxed that I can make it sweat with a query. YMMV, esp at a big shop.

Anonymous said...

I agree, it is slightly more complex on the human end. Howerver, I expect most analysts rarely edit the WHERE conditions of the queries beyond changing the timestamp, so I don't think the change will have much of a negative impact. On the other hand, the speed increases for those of us running big DBs on not so big hardware is huge.

Of course, any recommendations for a cleaner/friendlier interface are welcome. I openly admit I am not the greatest at UI design (I am using Tk for gawds sake). Rich has made more (accepted) recommendations than I can count. I've gone so far as to to coin a term to describe my UI design ineptness, but we won't mention that here ;)

Bammkkkk

Anonymous said...

This functionality would be most exellent! As it stands now I keep a mysql prompt available at all times to run queries like this. And yes, I also heavily modify the WHERE clause.