Softpanorama (slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-)

Cleanout of ACID Database

If acid became irresponsive due to huge amount of junk alerts collected (over a million) you will be better off recreating your snort database anew. To drop snort database, perform the following:

# mysql -u root -p
mysql> show databases;
+-------------+
| Database    |
+-------------+
| mysql       |
| snort       |
| snortcenter |
| test        |
+-------------+
4 rows in set (0.00 sec)
mysql> drop database snort;
mysql> exit

After that you can recreate it using instruction provided for installation. 

Recommended Links

'[Snort-users] Script to cleanup ACID-Snort Alerts in MySQL DB...' - MARC

[prev in list] [next in list] [prev in thread] [next in thread]

List:       snort-users
Subject:    [Snort-users] Script to cleanup ACID/Snort Alerts in MySQL DB...
From:       "Dusty Hall" <halljer () auburn ! edu>
Date:       2003-04-03 23:34:23
[Download message RAW]

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

cleanout-snort

The other way of cleaning it is to use cleanout-snort from Placid by Phil Deneault (dznzault@hiddzngroup.nzt, z's for e's)

cleanout-snort is a perl script for cleaning acid database:

#!/usr/bin/perl -w

use DBI;
use Getopt::Long;
use strict;

# you can comment this out if you don't have Time::HiRes, but you won't get very accurate timing values

use Time::HiRes qw( time );

my $usage = "Usage: \n$0 -i|-q [-d] [-s] [-t] [-o] [-c] [-k] [-f] [-p]\n" .
"\t-i <cid to delete>\n" .
"\t-q <sid to delete>\n" .
"\t-d <delete older than this number of days>\n" .
"\t-s <source ip>\n\t-t <target ip>\n" .
"\t-o <mysql optimize threshold, 0 to disable>\n" .
"\t-c <per pass count, max 50k>\n" .
"\t-k clean out unused sensor records\n" .
"\t-f clean out sensorless records and dangling sub table records (slow)\n" .
"\t-p clean out unused signature records\n";

Last updated: June 05, 2008