Script to cleanup ACID/Snort Alerts in MySQL DB...

["Dusty Hall" <halljer () auburn edu>]

Gang,

  I just thought I'd pass this script along..  hopefully it will save
someone some time/grief.  The main reason I wrote it is because we are
still in the process of tweaking Snort and our number of Alerts get out
of hand quickly.  ACID's frontend to delete the Alerts timed out most of
the time and I wanted a way to schedule the cleanup of Alerts.. 

Later,


-Dusty


--CODE--

#!/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,$exec_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
where timestamp >= '$timeframe{start}' and timestamp <=
'$timeframe{finish}'"; 
$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
ag_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;

--CODE--


-------------------------------------------------------
This SF.net email is sponsored by: ValueWeb: 
Dedicated Hosting for just $79/mo with 500 GB of bandwidth! 
No other company gives more support or power for your dedicated server
http://click.atdmt.com/AFF/go/sdnxxaff00300020aff/direct/01/
_______________________________________________
Snort-users mailing list
Snort-users () lists sourceforge net
Go to this URL to change user options or unsubscribe:
https://lists.sourceforge.net/lists/listinfo/snort-users
Snort-users list archive:
http://www.geocrawler.com/redir-sf.php3?list=snort-users