[Snort-users] Getting snort data into a database

Sean Brown srbrown at ...1498...
Mon Mar 12 16:24:20 EST 2001


I wrote a small perl script that will do this automatically.  You'll
need to modify some of the configuration parameters, but it will take
the ascii logs and dump them into a MySQL DB.  Be sure to read the
comments in the code before using it.

Sean

Paul Asadoorian wrote:
> 
> Hello all,
> 
> 
> Is there a way to take snort logs that have already been written to
> disk in text format (portscan, alerts, and packet logs) and dump them
> into a database for analyzing with acid?  I know you can do it easily
> if they are in tcpdump binary format but I have never done when they
> are in text format.
> 
> Thanks,
> 
> Paul

-- 
~~~~~~~~~~~~~~~
Sean R. Brown - srbrown at ...1498...
System Administrator   Applied Geographics, Inc.   Boston, MA
-------------- next part --------------
#!/usr/bin/perl
#
# $Id: alert2db.pl, v.1.0 2001/01/12 16:42:34 srbrown Exp $
# $Revision: 1.0 $
#
# Description: alert2db.pl is a perl script used to import snort v1.6x
# alert logs into a mysql database for use in historical analysis.  I
# have found this a great way to get historical alerts available for
# review in ACID.  Only the header information is currently imported
# but the data payload is on the TODO list.  Also, TCP options are not
# recorded.
#
# Any signatures that are not pattern matched are dumped to the STDOUT
# for review.  So far, this has been a relatively small percentage.
# In a recent upload of an alert log with 30,000+ signatures, alert2db
# only missed 48 signatures.
#
# Usage: cat <snort_alert> | alert2db.pl
#
# Dependancies: Perl DBI module, available from CPAN (www.cpan.org)
#
# Some of the ideas for this code came from the snort_stat.pl script.
# Also, portions of this code were derived from the following:
#       dot2dec.pl - converts dotted-quad IP addresses to their
#       decimal equivalents
#       By echofour on 4/3/99
#
#
# Copyright (C) 2001, Sean R. Brown
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#
# $Author: Sean Brown, <srbrown at ...1543...> $
# $Date: 2001/01/12 16:42:34 $
#
##
# Set some variables for your configuration
##
$host = neo;	# Change to database host
$snort_db = snort;	# Snort database
$db_user = suser;	# Database username
$db_pwd = "";	# Database password
$sensor_id = 2;	# Which sensor the logs are from.  Since
			# snort did not record this pre-1.7 you need
			# to suppply the correct sensor ID from your
			# database. Use:
			# select * from sensor;
			# to get the sensor ID for this entry.
$debug = 0;		# Set to 1 for debugging
$year = 2000;		# Enter year logs were obtained
#
##
# End of user defined values
##

# Open database connection
use DBI();		# Database Interface
$dbh = DBI->connect("DBI:mysql:database=$snort_db;host=$host",
	$db_user, $db_pwd, {'RaiseError' => 1})
	|| die "Unable to connect to DB: $dbh->errstr\n";
 
sub btod($addr) {
        # Get dotted-quad address 
        $addr = shift; $sum =  0;

        # We're adding an extra step to the regular expression to
        # make it slightly more readable.
        # $ln is a _l_egal _n_umber in an IP address - that is, a number
        # from 0 to 255.  One or two-digit numbers may be zero-padded on
        # the left.
 
        $ln = '([01]?\d\d|2[0-4]\d|25[0-5]|0?0?\d)';
 
        # If the argument to the script isn't four dot-separated legal
        # numbers and nothing else, quit right now.
 
        die "Invalid IP address $addr:$!" if ($addr !~ /^$ln\.$ln\.$ln\.$ln$/);
 
        # Divide the address into its components
        @digits = split (/\./, $addr);
 
        #  Let's start from the smaller numbers and work up.  Since
        #  IP addresses go from larger to smaller, we'll first reverse
        #  the order of our numbers.
 
        @digits = reverse @digits;
        for ($i = 0; $i <= 3; ++$i) {

          #  Multiply the smallest number by 1, the second smallest by
          #  256, the third smallest by 256 squared, and the largest by
          #  256 cubed, and add the result to our running total $sum.
 
          $sum += $digits[$i] * 256 ** $i;
        }
 
	$debug && print STDERR "Network Byte Order Address: $sum\n";
        return $sum;
} 

# Get the maximum cid for the chosen sensor
sub getcid() {

	my $sth = $dbh->prepare("SELECT max(cid) from event WHERE sid = $sensor_id");
	$sth->execute() || die "Unable to execute query: $dbh->errstr\n";
	my $ref = $sth->fetchrow_arrayref || die "Unable to fetchrow: $dbh->errstr\n";
	$maxcid = $$ref[0];
	$debug && print STDERR "Max CID: $maxcid\n";
	return $maxcid;
}

