[Snort-users] MySQL Log rotate

Jyri Hovila jyri.hovila at ...2940...
Mon Sep 10 08:58:02 EDT 2001


Hi!

> Ah.  The acid_event table got added in 0.9.6b13, and I have been
running
> 0.9.6b12.  It appears all you will need to do is add a couple of
lines:
>
>    $dbh->prepare("DELETE FROM acid_event WHERE sid = ? AND cid = ?"),
>
> and
>
>    $dbh->do("OPTIMIZE TABLE acid_event");
>
> I haven't tested this, so use at your own risk.

Works perfectly! Thank you! =)

Here's the updated script -- in case someone finds this message from an
archive some day in the distant future and won't be able to find the
original script... ;)

Cheers!

- Jyri

------------------------------begin
snortate.pl------------------------------
#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:mysql:database=snort:host=localhost",
"acid", "BMc,39LLwfdhYkmk")
    or die "Can't connect: $DBI::errstr\n";

my @deletes = (
               $dbh->prepare("DELETE FROM data    WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM icmphdr WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM udphdr  WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM tcphdr  WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM iphdr   WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM opt     WHERE sid = ? AND cid
= ?"),
               $dbh->prepare("DELETE FROM acid_ag_alert WHERE ag_sid = ?
AND ag_cid = ?"),
               $dbh->prepare("DELETE FROM acid_event WHERE sid = ? AND
cid = ?"),
               $dbh->prepare("DELETE FROM event   WHERE sid = ? AND cid
= ?"));

my $sth = $dbh->prepare("SELECT sid,cid FROM event WHERE timestamp < (
NOW() - INTERVAL 0 DAY ) ");
my ($sid, $cid);
$sth->execute();
$sth->bind_columns(undef, \$sid, \$cid);
my $count = 0;
while (my $ref = $sth->fetch) {
    $count++;
    foreach my $delete (@deletes) {
        $delete->execute($sid, $cid);
    }
}

if ($count) {
    $dbh->do("OPTIMIZE TABLE data");
    $dbh->do("OPTIMIZE TABLE icmphdr");
    $dbh->do("OPTIMIZE TABLE udphdr");
    $dbh->do("OPTIMIZE TABLE tcphdr");
    $dbh->do("OPTIMIZE TABLE iphdr");
    $dbh->do("OPTIMIZE TABLE opt");
    $dbh->do("OPTIMIZE TABLE acid_ag_alert");
    $dbh->do("OPTIMIZE TABLE acid_event");
    $dbh->do("OPTIMIZE TABLE event");
}

$dbh->disconnect or warn "Disconnect failed: $DBI::errstr\n";
------------------------------end
snortate.pl------------------------------





More information about the Snort-users mailing list