Snort mailing list archives
FW: Script to purge snort and acid databases?
From: "Jacob, Raymond A Jr" <raymond.jacob () navy mil>
Date: Tue, 5 Sep 2006 14:44:09 -0400
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
Current thread:
- Script to purge snort and acid databases? Jacob, Raymond A Jr (Sep 05)
- Re: Script to purge snort and acid databases? Paul Schmehl (Sep 05)
- <Possible follow-ups>
- FW: Script to purge snort and acid databases? Jacob, Raymond A Jr (Sep 05)
- Re: FW: Script to purge snort and acid databases? Paul Schmehl (Sep 05)