# Take care of inserting the ip headers and event info
sub hdrs($sensor_id, $max_cid, $sig, $timestamp, $sensor_id, $max_cid, $ip_src,
	@src_addr, $ip_dst, @dst_addr, $iplen, $iptos, $ipid, $ipttl, $ip_proto) {

    # Insert event information to the DB
    $dbh->do("INSERT INTO event (sid, cid, signature, timestamp) VALUES (?,?,?,?)", 
	undef, $sensor_id, $max_cid, $sig, $timestamp)
	|| die "Unable to execute query: $dbh->errstr\n";

    # Insert ip header info to the DB
    $dbh->do("INSERT INTO iphdr (sid, cid, ip_src, ip_src0, ip_src1, ip_src2, ip_src3,".
	"ip_dst, ip_dst0, ip_dst1, ip_dst2, ip_dst3," .
	"ip_len, ip_tos, ip_id, ip_ttl, ip_proto) VALUES".
	"(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
	undef, $sensor_id, $max_cid, $ip_src, $src_addr[0], $src_addr[1],
	$src_addr[2], $src_addr[3], $ip_dst, $dst_addr[0], $dst_addr[1],
	$dst_addr[2], $dst_addr[3], $iplen, $iptos, $ipid, $ipttl, $ip_proto)
	|| die "Unable to execute query: $dbh->errstr\n";
}
#
# set some counters
$t = 0 ; $a = 0;
# This is the main loop which populates the array of alerts which
# we'll iterate through and upload to the DB
MAIN: while (<>) {
	chomp;
	next MAIN if /^\=\+\=\+\=\+\=\+\=/ ;
	$alert = "" ;	# reset $alert to an empty string
	$alert = $_ if /^\[\*\*\]/;  # Signature title
	$t++;
	ALERT: while(<>) {
	    chomp;
	    # move to next alert if at the end of an alert record
	    if($_ eq "" || /^\=\+\=\+\=\+\=\+\=/) {
		push @alerts,$alert;
		next MAIN;
	    }
	    $alert = $alert . " " . $_;
	}
}
# Pattern matching for signatures
foreach (@alerts) {
    #TCP Alerts
    if ($_ =~ m/^\s*\[\*\*\]\s(.*)\[\*\*\]\s*
	(\d+)\/(\d+)\-(\d+)\:(\d+)\:(\d+)\.(\d+).*type\:0x(\S+)\slen\:0x(\S+)\s*
	([\d\.]+)[\:]*([\d]*)\s+\-\>\s+([\d\.]+)[\:]*([\d]*)\s+(TCP)\sTTL\:(\d+)\sTOS\:0x(\S+)\sID\:(\d+).*
        (.)(.)(.)(.)(.)(.)(.)(.)\sSeq\:\s0x(\S+)\s+Ack\:\s0x(\S+)\s+Win\:\s+0x(\S+)/ox ) {

	$a++;	#increment the counter
	$sig = $1;
	$month = $2; $day = $3; $hour = $4; $minute = $5; $second = $6; $iptype = hex $8; $iplen = hex $9;
	$saddr = $10; $sport  = $11; $daddr = $12; $dport = $13; $ip_proto = 6; $ipttl = $15;
	$iptos = hex $16; $ipid = $17;
	$flg[0]=$18;$flg[1]=$19;$flg[2]=$20;$flg[3]=$21;$flg[4]=$22;$flg[5]=$23;$flg[6]=$24;$flg[7]=$25;
	$tcp_seq = hex $26; $tcp_ack = hex $27; $tcp_win = hex $28;

	# Get TCP flag value
	$flags = 0;	# reset $flag for next record
	foreach (@flg) {
	    $flags += 1 if /F/ ;
	    $flags += 2 if /S/ ;
	    $flags += 4 if /R/ ;
	    $flags += 8 if /P/ ;
	    $flags += 16 if /A/ ;
	    $flags += 32 if /U/ ;
	    $flags += 64 if /2/ ;
	    $flags += 128 if /1/ ;
	}

	# Create a mysql compliant date string
	$timestamp = "$year-$month-$day $hour:$minute:$second";

	# get the Network byte order number for the src and dst addresses
	$ip_src = &btod($saddr); $ip_dst = &btod($daddr);
	# create an array from the dotted decimal addresses
	@src_addr = split (/\./, $saddr); @dst_addr = split (/\./, $daddr);

	$debug && print STDERR "Signature: $sig\n";
	$debug && print STDERR "Timestamp: $timestamp\n";

	$max_cid = &getcid() + 1;	# increment cid for this record
	# insert common headers
	&hdrs($sensor_id, $max_cid, $sig, $timestamp, $sensor_id, $max_cid, $ip_src,
        @src_addr, $ip_dst, @dst_addr, $iplen, $iptos, $ipid, $ipttl, $ip_proto);
	# insert tcp specific header info
	$dbh->do("INSERT INTO tcphdr (sid, cid, tcp_sport, tcp_dport, tcp_seq, tcp_ack,".
                "tcp_flags, tcp_win) VALUES (?,?,?,?,?,?,?,?)",
               	undef, $sensor_id, $max_cid, $sport, $dport, $tcp_seq, $tcp_ack,
                $flags, $tcp_win)
		|| die "Unable to execute query: $dbh->errstr\n";

    } elsif ($_ =~ m/^\s*\[\*\*\]\s(.*)\[\*\*\]\s*	# ICMP alert packet capture
	(\d+)\/(\d+)\-(\d+)\:(\d+)\:(\d+)\.(\d+).*type\:0x(\S+)\s+len\:0x(\S+)\s*
	([\d\.]+)\s+\-\>\s+([\d\.]+)\s+(ICMP)\s+TTL\:(\d+)\s+TOS\:0x(\S+)\sID\:(\d+).*
        ID\:(\d+)\s+Seq\:(\d+)\s+([\D\s]+)/ox ) {

	$a++;	#increment the counter
	$sig = $1;
	$month = $2; $day = $3; $hour = $4; $minute = $5; $second = $6; $iptype = hex $8; $iplen = hex $9;
	$saddr = $10; $daddr = $11; $ip_proto = 0 ; $ipttl = $13; $iptos = hex $14; $ipid = $15;
	$icmp_id = $16; $icmp_seq = hex $17; chop($icmp_desc = $18);

	# Get the icmp code and type from the text description in the snort alert
	$icmp_code = 0; $icmp_type = 0;
	if($icmp_desc eq "ECHO") {$icmp_code = 0; $icmp_type = 8;}

	# Create a mysql compliant date string
	$timestamp = "$year-$month-$day $hour:$minute:$second";
	$ip_src = &btod($saddr); $ip_dst = &btod($daddr);
	@src_addr = split (/\./, $saddr); @dst_addr = split (/\./, $daddr);

	$debug && print STDERR "Signature: $sig\n";
	$debug && print STDERR "Timestamp: $timestamp\n";

	$max_cid = &getcid() + 1;	# increment cid for this record
	# insert common headers
	&hdrs($sensor_id, $max_cid, $sig, $timestamp, $sensor_id, $max_cid, $ip_src,
        @src_addr, $ip_dst, @dst_addr, $iplen, $iptos, $ipid, $ipttl, $ip_proto);

	# insert tcp specific header info
	$dbh->do("INSERT INTO icmphdr (sid, cid, icmp_type, icmp_code,".
                "icmp_id, icmp_seq) VALUES (?,?,?,?,?,?)",
               	undef, $sensor_id, $max_cid, $icmp_type,
		$icmp_code, $icmp_id, $icmp_seq)
		|| die "Unable to execute query: $dbh->errstr\n";

    } elsif ($_ =~ m/^\s*\[\*\*\]\s(.*)\[\*\*\]\s*	# UDP alert packet captures
	(\d+)\/(\d+)\-(\d+)\:(\d+)\:(\d+)\.(\d+).*type\:0x(\S+)\s+len\:0x(\S+)\s*
	([\d\.]+)[\:]*([\d]*)\s+\-\>\s+([\d\.]+)[\:]*([\d]*)\s+(UDP)\sTTL\:(\d+)\sTOS\:0x(\S+)\sID\:(\d+).*
        Len\:\s+(\d+)/ox ) {

	$a++;	#increment the counter
	$sig = $1;
	$month = $2; $day = $3; $hour = $4; $minute = $5; $second = $6; $iptype = hex $8; $iplen = hex $9;
	$saddr = $10; $sport  = $11; $daddr = $12; $dport = $13; $ip_proto = 17; $ipttl = $15;
	$iptos = hex $16; $ipid = $17; $udp_len = $17;

	# Create a mysql compliant date string
	$timestamp = "$year-$month-$day $hour:$minute:$second";
	$ip_src = &btod($saddr); $ip_dst = &btod($daddr);
	@src_addr = split (/\./, $saddr); @dst_addr = split (/\./, $daddr);

	$debug && print STDERR "Signature: $sig\n";
	$debug && print STDERR "Timestamp: $timestamp\n";

	$max_cid = &getcid() + 1;	# increment cid for this record
	# insert common headers
	&hdrs($sensor_id, $max_cid, $sig, $timestamp, $sensor_id, $max_cid, $ip_src,
        @src_addr, $ip_dst, @dst_addr, $iplen, $iptos, $ipid, $ipttl, $ip_proto);

	# insert tcp specific header info
	$dbh->do("INSERT INTO udphdr (sid, cid, udp_sport, udp_dport, udp_len) VALUES (?,?,?,?,?)",
            	undef, $sensor_id, $max_cid, $sport, $dport, $udp_len)
		|| die "Unable to execute query: $dbh->errstr\n";
    } else {
	    print "Unrecorded signature: $_\n";
    }
}	

$dbh->disconnect;

printf("Total number of alerts : %-6d\n",$t);
printf("Recorded alerts        : %-6d\n",$a);


More information about the Snort-users mailing list