I ran across problems in ACID when trying to cleanup large amounts of Snort Alerts, perl to the rescue! Anyway, it probably could be better but it works for me. Let me know if you think of improvements. 
#!/usr/bin/perl -w
#----------------------------------------
# name: alert_cleanup.pl 
#
# description: script to cleanup snort/acid db (only tested w/mysql)
#
# goal: allows you to schedule db cleanup without using php frontend
#
# usage: snort_db_cleanup.pl "2003-04-01 8:00:00" "2003-04-01 9:00:00"
#
# comments: dusty hall, halljer@<NOSPAM>auburn.edu
#----------------------------------------

use strict;
use DBI;

my $ds = "dbi:mysql:snort";
my $db_user = "acid_user";
my $db_pass = "secret";
my $db = DBI->connect($ds, $db_user, $db_pass) or die $DBI::errstr;

my ($cid,$sid,$sql,$time_select,$exec_time_select);
my ($event,$iphdr,$tcphdr,$udphdr,$icmphdr,$opt,$data,$acid_ag_alert,$
+acid_event);
my ($exec_event,$exec_iphdr,$exec_tcphdr,$exec_udphdr,$exec_icmphdr,$e
+xec_opt,$exec_data,$exec_acid_ag_alert,$exec_acid_event);
my %timeframe;

$timeframe{start} = $ARGV[0];
$timeframe{finish} = $ARGV[1];
chomp $timeframe{start};
chomp $timeframe{finish};

$time_select = "select acid_event.sid,acid_event.cid from acid_event w
+here timestamp >= '$timeframe{start}' and timestamp <= '$timeframe{fi
+nish}'"; 
$exec_time_select = $db->prepare($time_select);

$exec_time_select->execute();
$exec_time_select->bind_columns(undef,\$sid,\$cid);

while ($exec_time_select->fetch) {

 $event = "delete from event where sid='$sid' and cid='$cid'";
 $iphdr = "delete from iphdr where sid='$sid' and cid='$cid'";
 $tcphdr = "delete from tcphdr where sid='$sid' and cid='$cid'";
 $udphdr = "delete from udphdr where sid='$sid' and cid='$cid'";
 $icmphdr = "delete from icmphdr where sid='$sid' and cid='$cid'";
 $opt = "delete from opt where sid='$sid' and cid='$cid'";
 $data = "delete from data where sid='$sid' and cid='$cid'";
 $acid_ag_alert = "delete from acid_ag_alert where ag_sid='$sid' and a
+g_cid='$cid'";
 $acid_event = "delete from acid_event where sid='$sid' and cid='$cid'
+";
 
 $exec_event = $db->prepare($event);
 $exec_iphdr = $db->prepare($iphdr);
 $exec_tcphdr = $db->prepare($tcphdr);
 $exec_udphdr = $db->prepare($udphdr);
 $exec_icmphdr = $db->prepare($icmphdr);
 $exec_opt = $db->prepare($opt);
 $exec_data = $db->prepare($data);
 $exec_acid_ag_alert = $db->prepare($acid_ag_alert);
 $exec_acid_event = $db->prepare($acid_event);

 $exec_event->execute(); 
 $exec_iphdr->execute(); 
 $exec_tcphdr->execute(); 
 $exec_udphdr->execute(); 
 $exec_icmphdr->execute(); 
 $exec_opt->execute(); 
 $exec_data->execute(); 
 $exec_acid_ag_alert->execute(); 
 $exec_acid_event->execute(); 

 $exec_event->finish(); 
 $exec_iphdr->finish(); 
 $exec_tcphdr->finish(); 
 $exec_udphdr->finish(); 
 $exec_icmphdr->finish(); 
 $exec_opt->finish(); 
 $exec_data->finish(); 
 $exec_acid_ag_alert->finish(); 
}

$exec_time_select->finish;

[download]

Replies are listed 'Best First'.
•Re: Cleanup ALerts in Snort/ACID Mysql DB
by merlyn (Sage) on Apr 03, 2003 at 23:10 UTC
    Wow. Too bad MySQL doesn't have foreign keys with ON DELETE CASCADE support, like PostgreSQL has had for a few years already.

    Would be a great time saver. Might be easier just to install PostgreSQL.

    -- Randal L. Schwartz, Perl hacker
    Be sure to read my standard disclaimer if this is a reply.

[reply]

      I don't want to start up a PostgreSQL vs MySQL debate (as it is comparing apples and oranges) but if you make use of InnoDB tables then you can specify foreign keys and cascading deletes.

      MySQL Documentation

[reply]
Re: Cleanup ALerts in Snort/ACID Mysql DB
by VSarkiss (Monsignor) on Apr 04, 2003 at 01:18 UTC

    Your code has a lot of regularity, but you're not exploiting that. You can reduce a lot of potential maintenance headaches by doing so. I'd rewrite it as follows. 

    use strict;
use DBI;

# Check arguments first
if (@ARGV != 2)
{
    die "Need exactly two args, got ", scalar(@ARGV), "\n";
}

# Set up all our SQL
my $time_sql =
    'select sid, cid from acid_event where timestamp between ? and ?';

my @tab_sql;
foreach (qw(event iphdr tcphdr udphdr icmphdr
        opt data acid_ag_alert acid_event))
{
    push @tab_sql =>
    "delete from $_ where "
    . ($_ eq 'acid_ag_alert'             # this one's different
        ? 'ag_sid = ? and ag_cid = ?'
        : 'sid = ?    and cid = ?');
}

# Connect to the database
my $db = DBI->connect(qw(dbi:mysql:snort acid_user secret))
    or die "Can't connect: $DBI::errstr\n";

# Get outer level data
my ($outer_sth, $inner_sth);
my ($sid, $cid);

eval {
    $outer_sth = $db->prepare($time_sql);
    $outer_sth->execute(@ARGV);
    $outer_sth->bind_columns(undef, \$sid, \$cid);
};

die $@ if $@;

# Loop for each row of data
while ($outer_sth->fetch())
{
    foreach (@tab_sql)
    {
    eval {
        $inner_sth = $db->prepare($_);
        $inner_sth->execute($sid, $cid);
        $inner_sth->finish();            # not really necessary
    };
    die $@ if $@;
    }
}

$outer_sth->finish();         # not really necessary

    [download]
    The whole trick, if you can call it that, is to notice that all your SQL statements vary in the table name, and only once in the column name. It's gravy after that.

    Note, I've only tested this to a limited extent since I don't have snort.

[reply]
[d/l]

Back to Cool Uses for Perl