Re: Purge all Snort events from MySQL database?

["Kristofer T. Karas" <ktk () enterprise bidmc harvard edu>]

On Thu, 2003-09-18 at 14:24, jon baer wrote:
> hmm not sure there is one ... you can always flush the events via command
> line:
> echo "delete from snort.event" | mysql -h 10.10.10.10 -u snort -pmypassword

Oh dear, don't do that!  You have *seven* tables you have to delete
from, and they're all indexed by SID and CID!  Thus you have to look up
entries in "event" and then delete from all seven tables.

OK, I guess I'm just going to have to break down and attach my
"snortdelete" shell script to this mail.  :-)

And while I'm at it, I'll attach the "snortdata" script that makes a
really nice, fast summary of the IP addresses that are triggering some
particular signature.  One of its main features (other than looking up
the user who is on an "infected" machine in your LDAP directory using
NetBIOS name queries) is that it can limit the output to only those
signatures that are outgoing from your network: if you've got 10,000
"cmd.exe access attempt" signatures in your database, this will show you
the 4 machines that are on your network accessing somebody else's
cmd.exe (etc).

Examples:
ktk@linux~$ snortdata
Must specify at least one of either -i, -l or -s

Usage:  snortdata [ -s "scriptlike" ] [ -l | -i ip ] [ -n ] [ -d ] [ -t hours ]

Rummages through the 'snort' MySQL database looking for signatures that:
        -s "x"          Have a signature like '%x%'
        -l              Have a source IP on our network.
        -i "ip"         Have the given source IP; exclusive of -l, above.
At least one of the above must be specified.

Options:
        -b              Debug SQL - Prints executed SQL to stderr
        -c              Sort by count instead of by IP
        -d              Use destination IP; the default is source IP.
        -g              Sort by signature name instead of by IP
        -n              Perform a NetBIOS name lookup on returned IPs.
        -t hours        Only consider signatures received within <hours>.

ktk@linux~$ snortdata -s cyberkit -n
   10.35.24.199    x84: "ICMP PING CyberKit 2.2 Windows"

  10.75.133.253  x2405: "ICMP PING CyberKit 2.2 Windows"
         NBName: DA22A40CF4
         WGroup: WORKGROUP
          UName: ADMINISTRATOR

   10.75.159.20   x307: "ICMP PING CyberKit 2.2 Windows"
         NBName: JDBUI
         WGroup: ITS
          UName: JSMITH
          RName: Smith, John A.

ktk@linux~$ snortdata -i 10.75.159.20
   10.75.159.20    x45: "ICMP PING CyberKit 2.2 Windows"
                    x8: "NETBIOS DCERPC ISystemActivator bind attempt"
                   x67: "WEB-IIS WEBDAV nessus safe scan attempt"

ktk@linux~$ snortdelete
Usage:  ./snortdelete  [ -<options> ]  hours

Deletes old data in the snort database, keeping entries received within
the past <hours>.  You can limit the data deleted by signature or ip,
using the specified options.

Options:
        -b              Debug SQL - Prints executed SQL to stderr
        -d              Use destination IP with -r or -i; default is source.
        -i "ip"         Have the given source IP exclusive of -r.
        -n              Don't actually do anything; just look up data.
        -o              Optimize the tables after deleting.
        -r              Remote source IPs only (incoming, not outgoing).
        -s "x"          Signature must be like '%x%'
ktk@linux~$ snortdelete -os cyberkit 1
ktk@linux~$

If the example usage above strikes your fancy and you are comfortable
editing a shell script, then feel free to DL the attachments and try
them out on your system.

Obvious caveats:
These scripts are cheap, quick and dirty hacks that "work for me".  They
assume a GNU/Linux system (I'm running Slackware) that also has Samba
and OpenLDAP installed.  It is hard-wired for MySQL but with some
hackery you could get it to use the database of your choice.  The
scripts take advantage of bash-isms; you'll probably have to spend some
time if you want to convert it so some other shell interpreter.

Sorry, I'm a bit swamped to field "how do I make this work" questions. 
If you get it working, great.  If not, it was worth what you paid for
it.  Have fun!

Kris

Attachment: snortdata
Description:

Attachment: snortdelete
Description: