Here’s a slightly modified quarantine_report.php that I use. It is sent daily, and includes only messages that were dated on the previous day (so it doesn’t matter what time of day this is run by cron).
The other enhancement is to sort the results by spam score, least spammy at the top, and to colour the table rows according to ranges of spam score. Thus the most likely false-positive messages are listed at the top of the message, which a white background, and the most spammy messages are listed at the bottom with a dark red background. This makes it easy to scan the message for potential false positives, while also showing the number of very spammy messages that MailScanner is filtering out.
Please e-mail comments, improvements, etc. to me at ajcartmell@fonant.com.
Thanks to Steve for writing the original version!
#!/usr/local/bin/php -q <?php /* MailWatch for MailScanner Copyright (C) 2003 Steve Freegard (smf@f2s.com) Modified by ajcartmell@fonant.com to report on just messages from the previous day, and to sort the table by spam score, with rows coloured to indicate spam levels. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ // Change the following to reflect the location of functions.php require_once('/home/sites/nick.fonant.com/web/mailscanner/functions.php'); require_once('Mail.php'); require_once('Mail/mime.php'); ini_set('html_errors','off'); ini_set('display_errors','on'); ini_set('implicit_flush','false'); ini_set("memory_limit",'256M'); ini_set("error_reporting",E_ALL); ini_set("max_execution_time",0); /* ** HTML Template */ $html = '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>Message Quarantine Report</title> <style type="text/css"> <!-- body, td, tr { font-family: "Trebuchet MS",Trebuchet,sans-serif; font-size: 8pt; } th { background: #3574B3; color: #ffffff; padding: 2px 0; } --> </style> </head> <body marginheight="5" marginwidth="5"> <h1>E-mail Quarantine Report for %s</h1> <p>In the last day you received %s e-mails that were quarantined, as listed below. All messages in the quarantine are automatically deleted %s days after the date that they were received.</p> <p>The list is ordered by Spam Score, with the messages considered least like spam at the top.</p> %s </body> </html>'; $html_table = '<table width="100%%" border="0"> <tr> <th>Received</th> <th>From</th> <th>Subject</th> <th>Spam Score</th> <th>Reason</th> <th>Action</th> </tr> %s </table>'; function html_row($time,$from,$subject,$score,$reason,$action) { $row_background = '#f0f0f0'; if ($score>=9) $row_background = '#f0dddd'; if ($score>=12) $row_background = '#e9cccc'; if ($score>=15) $row_background = '#d6bbbb'; $out = ' <tr bgcolor="'.$row_background."\">\n"; $out .= ' <td style="white-space: nowrap">'.htmlspecialchars($time)."</td>\n"; $out .= ' <td>'.htmlspecialchars($from)."</td>\n"; $out .= ' <td>'.htmlspecialchars($subject)."</td>\n"; $out .= ' <td>'.htmlspecialchars($score)."</td>\n"; $out .= ' <td>'.htmlspecialchars($reason)."</td>\n"; $out .= ' <td>'.$action."</td>\n"; $out .= " </tr>\n"; return $out; } /* ** Text Template */ $text = 'Quarantine Report for %s In the last 25 hours you have received %s e-mails that have been quarantined and are listed below. All messages in the quarantine are automatically deleted %s days after the date that they were received. The list is ordered by Spam Score, with the messages considered least like spam at the top. %s'; $text_content = 'Received: %s From: %s Subject: %s Reason: %s Spam Score: %s Action: %s '; /* ** SQL Templates */ $users_sql = " SELECT username, quarantine_rcpt, type FROM users WHERE quarantine_report=1 "; $filters_sql = " SELECT filter FROM user_filters WHERE username=%s AND active='Y' "; $sql = " SELECT DISTINCT a.id AS id, DATE_FORMAT(timestamp,'".str_replace('%','%%',DATE_FORMAT)." ".str_replace('%','%%',TIME_FORMAT)."') AS datetime, a.from_address AS from_address, a.subject AS subject, sascore, CASE WHEN a.virusinfected>0 THEN 'Virus' WHEN a.nameinfected>0 THEN 'Bad Content' WHEN a.otherinfected>0 THEN 'Infected' WHEN a.ishighspam>0 THEN 'Spam' WHEN a.issaspam>0 THEN 'Spam' WHEN a.isrblspam>0 THEN 'Spam' WHEN a.spamblacklisted>0 THEN 'Blacklisted' WHEN a.isspam THEN 'Spam' WHEN a.ismcp>0 THEN 'Policy' WHEN a.ishighmcp>0 THEN 'Policy' WHEN a.issamcp>0 THEN 'Policy' WHEN a.mcpblacklisted>0 THEN 'Policy' WHEN a.isspam>0 THEN 'Spam' ELSE 'UNKNOWN' END AS reason FROM maillog a WHERE a.quarantined = 1 AND ((to_address=%s) OR (to_domain=%s)) AND TO_DAYS(date) = TO_DAYS(NOW())-1 ORDER BY sascore, a.date DESC, a.time DESC"; $result = dbquery($users_sql); $rows = mysql_num_rows($result); if($rows>0) { while($user=mysql_fetch_object($result)) { dbg("\n === Generating report for ".$user->username." type=".$user->type); // Work out destination e-mail address switch($user->type) { case 'U': // Type: user - see if to address needs to be overridden if(!empty($user->quarantine_rcpt)) { $email = $user->quarantine_rcpt; } else { $email = $user->username; } break; case 'D': // Type: domain admin - this must be overridden $email = $user->quarantine_rcpt; break; default: // Shouldn't ever get here - but just in case... $email = $user->quarantine_rcpt; break; } // Make sure we have a destination address if(!empty($email)) { dbg(" ==== Recipient e-mail address is $email"); // Get any additional reports required $filters = array_merge(array($user->username),return_user_filters($user->username)); foreach($filters as $filter) { dbg(" ==== Building list for $filter"); $quarantined = return_quarantine_list_array($filter); dbg(" ==== Found ".count($quarantined)." quarantined e-mails"); //print_r($quarantined); if(count($quarantined)>0) { send_quarantine_email($email,$filter,$quarantined); } unset($quarantined); } } else { dbg(" ==== ".$user->username." has empty e-mail recipient address, skipping..."); } } } function dbg($text) { echo $text."\n"; } function return_user_filters($user) { global $filters_sql; $result = dbquery(sprintf($filters_sql,quote_smart($user))); $rows = mysql_num_rows($result); if($rows>0) { while($row=mysql_fetch_object($result)) { $array[] = $row->filter; } return $array; } else { return array(); } } function return_quarantine_list_array($filter) { global $sql; $query = sprintf($sql,quote_smart($filter),quote_smart($filter)); $result = dbquery($query); $rows = mysql_num_rows($result); if($rows>0) { while($row=mysql_fetch_object($result)) { $array[] = array( 'id' => trim($row->id), 'datetime' => trim($row->datetime), 'from' => trim_output($row->from_address,FROMTO_MAXLEN), 'subject' => trim_output($row->subject,SUBJECT_MAXLEN), 'sascore' => trim($row->sascore), 'reason' => trim($row->reason)); } return $array; } else { // Return an empty array return array(); } } function send_quarantine_email($email, $filter, $quarantined) { global $html, $html_table, $text, $text_content; // Setup variables to prevent warnings $h1 = ""; $t1 = ""; // Build the quarantine list for this recipient foreach($quarantined as $qitem) { // HTML Version $h1 .= html_row($qitem['datetime'], $qitem['from'], $qitem['subject'], $qitem['sascore'], $qitem['reason'], '<a href="'.QUARANTINE_REPORT_HOSTURL.' /viewmail.php?id='.$qitem['id'].'">View</a>'); // Text Version $t1 .= sprintf($text_content, strip_tags($qitem['datetime']), $qitem['from'], $qitem['subject'], $qitem['sascore'], $qitem['reason'], '<a href="'. QUARANTINE_REPORT_HOSTURL.'/viewmail.php?id='.$qitem['id'].'">View</a>'); } // HTML $h2 = sprintf($html_table, $h1); $html_report = sprintf($html, $filter, /* QUARANTINE_REPORT_DAYS, */count($quarantined), QUARANTINE_DAYS_TO_KEEP, $h2); if(DEBUG) { echo $html_report; } // Text $text_report = sprintf($text, $filter, /*QUARANTINE_REPORT_DAYS, */count($quarantined), QUARANTINE_DAYS_TO_KEEP, $t1); if(DEBUG) { echo "<PRE>$text_report</PRE>\n"; } // Send e-mail $mime = new Mail_mime("\n"); $hdrs = array('From' => QUARANTINE_REPORT_FROM_NAME.' <'.QUARANTINE_FROM_ADDR.'>', 'To' => $email, 'Subject'=> QUARANTINE_REPORT_SUBJECT, 'Date' => date("r")); $mime->addHTMLImage(MAILWATCH_HOME.'/images/mailwatch-logo.gif','image/gif','mailwatch-logo.gif',true); $mime->setTXTBody($text_report); $mime->setHTMLBody($html_report); $body = $mime->get(); $hdrs = $mime->headers($hdrs); $mail_param = array('host' => QUARANTINE_MAIL_HOST); $mail =& Mail::factory('smtp',$mail_param); $mail->send($email,$hdrs,$body); dbg(" ==== Sent e-mail to $email"); } ?>