Spam - the fight continues..

# prune logs. - delete records from trylog that are older than 5 days.
warn  condition = ${lookup mysql{delete from email_trylog  where  try <  DATE_SUB(NOW(), INTERVAL 5 DAY)  }{no}{no}}
        log_message = "Something impossible happened";

# totally new ip address (based on iplog)
#- insert into email_trylog
#- insert into email_iplog
#- defer.

#no record what so ever of the IP address:
defer   condition = ${lookup mysql{select count(id) from email_iplog \
                                where ip='${sender_host_address}' having count(id) > 0 \
                                }{no}{yes}}
        condition = ${lookup mysql{insert into email_trylog (ip,title,sender,host,try) values ( \
                                '${sender_host_address}',SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                '${quote_mysql:$sender_address}', '${quote_mysql:$sender_helo_name}',NOW()) \
                                }{yes}{yes}}
        condition = ${lookup mysql{insert into email_iplog (ip,title,sender,host,try) values ( \
                                '${sender_host_address}',SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                '${quote_mysql:$sender_address}', '${quote_mysql:$sender_helo_name}',NOW()) \
                                }{yes}{yes}}
        message = "Delivery defered while we check the IP address (new IP)"

#blacklisted.
deny    condition = ${lookup mysql{select id from email_iplog where ip='${sender_host_address}' and blacklist > 0}{yes}{no}}
        condition = ${lookup mysql{update email_iplog set \
                                title=SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                sender='${quote_mysql:$sender_address}', \
                                host='${quote_mysql:$sender_helo_name}', \
                                try=NOW(), \
                                efforts = efforts + 1 \
                                WHERE ip='${sender_host_address}' \
                                }{yes}{yes}}
        condition = ${lookup mysql{insert into email_trylog (ip,title,sender,host,try) values (\
                                '${sender_host_address}',SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                '${quote_mysql:$sender_address}', '${quote_mysql:$sender_helo_name}',NOW()) \
                                }{yes}{yes}}
        message = "Your IP {$sender_host_address} was blacklisted, please email this message to akbkhome@the-gmail.com to get unlisted"


#not approved or looked at yet..

defer   condition = ${lookup mysql{select id from email_iplog where ip='${sender_host_address}' and whitelist < 1 and greylist < 1}{yes}{no}}
        condition = ${lookup mysql{update email_iplog set \
                                title=SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                sender='${quote_mysql:$sender_address}', \
                                host='${quote_mysql:$sender_helo_name}', \
                                try=NOW(), \
                                efforts = efforts + 1 \
                                WHERE ip='${sender_host_address}' \
                                }{yes}{yes}}
        message = "Delivery defered while we check the IP address (more that 1 effort)"


warn    condition = ${lookup mysql{insert into email_trylog (ip,title,sender,host,try) values (\
                                '${sender_host_address}',SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                '${quote_mysql:$sender_address}', '${quote_mysql:$sender_helo_name}',NOW()) \
                                }{no}{no}}
        log_message = "updating trylog failed.?"

warn    condition = ${lookup mysql{update email_iplog set \
                                title=SUBSTRING('${quote_mysql:$h_subject:}',1,255), \
                                sender='${quote_mysql:$sender_address}', \
                                host='${quote_mysql:$sender_helo_name}', \
                                try=NOW(), \
                                efforts = efforts + 1 \
                                sucess = success + 1 \
                                WHERE ip='${sender_host_address}' \
                                }{no}{no}}
        log_message = "updating iplog failed.?"

defer condition  = ${lookup mysql{select count(email_trylog.id) from \
            email_trylog LEFT JOIN email_iplog on email_trylog.ip = email_iplog.ip where email_trylog.ip='${sender_host_address}' AND \
             email_trylog.title=SUBSTRING('${quote_mysql:$h_subject:}',1,255) AND \
             email_trylog.sender='${quote_mysql:$sender_address}' AND \
             email_trylog.host='${quote_mysql:$sender_helo_name}' AND \
            email_trylog.try >  DATE_SUB(NOW(), INTERVAL 2 DAY)  AND \
            email_iplog.whitelist = 0 AND \
            email_iplog.success < 12 \
            HAVING count(email_trylog.id) < 1}{yes}{no}}
    condition = ${lookup mysql{insert into email_trylog (ip,title,sender,host,try) values ('${sender_host_address}',SUBSTRING('${quote_mysql:$h_subject:}',1,255), '${quote_mysql:$sender_address}', '${quote_mysql:$sender_helo_name}',NOW()) }{yes}{yes}}
    message = "Delivery defered while we check the IP address (new message)"

CREATE TABLE `email_trylog` (
 `id` int(11) NOT NULL auto_increment,
 `ip` varchar(16) default '',
 `title` varchar(255) default '',
 `sender` varchar(255) default '',
 `host` varchar(255) default '',
 `try` datetime default NULL,
 PRIMARY KEY (`id`)
) TYPE=MyISAM

CREATE TABLE `email_iplog` (
 `id` int(11) NOT NULL auto_increment,
 `ip` varchar(16) default '',
 `title` varchar(255) default '',
 `sender` varchar(255) default '',
 `host` varchar(255) default '',
 `try` datetime default NULL,
 `blacklist` int(11) default '0',
 `whitelist` int(11) default '0',
 `greylist` int(11) default '0',
 `efforts` int(11) default '1',
 `success` int(11) default '0',
 PRIMARY KEY  (`id`)
) TYPE=MyISAM