Php script for deleting alerts

[Paul Schmehl <pauls () utdallas edu>]

I have written a php script for deleting alerts from a mysql db when 
you're using base to view snort.  (The script uses schema 106 for mysql. 
 It hasn't been tested with any other schema.)  It's a fairly simple 
script, with a handful of options, and it can (and should) use a conf 
file, at least for the db userid and password.  This is a use at your 
own risk, beta script, so if you're not into testing and trying things 
out, you don't want to get a copy.

If you are interested in testing this script, let me know, and I'll send 
you a copy.  (There's actually three files; the script, a conf file and 
a sql script for creating a table in the db.)  If there's enough 
interest, I'll include it as a tarball download from our ntsug website, 
just as I do my archive script.

The script does one thing - deletes all alerts for a single IP (both 
source and destination events), regardless of what those alerts are.  If 
there's an interest, I'd be willing to work on further functionality. 
At the present time it does not delete discrete types of alerts.  Nor 
will it delete alerts associated with more than one IP address.  IOW, 
you can't delete alerts for a range of IPs (CIDR or otherwise).  It 
should also be used with caution, since you're exposing a userid and 
password to your database (so set your perms tightly and control access, 
yada, yada, yada.)

I wrote this script because I got tired of deleting large numbers of 
portscanning events from base, 10,000 or so at a time.  This script has 
successfully deleted 500,000 events (associated with one IP) in a short 
amount of time.  Run times are about six times longer on mysql 3.x than 
they are on mysql 4.x.  I haven't tested mysql 5.x.

Here's some of the times I've been getting.  (FreeBSD 6.0 dual AMD 
processors, 2GB ram, mysql 4.1.19.)  YMMV depending on hardware and 
version of mysql.

php delete_alerts.php -c delete_alerts.conf -i 72.32.58.187
The 82269 alerts associated with 72.32.58.187 were deleted from 7 tables 
in 9 seconds

php delete_alerts.php -c delete_alerts.conf -i 68.142.213.132
The 16675 alerts associated with 68.142.213.132 were deleted from 7 
tables in 2 seconds

php delete_alerts.php -c delete_alerts.conf -i 140.129.37.154
The 1811 alerts associated with 140.129.37.154 were deleted from 7 
tables in 1 seconds

php delete_alerts.php -c delete_alerts.conf -i 68.94.75.19
The 1685 alerts associated with 68.94.75.19 were deleted from 7 tables 
in 2 seconds

--
Paul Schmehl (pauls () utdallas edu)
Adjunct Information Security Officer
The University of Texas at Dallas
http://www.utdallas.edu/ir/security/

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature