Looking for Perl archive script for Snort with ACID and MySQL

[JP Vossen <vossenjp () netaxs com>]

Given 2 databases, Snort and Snort_Archive, where Snort has a rolling time
frame (say 30 days) and Snort_Archive is for historical reporting, I am
looking for a Perl script to fulfill the following requirements (more or
less).  The high level goal is to have a reasonable period of time in the
active database (so ACID response time is bearable), but to archive all events
in a reporting DB for later research, data mining, whatever.  This is intended
for a honeypot environment, but there's no reason it wouldn't be useful for
production.

* Written in Perl, presumably using DBI and reasonably efficient and fast.
* Run periodically from cron (e.g. weekly).
* Choose a date at some arbitrary point in the past (e.g. 30 days).
* Query the entire Snort database for all records less than that date.  Also
count the number of records returned.
* Count the number of records in the Snort_Archive database.
* Copy the selected (old) events from Snort to Snort_Archive.  If any
identical records already exist, safely error out.  This is a little tricky
since the snort db (except for ACID) seems normalized and thus all over the
place.
* Count the number of events now in the Snort_Archive database and make sure
the new number of records matches the old number plus the number moved.  If
not, safely error out.  Note that this is a clumsy attempt to verify the copy
worked before deleting the old records.  If anyone can suggest a better way,
that'd be great.
* Delete selected (old) events from Snort.
* Optimize tables in both databases as required.
* Optionally, kick off a reporting script when finished.
* Have the ability to purge really old records out of Snort_Archive if/when
needed.  Obviously, one of the stand along scripts below can do this, but why
have and maintain 2 sets of code when one is a subset of the other?

I've spent a fair amount of time in the Snort-users archive and on Google and
found the following, none of which does exactly what I want (though Susan's
scripts are very close).  I can piece something together from these and my own
meager Perl skills, but before I do I wanted to see a) if anyone else already
and b) if anyone else is interested.  I can't believe I'm the only person who
wants to do it this way. :-)

http://marc.theaimsgroup.com/?l=snort-users&m=104941480228554&w=2
Dusty Hall's alert_cleanup.pl from 2003-04-03

http://archives.neohapsis.com/archives/snort/2003-08/0414.html
Masood Ahmad Shah's (sort of?) acidmysqlclean.pl from 2003-08-13, but this
identical to Dusty's above, except for trivial white space and line break
changes and username and password?!?  This seems rather shady to me...

http://marc.theaimsgroup.com/?l=snort-users&m=100274983602247&w=2
Susan Coulter's archive.pl, load.pl and clear.pl from 2001-10-10

http://marc.theaimsgroup.com/?l=snort-users&m=99972976601252&w=2
David Gadbois' snort-cleanup frp, 2001-09-05

http://marc.theaimsgroup.com/?l=snort-users&m=97933672911798&w=2
Chris Green's snort_cleandb.pl from 2001-01-12


TIA,
JP
------------------------------|:::======|--------------------------------
JP Vossen, CISSP              |:::======|         jp{at}jpsdomain{dot}org
My Account, My Opinions       |=========|       http://www.jpsdomain.org/
------------------------------|=========|--------------------------------
You used to have to reboot the Windows 9.x series every couple of days
because it would crash.  Now you have to reboot Windows 200x or XP every
couple of days because of a patch.  How is that better or more stable?



-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click
_______________________________________________
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