Snort DB clean up ACID/BASE

[Amm Snort <ammdispose-snort () yahoo com>]

Hello all,

Just wanted to share clean up schema which should be added in
basic schema. I know snort no longer uses DB but I am still sharing
here as many people (like me) still come here looking for solution.

Idea is that deletion from other tables should be automatic once
the sid/cid is deleted from "event" table.

This way you dont have to write big cleanup script. And also you
can simply run SQL command manually:
For eg:
delete from event where timestamp<SOMEDATE;

Rest will be taken care by following rule.

This is for PostgreSQL, may be similar rule can be written for
other DBs.

-- this would go in snort schema file create_postgresql
create rule event_deleted as on delete to event do (
    delete from iphdr where sid=old.sid and cid=old.cid;
    delete from tcphdr where sid=old.sid and cid=old.cid;
    delete from udphdr where sid=old.sid and cid=old.cid;
    delete from icmphdr where sid=old.sid and cid=old.cid;
    delete from opt where sid=old.sid and cid=old.cid;
    delete from data where sid=old.sid and cid=old.cid;
    );

-- this would go in base schema file create_base_tbls_pgsql.sql
create rule acid_event_deleted as on delete to event do (
    delete from acid_event where sid=old.sid and cid=old.cid;
    delete from acid_ag_alert where ag_sid=old.sid and ag_cid=old.cid;
    );

Hope it helps someone.


AMM.


------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and 
threat landscape has changed and how IT managers can respond. Discussions 
will include endpoint security, mobile security and the latest in malware 
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
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

Please visit http://blog.snort.org to stay current on all the latest Snort news!