#!/bin/bash
#
# Script to delete old data from the snort sql database.

# NOTE!  Before you can use this script, you must change the defines
# in the following lines to match those at your company.
#
# A few constants needed.  User with R/W privileges to snort database.
MYUSER="snort"
MYPASS="secret"
SNORTDB="snort"
# Now define the public IP address ranges used by your company.
# If you have more than one discontiguous range, you'll need to edit
# the SQL generation code lower down in this script.  It's not hard to do.
IPLOW="1.2.3.0"
IPHIGH="1.2.3.254"

function usage() {
  cat <<EOF >&2
Usage:	$0  [ -<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%'
EOF
}

if TEMP=`getopt -o bdi:nors: -n "$0" -- "$@"`; [ $? -ne 0 ]; then
  usage; exit 1
fi

eval set -- "$TEMP"

LIKE=""; REMOTES=""; IP=""; SRCDST="ip_src"; NOEXEC=""; DBG=""; OPTIM=""
while true ; do
  if [ "$1" = "-b" ]; then DBG=1;		shift
  elif [ "$1" = "-d" ];	then SRCDST="ip_dst";	shift
  elif [ "$1" = "-i" ]; then IP="$2";		shift 2
  elif [ "$1" = "-n" ]; then NOEXEC=1;		shift
  elif [ "$1" = "-o" ]; then OPTIM=1;		shift
  elif [ "$1" = "-r" ]; then REMOTES=1;		shift
  elif [ "$1" = "-s" ]; then LIKE="$2";		shift 2
  elif [ "$1" = "--" ]; then			shift; break
  else echo "Internal getopt error?" >&2;	exit 2
  fi
done
if [ $# -ne 1 ]; then
  usage; exit 1
elif [ -n "$IP" -a -n "$REMOTES" ]; then
  echo -e "\n\nCannot specify both -i and -r.\n" >&2
  usage; exit 1
elif HOURS="$1"; ! echo "$HOURS" | grep -q '^[0-9]\+$'; then
  echo -e "\n\nThe <Hours> argument must be a non-negative integer.\n" >&2
  usage; exit 1
elif [ -z "$IP" -a -z "$REMOTES" -a -z "$LIKE" -a $(($HOURS+0)) = 0 ]; then
  echo -e "\n\nMust specify at least one of either -i, -r or -s" >&2
  echo -e "when the <hours> argument is zero (else delete entire DB!).\n" >&2
  usage; exit 1
fi

function makequery () {
  local wa="WHERE"
  echo -n "SELECT event.sid, event.cid FROM "
  if [ -n "$IP$REMOTES" ]; then echo -n "iphdr, "; fi
  if [ -n "$LIKE" ]
    then echo -n "signature, event"
    else echo -n "event"
  fi
  if [ $HOURS -gt 0 ]; then
    echo -en "\n       $wa event.timestamp < NOW() - INTERVAL '$HOURS' HOUR"
    wa="AND"
  fi
  if [ -n "$LIKE" ]; then
    if ! echo "$LIKE" | grep -q '%'; then
      LIKE="%${LIKE}%"
    fi
    echo -e "\n       $wa signature.sig_name LIKE '$LIKE'"
    echo -n "       AND signature.sig_id = event.signature"; wa="AND"
  fi
  if [ -n "$IP" ]; then
    echo -e "\n       $wa iphdr.$SRCDST = INET_ATON('$IP')"
  elif [ -n "$REMOTES" ]; then
    cat <<EOF

       $wa iphdr.$SRCDST NOT BETWEEN INET_ATON('$IPLOW')
				AND INET_ATON('$IPHIGH')
       AND iphdr.$SRCDST NOT BETWEEN INET_ATON('10.0.0.0')
				AND INET_ATON('10.255.255.255')
       AND iphdr.$SRCDST NOT BETWEEN INET_ATON('192.168.0.0')
				AND INET_ATON('192.168.255.255')
EOF
  fi
  if [ -n "$IP$REMOTES" ]
    then echo "       AND iphdr.sid = event.sid AND iphdr.cid = event.cid;"
    else echo ";"
  fi
}

# This takes the output of makequery, pipes it through mysql to get the
# list of rows to delete, generates the delete statements for each table,
# then optionally adds optimize commands.
function makesql () {
  local rhs table
  rhs='s%^\([0-9]\+\)[[:space:]]\+\([0-9]\+\)$%\
'
  for table in data event icmphdr tcphdr udphdr iphdr opt; do
    rhs="${rhs}DELETE FROM $table WHERE sid='\1' AND cid='\2';\\
"
  done
  rhs="$rhs%"
  makequery | mysql --user="$MYUSER" --password="$MYPASS" -s -B "$SNORTDB" |\
  sed -e "$rhs"
  if [ -n "$OPTIM" ]; then
    # Order tables by approximate size.
    for table in icmphdr udphdr opt event tcphdr iphdr data; do
      echo "OPTIMIZE TABLE $table;"
    done
  fi
}

#########################################################################
#									#
#		 Run the query and output the results...		#
#									#
#########################################################################

if [ -n "$DBG" ]; then
  echo -e "\nSQL Query:\n" >&2; makequery >&2; echo >&2
fi

if [ -n "$NOEXEC" ]
  then makesql
  else makesql | mysql --user="$MYUSER" --password="$MYPASS" "$SNORTDB"
fi