FW: Script to purge snort and acid databases?

["Jacob, Raymond A Jr" <raymond.jacob () navy mil>]

Thank you:

 I was afraid to use DELETE because I thought it would be too slow.
I was wrong I worked lickety split. 4GB of data gone in less than a 
 second. Base works again at least until I start filling the database.

Thank you again,
Raymond
PS: I tried the  archive script but had trouble with Perl modules, the DBI
mysql module as I 
 remember. The script would not login to the database. After modifying the
script so it could
 login, the script seemed to want to move the alerts to the snort_archive
the database.
 I could not figure out how to delete without archiving. I also never
 knew if the script was working. My tables where so big that it took forever
so I just
 killed the script. As a suggestion for large tables you might want to
Delete one minute
 of data a time just so you can maintain a running total and if you have to
interrupt the
 the DELETE at least you know that up to that point X-records have been
deleted. As I recall
 BITIO(before I took it over) the previous administrator had the archive
script working.
 It took about 20-30 days to delete a month's worth of snort_archive
 data on a production system. Deleting the previous day's alerts from the
snort database 
 took about six hours. Causing updates to acid_event to fail until the
Delete finished.
 I apologize in advance if my difficulties were a result of my ignorance. I
do appreciate
 your help.

Thank you,
Raymond



-----Original Message-----
From: Paul Schmehl [mailto:pauls () utdallas edu] 
Sent: Tuesday, September 05, 2006 12:34
To: Jacob, Raymond A Jr; snort-users () lists sourceforge net
Subject: Re: [Snort-users] Script to purge snort and acid databases?

--On Tuesday, September 05, 2006 11:34:56 -0400 "Jacob, Raymond A Jr" 
<raymond.jacob () navy mil> wrote:
> I am running base "1.2.2 (cindy)"  I want to start  fresh without 
> having to recreate the tables.
> Is there a script that will purge the relevant tables?
>
> Doing an ls -last *.MYD
> 4543712 -rw-rw----  1 mysql  mysql  4650467248 Jul 17 17:25 data.MYD
> 1386304 -rw-rw----  1 mysql  mysql  1418840624 Jul 14 16:20 
> acid_event.MYD
>  301792 -rw-rw----  1 mysql  mysql   308848764 Jul 14 16:19 event.MYD
>  459856 -rw-rw----  1 mysql  mysql   470626688 Jul 14 16:19 iphdr.MYD
>   21280 -rw-rw----  1 mysql  mysql    21759711 Jul 14 16:19 udphdr.MYD
>  175088 -rw-rw----  1 mysql  mysql   179167726 Jul 14 01:08 icmphdr.MYD
>   84208 -rw-rw----  1 mysql  mysql    86161620 Jul 13 21:44 tcphdr.MYD
>   14608 -rw-rw----  1 mysql  mysql    14930292 Jul 13 21:43 opt.MYD
>      16 -rw-rw----  1 mysql  mysql       15448 Jul 12 11:20 signature.MYD
>       8 -rw-rw----  1 mysql  mysql        6929 Jul  4 22:57
> sig_reference.MYD
>      12 -rw-rw----  1 mysql  mysql       11088 Jul  4 22:56 reference.MYD
>       2 -rw-rw----  1 mysql  mysql         556 Jun  5 18:59 sig_class.MYD
>       2 -rw-rw----  1 mysql  mysql         160 Apr 21 12:29
> reference_system.MYD
>       6 -rw-rw----  1 mysql  mysql        4836 Apr 18 18:30
> acid_ip_cache.MYD
>       2 -rw-rw----  1 mysql  mysql          84 Mar  2  2006 sensor.MYD
>       0 -rw-rw----  1 mysql  mysql           0 Mar  2  2006 acid_ag.MYD
>       0 -rw-rw----  1 mysql  mysql           0 Mar  2  2006
> acid_ag_alert.MYD
>       0 -rw-rw----  1 mysql  mysql           0 Mar  2  2006
> base_roles.MYD
>       0 -rw-rw----  1 mysql  mysql           0 Mar  2  2006
> base_users.MYD
>       2 -rw-rw----  1 mysql  mysql          40 Mar  2  2006 detail.MYD
>       2 -rw-rw----  1 mysql  mysql          60 Mar  2  2006 encoding.MYD
>       2 -rw-rw----  1 mysql  mysql          13 Mar  2  2006 schema.MYD
>
> So my guess is that I can run
>  echo "TRUNCATE TABLE data;                " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE acid_event ; " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE event;           " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE iphdr;           " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE udphdr;         " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE icmphdr;       " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE tcphdr;         " | mysql -u need_help -p now
>  echo "TRUNCATE TABLE opt;               " | mysql -u need_help -p now
>
> And restart the sensors.
> Is there anything else that I need to do?
First of all, the acid/base tables are "recreations" of what's in the snort
db, so you can drop them at any time, and BASE will recreate the data in
them.

Secondly, I would be careful about using TRUNCATE.  I'd use DELETE FROM
TABLE 'tablename' instead.  TRUNCATE is not transaction-safe.  (However,
TRUNCATE is mapped to DELETE FROM prior to mysql 5.0.3, so there's no
difference between the two in earlier versions.)

Finally, if you just want to keep a certain number of days in the database
(rather than deleting everything) in order to keep its size down to a
workable level, you may want to try my archvie script: 
http://www.ntsug.org/downloads.html

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:

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642

_______________________________________________
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