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.