Querying Session Data Based on Snort Rule IPs

Sometimes Snort rules can contain useful information but are not practical to use in production. The Bleeding Snort rules recently added a set of rules to detect connection attempts to known compromised hosts. If you take a look at the rules, you'll see that it is essentially a large list of IP addresses that are known to be compromised.

When I first ran these rules on a Snort sensor, they definitely gave me some alerts requiring action. However, the performance of the sensor really suffered, particularly as the set of IP addresses grew. Since the rules were causing packet loss, I wanted to disable them.

I decided to write a script to grab the IP addresses from the Bleeding rule, then load the addresses into my database to compare with stored sancp data. If you are monitoring your network but not storing session data acquired with tools like sancp or argus, you should be. In my case, I am running Sguil, which uses sancp.

First, I had to write the script to grab the IP addresses. Since I just finished an introduction to Perl class in school and had many projects that required string matching, I figured that was the way to go. Since I'm a beginner, I would be more than happy if anyone can offer improvements to the following script. In particular, one thing I have not worked on yet is a method for expanding CIDR notation to individual IP addresses. The bleeding-compromised.rules do contain some networks in CIDR notation rather than just individual IP addresses, and for now the script simply strips the notation resulting in an incomplete IP list.

Edit: I posted an updated script that replaces the one in this post. I would suggest using the updated version rather than this one.

#!/usr/bin/perl
#
# Script to pull IP address from Snort rules file
# by nr
# 2007-08-30

# Set filenames
$rulefile = "/nsm/rules/bleeding-compromised.rules";
$ip_outfile = "iplist.txt";

# Print error unless successful open of file to read
die "Can't open rulefile.\n" unless open RULEFILE, "<", "$rulefile"; # Open file to write open IPLIST, ">", "$ip_outfile";

# Put each rule from rules file into array
chomp(@rule = <RULEFILE>);

# For each rule
foreach $rule (@rule) {
# Match only rules with IP addresses so we don't get comments etc
# This string match does not check for validity of IP addresses
if ( $rule =~ /\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}/ ) {
    # Remove everything before [ character
    $rule =~ s/.*\[//g;
    # Remove everything after ] character
    $rule =~ s/\].*//g;
    # Split the remaining data using the commas
    # and put it into ip_address array
    @ip_address = split /\,/, $rule;

    # For each IP address in array
    foreach $ip_address (@ip_address) {
        # Remove CIDR notation (means those IP ranges are missed - need to fix)
        $ip_address =~ s/\/.*//g;
        # Print to output file one IP per line
        print IPLIST "$ip_address\n";
        }
    }
}

# Close filehandles
close RULEFILE;
close IPLIST;

Now I have a file called "iplist.txt" with the list of IP addresses, one per line. Next, I log into MySQL and load the list into a temporary database and table. The table really only needs one column of the CHAR or VARCHAR data type. (See the MySQL documentation for creating tables or databases).

LOAD DATA LOCAL INFILE '/home/nr/iplist.txt' INTO TABLE temp.ipaddr;

Then I have to convert the IP addresses to the INT data type using the INET_ATON function so they can be matched against my sancp session data. I created the table "sguildb.ipaddresses" for cases like this where I want to load external IP address and then run a query. The "temp.ipaddr" table has one column called "new_ip". The "sguildb.ipaddresses" table also has one column, but called "dst_ip".

INSERT INTO sguildb.ipaddresses SELECT INET_ATON(new_ip) FROM temp.ipaddr;

In MySQL 5.x, you can combine the previous two steps to add and convert the data in one step. I'm currently running 4.1, so I have not investigated the exact syntax.

Finally, I can query my sancp session data for connections going to any of the IP addresses in sguildb.ipaddresses, which are the IP addresses from the Snort rule.

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 ipaddresses ON
(sancp.dst_ip = ipaddresses.dst_ip) WHERE sancp.start_time >= DATE_SUB(UTC_DATE(),
INTERVAL 24 HOUR) AND sancp.dst_port = '80';

This query will return the sensor ID, source IP, source port, destination IP, destination port, and session start time from the sancp table wherever the sancp.dst_ip matches the ipaddresses.dst_ip where I stored the IP addresses from the Snort rule. Notice that it will query the last 24 hours for port 80 connections only. Depending on the type of activity you are looking for, you could change ports or remove the port match entirely.

This whole process could be automated further by running the mysql commands directly from the Perl script so the compromised IP addresses are updated in the database when the script is run.

The final SQL query to match the compromised IP addresses with sancp destination IP addresses can easily be turned into a cronjob. For instance, if querying for the past 24 hours then run the cronjob once a day. Once the results are returned, if running Sguil with full content logging it is easy to then query for the individual connections you're interested in and view the ASCII transcripts or the packet captures.

Tweet