BASE-DeleteEvents.php
From ChekMate Security Group
Contents |
[edit]
Introduction
[edit]
What is BASE-DeleteEvents.php
A large number of alerts can be collected within the Snort/Basic Analysis and Security Engine (BASE) implementation. The performance starts to become a factor when the number of alerts keeps growing. This script removes old events from the Snort database so that old histroy does not clog your system.
[edit]
Maintainer
Shannon McNaught (smcnaught)
[edit]
Download
The following script removes old events from the SNORT/BASE MySql database properly.
[edit]
base_delete_events.php
Modify the following variables:
$olderthan : # of days to keep $db_server : database IP address $db_user : database user $db_pwd : database password $db_name : database name
<?php
#################################################################################
#
# BASE-DeleteEvents.php - Removes events older then ## days my Snort Database.
# Copyright (C) 2005 Shannon McNaught
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
#
#################################################################################
## ***** Change These Values **** ##
$olderthan = 30;
$db_server = 'localhost';
$db_user = 'user';
$db_pwd = 'password';
$db_name = 'database';
## ***** Do Not Change Anything Below **** ##
$db_link = @mysql_pconnect($db_server, $db_user, $db_pwd) or exit('Could not connect: ' . mysql_error());
$db = @mysql_select_db($db_name, $db_link) or exit('Could not select database: ' . mysql_error());
$select =mysql_query("SELECT sid, cid FROM event WHERE (TO_DAYS(NOW()) - TO_DAYS(timestamp)) >= $olderthan") or die(mysql_error());
$num_to_delete = mysql_num_rows($select);
$select =mysql_query("SELECT sid, cid FROM event WHERE (TO_DAYS(NOW()) - TO_DAYS(timestamp)) >= $olderthan") or die(mysql_error());
while($row=mysql_fetch_object($select)) {
echo "Deleting sid: ". $row->sid . " cid: " . $row->cid . "\n";
$delete=mysql_query("DELETE FROM data WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM iphdr WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM tcphdr WHERE cid='$row->cid'and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM udphdr WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM icmphdr WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM event WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM acid_event WHERE cid='$row->cid' and sid='$row->sid'") or die(mysql_error());
$delete=mysql_query("DELETE FROM acid_ag_alert WHERE ag_cid='$row->cid' and ag_sid='$row->sid'") or die(mysql_error());
}
echo "Deleted: $num_to_delete from the snort database.\n";
?>
[edit]
Installation
- Copy the above script to your YOUR-BASE-DIR/scripts directory
- cd YOUR-BASE-DIR/scripts
- chmod 700 BASE-DeleteEvents.php
- chown snort.snort BASE-DeleteEvents.php
- Modify /etc/crontab:
- Add:
- 02 4 * * * snort php YOUR-BASE-DIR/scripts/base_delete_events.php
- Add:
You can test the script from the command-line to ensure that it is working properly but otherwise you can just let crontab clean up the database every night.
[edit]
Change Log
Nothing to document.
[edit]
References
- Snort IDS : http://www.snort.org/
- Basic Analysis and Security Engine (BASE) : http://secureideas.sourceforge.net/
[edit]
SNORT Tables
Description of relevant tables within the snort database:
event; +-----------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------------------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | signature | int(10) unsigned | | MUL | 0 | | | timestamp | datetime | | MUL | 0000-00-00 00:00:00 | | +-----------+------------------+------+-----+---------------------+-------+ tcphdr; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | tcp_sport | smallint(5) unsigned | | MUL | 0 | | | tcp_dport | smallint(5) unsigned | | MUL | 0 | | | tcp_seq | int(10) unsigned | YES | | NULL | | | tcp_ack | int(10) unsigned | YES | | NULL | | | tcp_off | tinyint(3) unsigned | YES | | NULL | | | tcp_res | tinyint(3) unsigned | YES | | NULL | | | tcp_flags | tinyint(3) unsigned | | MUL | 0 | | | tcp_win | smallint(5) unsigned | YES | | NULL | | | tcp_csum | smallint(5) unsigned | YES | | NULL | | | tcp_urp | smallint(5) unsigned | YES | | NULL | | +-----------+----------------------+------+-----+---------+-------+ udphdr; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | udp_sport | smallint(5) unsigned | | MUL | 0 | | | udp_dport | smallint(5) unsigned | | MUL | 0 | | | udp_len | smallint(5) unsigned | YES | | NULL | | | udp_csum | smallint(5) unsigned | YES | | NULL | | +-----------+----------------------+------+-----+---------+-------+ icmphdr; +-----------+----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | icmp_type | tinyint(3) unsigned | | MUL | 0 | | | icmp_code | tinyint(3) unsigned | | | 0 | | | icmp_csum | smallint(5) unsigned | YES | | NULL | | | icmp_id | smallint(5) unsigned | YES | | NULL | | | icmp_seq | smallint(5) unsigned | YES | | NULL | | +-----------+----------------------+------+-----+---------+-------+ opt; +-----------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | optid | int(10) unsigned | | PRI | 0 | | | opt_proto | tinyint(3) unsigned | | | 0 | | | opt_code | tinyint(3) unsigned | | | 0 | | | opt_len | smallint(6) | YES | | NULL | | | opt_data | text | YES | | NULL | | +-----------+---------------------+------+-----+---------+-------+ data; +--------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | data_payload | text | YES | | NULL | | +--------------+------------------+------+-----+---------+-------+ acid_ag_alert; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | ag_id | int(10) unsigned | | PRI | 0 | | | ag_sid | int(10) unsigned | | PRI | 0 | | | ag_cid | int(10) unsigned | | PRI | 0 | | +--------+------------------+------+-----+---------+-------+ acid_event; +--------------+------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------------------+-------+ | sid | int(10) unsigned | | PRI | 0 | | | cid | int(10) unsigned | | PRI | 0 | | | signature | int(10) unsigned | | MUL | 0 | | | sig_name | varchar(255) | YES | MUL | NULL | | | sig_class_id | int(10) unsigned | YES | MUL | NULL | | | sig_priority | int(10) unsigned | YES | MUL | NULL | | | timestamp | datetime | | MUL | 0000-00-00 00:00:00 | | | ip_src | int(10) unsigned | YES | MUL | NULL | | | ip_dst | int(10) unsigned | YES | MUL | NULL | | | ip_proto | int(11) | YES | MUL | NULL | | | layer4_sport | int(10) unsigned | YES | MUL | NULL | | | layer4_dport | int(10) unsigned | YES | MUL | NULL | | +--------------+------------------+------+-----+---------------------+-------+
[edit]
License
BASE-DeleteEvents.php - Removes events older then ## days my Snort Database. Copyright (C) 2005 Shannon McNaught This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
Sourcefire, the Sourcefire logo, Snort, the Snort logo, Intrusion Sensor, Intrusion Agent, Real-time Network Awareness, RNA Sensor, Defense Center, Success Pack, and 3D System, are trademarks or registered trademarks of Sourcefire, Inc.
