Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Cleanup ALerts in Snort/ACID Mysql DB

by draper7 (Scribe)
on Apr 03, 2003 at 23:01 UTC ( #247926=snippet: print w/replies, xml ) Need Help??
Description:   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: 
# description: script to cleanup snort/acid db (only tested w/mysql)
# goal: allows you to schedule db cleanup without using php frontend
# usage: "2003-04-01 8:00:00" "2003-04-01 9:00:00"
# comments: dusty hall, halljer@<NOSPAM>

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,$
my ($exec_event,$exec_iphdr,$exec_tcphdr,$exec_udphdr,$exec_icmphdr,$e
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
$exec_time_select = $db->prepare($time_select);


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
 $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);




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.

      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

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
    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://247926]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (3)
As of 2019-08-19 23:30 GMT
Find Nodes?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?

    Results (141 votes). Check out past polls.