Re: mysql - acid - dshield

[Mark Rowlands <mark.rowlands () minmail net>]

On Mon August 12 2002 00:50, Toby Nelson wrote:
> Does any one know of a script for exporting snort data within mysql to
> dshield?

Well this ain't pretty and it aint tested either and it presumes you are 
running a "unix" based system and requires mail::Sendmail, dbi and 
socket...but ya probably get the idea

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

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

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


print "Start ACid Extraction\n";
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:localhost", 'snort', 'XXXXXX') || 
&error;
  $sth = $dbh->prepare($query);
  $sth->execute();
  
  while (($time,$count,$source,$des,$prot,$sport,$dport) = 
$sth->fetchrow_array()) {


  if ($prot=~m/ICMP/) {
    $prot="ICMP";
    $sport="0";
    $dport="0";
  }

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



sub convmonth()
{
  my %months_tab = (Jan=>"01",
                    Feb=>"02", 
                    Mar=>"03", 
                    Apr=>"04", 
                    May=>"05", 
                    Jun=>"06", 
                    Jul=>"07", 
                    Aug=>"08", 
                    Sep=>"09", 
                    Oct=>"10", 
                    Nov=>"11", 
                    Dec=>"12");
  
  return $months_tab{$_[0]};
}

turn a raw ip into a dotty decimal
sub getip {
  $ip=inet_ntoa(pack("N", $_[0]));
  return $ip;
}

# get a name given an dotty decimal 
sub name2 {
  @numbers = split(/\./, $_[0]);
  $ip_number = pack("C4", @numbers);
  ($name) = (gethostbyaddr($ip_number, 2))[0];
  if ($name) {
    return $name;
    } else {
    $name =$source;
    return $name;
  }
}

Attachment: dshield.pl
Description: