[Dshield] MySql reporter

Mark Rowlands mark.rowlands at minmail.net
Fri Aug 24 15:48:04 GMT 2001


On Friday 24 August 2001 8:52 am, Thomas Nilsen wrote:
> Does anyone know of any script that will extract log files from a MySQL
> database (ACID) that again can be posted to Dshield???
>
> We do not want to have to maintain both a file log on the snort host as
> well as the ACID database.
>

acid is pretty recent and snort is 1,81

#!/usr/bin/perl
use DBI;
use Mail::Sendmail;
use Socket;
#  Parameters:

my %mail = (
  To      => 'reports at dshield.org',
  Subject => 'FORMAT DSHIELD USERID 45486598  TZ +01:00',
);

my $lastrun=`cat /rules/timestamp`;
chop $lastrun;
my $timestamp=`date '+%Y-%m-%d %H:%M:%S'`;
open (FH,">/rules/timestamp");
print FH $timestamp;
close FH;
chop $timestamp;

my $query=qq{select timestamp,COUNT(*) AS cnt, ip_src,  ip_dst, "UDP"  AS 
protocol, udp_sport AS sport, udp_dport AS dport from event, iphdr, udphdr 
where timestamp<'$timestamp'  and timestamp>='$lastrun' and 
iphdr.sid=event.sid and iphdr.cid=event.cid and udphdr.sid=event.sid and 
udphdr.cid=event.cid GROUP BY   ip_src, ip_dst,  sport, dport};
&go($query);
   $query=qq{select timestamp,COUNT(*) AS cnt,ip_src,  ip_dst, "TCP"  AS 
protocol, tcp_sport AS sport, tcp_dport AS dport from event, iphdr, tcphdr 
where timestamp<'$timestamp'  and timestamp>='$lastrun' and 
iphdr.sid=event.sid and iphdr.cid=event.cid and tcphdr.sid=event.sid and 
tcphdr.cid=event.cid GROUP BY   ip_src,  ip_dst, sport, dport};
&go($query);
   $query=qq{select timestamp,COUNT(*) AS cnt, ip_src,  ip_dst, "ICMP" AS 
protocol   from event, iphdr,icmphdr where timestamp<'$timestamp'  and 
timestamp>='$lastrun' and event.sid = iphdr.sid and  iphdr.cid = event.cid 
and  event.sid =  icmphdr.sid and  event.cid = icmphdr.cid GROUP BY  ip_src, 
ip_dst};
&go($query);


sub go {
$query = shift;
$dbh = DBI->connect("DBI:mysql:snort:192.168.0.2", 'snort', 'XXXXXX') || 
&error;
$sth = $dbh->prepare($query);
$sth->execute();
while (($time,$count,$src,$des,$prot,$sport,$dport) = $sth->fetchrow_array()) 
{
$src=&getip($src);
$des=&getip($des);
print "$time +01:00\t45486598\t$count\t$src\t$sport\t$des\t$dport\t$prot\n";

$mail{'Message : '}="$time 
+01:00\t45486598\t$count\t$src\t$sport\t$des\t$dport\t$prot\n";
sendmail(%mail) || print "Error sending mail: $Mail::Sendmail::error\n";

}
}

sub getip {
$ip=inet_ntoa(pack("N", $_[0]));
return $ip;
}




More information about the list mailing list