Wednesday, December 21, 2005

Changes Coming in Sguil 0.6.1

Sguil 0.6.0p1 introduced cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 following standard event or alert query in Sguil. This query says return Snort alerts where 151.201.11.227 is cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 source IP OR cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 source IP address, and use UNION to return those results with alerts where 220.98.198.35 is cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 WHERE portions of cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 database queries. Here is each version of similar queries look like in cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365ir 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 human end. I would racá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365r 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 human end. Howerver, I expect most analysts rarely edit cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 WHERE conditions of cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 queries beyond changing cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 timestamp, so I don't think cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 change will have much of a negative impact. On cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 ocá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365r hand, cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 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 cá cược thể thao bet365_cách nạp tiền vào bet365_ đăng ký bet365 WHERE clause.