Querying session data based on Bleeding Snort compromised IPs

Recently Bleeding Edge added a new rulesets to their list of rules, to detect connection attempts to known compromised hosts. This is quite handy, although it will increase the load of each Snort process on each sensor, and even to the point of dropping packets.(!!)

When I was checking some sites, I came across this post on Eating Security

He posted an article on extracting the IPs from the rulesets and cross reference them against session data, which in my case against sancp.

To extract each IP from the ruleset, he’s using the following Perl script. Then import the extracted IP list into a table and then cross reference the extracted IP list with sancp session data.

To import the data into MySQL version 5.x database.

CREATE TABLE compromised (dst_ip int(10) unsigned NULL);

LOAD DATA LOCAL INFILE '/tmp/output' INTO TABLE compromised (@col1) SET dst_ip = INET_ATON(@col1);

Then query the sancp session data for connections going to any of the IP addresses.

SELECT sancp.sid,INET_NTOA(sancp.src_ip),sancp.src_port,INET_NTOA(sancp.dst_ip), sancp.dst_port,sancp.start_time FROM sancp INNER JOIN compromised ON (sancp.dst_ip = compromised.dst_ip) WHERE sancp.start_time >= DATE_SUB(UTC_DATE(), INTERVAL 24 HOUR) AND sancp.dst_port = '80';

Finally you can run this from a cronjob and mail the output every day to your email address.