#!/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]};
}

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


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;
  }
}