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/
smime.p7s
Description: S/MIME Cryptographic Signature
