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 - srbrownappgeo.com
System Administrator   Applied Geographics, Inc.   Boston, MA

#!/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, <srbrownnyx.net> $ # $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);

_______________________________________________ Snort-users mailing list Snort-userslists.sourceforge.net Go to this URL to change user options or unsubscribe: http://lists.sourceforge.net/lists/listinfo/snort-users

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]