#!/usr/bin/perl

use FileHandle;
use strict;
use Data::Dumper;
use DBI;
my $dbh = DBI->connect("DBI:mysql:database=postfix;host=xxxxx",
						"xxxx",
						"xxxx") or die;
my $debug = 1;

use vars qw($line);
###################################################################
#  Main Script
#	First, lets collect the data, then we'll process it
# 
open LOGFILE, ">>/var/log/dgmtest";

autoflush LOGFILE 1;

while (1) {
    $_ = <>;
    $line = $_;
	if (/postfix\S+ reject: RCPT from (\S+) (530|554|450) (\S+): (.*) from=<(.*?)> to=<(.*?)>/) {
		my ($from, $to, $message, $o, $t, $th) = (lc($5),lc($6),$4, $1, $2, $3);
		#print "Blocked message from: $from to: $to because: $message\n";
		$message = ($message =~ /www.ordb.org/ ? "ORDB" : $message);
		$message = ($message =~ /www.spamcop.net/ ? "SPAMCOP" : $message);
		$message = ($message =~ /dun.dnsrbl.net/ ? "DUNDNSRBL" : $message);
		$message = ($message =~ /spam.dnsrbl.net/ ? "SPAMDNSRBL" : $message);
		$message = ($message =~ /Cannot find your hostname/ ? "RDNS" : $message);
		$message = ($message =~ /relays.osirusoft.com/ ? "OSFT" : $message);
		$message = ($message =~ /Relay access denied/ ? "RELAYDENIED" : $message);
		$message = ($message =~ /Recipient address rejected: Access denied/ ? "BLACKLIST" : $message);
		$message = ($message =~ /china.blackholes.us/ ? "CHINA" : $message);
		$message = ($message =~ /cn-kr.blackholes.us/ ? "KOREA" : $message);
		$message = ($message =~ /argentina.blackholes.us/ ? "ARGENTINA" : $message);
		$message = ($message =~ /brazil.blackholes.us/ ? "BRAZIL" : $message);
		$message = ($message =~ /blackholes.easynet.nl/ ? "EASYNET" : $message);
		$message = ($message =~ /opm.blitzed.org/ ? "BLITZED" : $message);
		$message = ($message =~ /trustic.com/ ? "TRUSTIC" : $message);
		
		# some ID-10T is trying to spam through us as if we were an open relay.  Let's not count them. 
		if ($message =~ /RELAYDENIED/ and $from =~ /(blvelasq|douglasl|meinsen|ecr)/) {
			print LOGFILE "dropping relay from $o, $from to $to\n";	
			next;
		}
		
		my $result = Check($to, $from);		
		$result ? Update($result, 1, $message) : 
			  Insert($to,$from,$message,1);
		
		UpdateStats($to, $message);
		
		print LOGFILE $_;
		
	} elsif (/bouncer postfix\S+ reject: /) {
		print LOGFILE $_;	
	}
}

END {
    print "dying";
    print LOGFILE scalar localtime() ." screport.pl ending. Last Line: $line\n";
    close LOGFILE;


}



sub Check($$) {
	my ($rcpt, $from) = @_;
	my $id;
	my $sth = $dbh->prepare("SELECT id from per_user_errors WHERE rcpt=? AND sender=?") or die $dbh->errstr;
	#$rcpt = $dbh->quote($rcpt);
	#$from = $dbh->quote($from);
	
	$sth->execute($rcpt, $from);
	
	
	($id) = $sth->fetchrow_array();
	$sth->finish();
	
	
	return $id;
}

sub Insert ($$$$) {
	my ($rcpt, $from, $why, $count) = @_;
	
	if ($debug) {
		print "INSERT INTO per_user_errors VALUES ('','$rcpt','$from','$why','$count',CURRENT_DATE)\n";
		return;
	}
	
	my $sth = $dbh->prepare("INSERT INTO per_user_errors VALUES ('','$rcpt','$from','$why','$count',CURRENT_DATE)");
	$sth->execute() or die $dbh->errstr;
	$sth->finish();
}

sub Update ($$$) {
	my ($id, $count, $why) = @_;

	if ($debug) {
		print "UPDATE per_user_errors SET tries=tries+$count, tstamp=CURRENT_DATE, method='$why' WHERE id=$id\n";
		return;
	}
	my $sth = $dbh->prepare("UPDATE per_user_errors SET tries=tries+$count, tstamp=CURRENT_DATE WHERE id=$id");
	#print ("UPDATE per_user_errors SET tries=tries+$count, tstamp=CURRENT_DATE WHERE id=$id");
	$sth->execute() or die $dbh->errstr;
	$sth->finish();
}


sub UpdateStats($$) {
	my ($address, $type) = @_;


	return if $type !~ /RDNS|SPAMCOP|OSFT|ORDB|BLACKLIST|DUNSDNSRBL|SPAMDNSRBL/;

	#Check for entry
	my $sth = $dbh->prepare("SELECT id FROM control_stats WHERE address=? AND type=?");
	my $numrows = $sth->execute($address, $type);
	$sth->finish();
	#insert or update
	
	if ($numrows == 0) { #INSERT
		$sth = $dbh->prepare("INSERT INTO control_stats VALUES ('',?,?,1)");
	} else { #UPDATE
		$sth = $dbh->prepare("UPDATE control_stats set count=count+1 WHERE address=? AND type=?");
	}
	$sth->execute($address,$type);
	$sth->finish();
	
	#update global stats
	$sth = $dbh->prepare("UPDATE control_stats set count=count+1 WHERE address='system' AND type=? OR type='total'");
	$sth->execute($type);
	$sth->finish();
}

#sample error message
#Jul  3 11:19:00 bouncer postfix/smtpd[14071]: reject: RCPT from unknown[207.250.144.22]: 530 <rrrrrr@osprey.net>: Recipient address rejected: Cannot find your hostname, [207.250.144.22]. Ask your system manager to fix your reverse domain name registration.  If you are sending spam, go away. ; from=<bounce-isp-equipment-34419@lists.isp-lists.com> to=<rrrrrrrr@osprey.net>
#Jan 15 19:52:29 staypuft postfix/smtpd[8530]: reject: RCPT from pp2.dailyprmo1.com[64.70.17.74]: 554 <rrrrrr@osprey.net>: Recipient address rejected: Service unavailable; [64.70.17.74] blocked by relays.osirusoft.com. See http://relays.osirusoft.com for details. ]; from=<rrrrr@dailyprmo1.com> to=<rrrrrr@osprey.net>