MailScanner Monthly Report

waelaltaqi · 10-18-2007, 01:10 PM

I've been scratching my head for about a week to try to figure out this.
I'm running Mailscanner/Mailwatch server that filters spam for 4 domains.The server is wokring beaufitully... the only down side is that the clients would like some reports for what it's doing. I succsffuly created a Monthly Report filter for each domain and it's coming back with the results i want.

I would like to know if somebody have figured out a way to mail MailWatch reports out to clients. I tried to play with the report php code but it's not coming back with successull results.
Any help on this will be highly appreciated.

unSpawn · 10-21-2007, 03:31 AM

AFAIK it only renders reports. No way in the package to save it automagically.

waelaltaqi · 10-21-2007, 09:21 AM

I've written a PHP script that will do the job (thanks for Linux Questions users who put me on the right path). Not quit done yet, but for right now the script will give statistics for Total message usage for the current month (Total, Clean, Spam , Virus ... etc) , Monthly average usage statistics per mailbox and daily average usage statistics per mailbox.
The part I'm stuck on is how to mail the script out ... i'm a super PHP newbie obviously ...

ntougait · 11-15-2007, 06:18 AM

Hi,

I would be interested in your php script. Would it be possible for you to share what you already did ?

If you want to send a mail, you can just use the mail() function in php which will use the sendmail command of your box. Before it works you have to configure the php.ini file so to fill in the fields STMP, smtp_port, sendmail_from and sendmail_path.

Then what i would do is use the php linux command to execute the php script you just did. Put that command in a crontab and run it every sunday for weekly report for example.

Nathanaël

waelaltaqi · 11-16-2007, 09:24 AM

I'm a PHP/HTML beginer. I'm more of a networking guy than a programming guru. So please feel free to point out any mistakes and feel free to use the script the way you like. I kind of got involved in managing MailScanner for many domains and i needed to some reports working; that's way i had to learn php/mysql.
I couldn't find a way to mail the ouput of the script from the script itself; i'm sure that there is a way but i didn't feel like screwing with it. i used another shell script with metasend to mail the script out. If you figure out a native PHP way of doing it, please post.
You have to change three things on the script then put it on your webserver:
1- Chance the <img> tag to your company's logo.
2- change $domain to the domain you want a report for.
3- Change the password for your root mysql account
4- point your browser to it and it will generate a report for the entire month.

Code:

<html>
<head>
<meta name="keywords" content="">
<style type="text/css">
h3 {font-family: sans-serif }
h3.month {font-family: sans-serif ; color:d7060c}
h3.heading {font-family: sans-serif ; color:1c2894}
h4 {font-family: sans-serif ; font-size: x-small}
table {table-layout: automatic}
tr {font-size: x-small}
tr.first {background-color:FFE303; text-align: center ; font-family: sans-serif ; font-weight: bold}
tr.second{background-color:E8E8E8 ; text-align: center ; font-family: sans-serif}
p {font: sans-serif ; font-weight: normal}
td {padding: 0.2cm}
</style>
<title>CTI Monthly Spam Report</title>
</head>

<body>
<table>
	<tr>
	 	<td class="logo"><img src="http://www.mywebserver/CompanyLogo.jpg"></td>
	 	<td class="logo">
			<br><h3> Monthly Spam Filter Report for mydomain.com</h3>
			<h3 class="month"><?php echo date(F) . " " . date(Y);?></h2>
		</td>
	</tr>
	</td>
</table><br>


<?php 
$con = mysql_connect("localhost","root","mysqlpassword");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("mailscanner", $con);
$domain = "mydomain.com";
//All messages query:
$AllMSG = mysql_query("select COUNT(*) as 'AllMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE);");

//Clean Message query:
$CleanMSG = mysql_query("select COUNT(*) as 'CleanMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND isspam=\"0\";");

//Total Spam query:
$TSpamMSG = mysql_query("select COUNT(*) as 'TSpamMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND isspam=\"1\";");

//High Scoring Spam query:
$HSpamMSG = mysql_query("select COUNT(*) as 'HSpamMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND ishighspam=\"1\";");

//Black Listed Spam query:
$BSpamMSG = mysql_query("select COUNT(*) as 'BSpamMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND spamblacklisted=\"1\";");

//White Listed Messages:
$WMSG = mysql_query("select COUNT(*) as 'WMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND spamwhitelisted=\"1\";");

//Virus Message:
$VirusMSG = mysql_query("select COUNT(*) as 'VirusMSG' from maillog where to_domain= '$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND virusinfected=\"1\";");

//Bad Content Messages:
$BC = mysql_query("select COUNT(*) as 'BC' from maillog where to_domain='$domain' AND MONTH(date) = MONTH(CURRENT_DATE) AND nameinfected=\"1\";");

//Total Mailboxes Protected:
$TMailbox = mysql_query("SELECT COUNT(*) as 'TMailbox' FROM users WHERE username LIKE '%$domain';");

//Current Date
$CD = mysql_query("SELECT CURRENT_DATE AS 'CD';");

//Mailboxes List:
$Mailbox = mysql_query("SELECT username AS 'Users' FROM users WHERE username LIKE '%$domain'");

//Top 10 Spammers: 
$TopSpam = mysql_query("SELECT from_domain, clientip AS 'IP Address', COUNT(*) AS 'Spam Hits' FROM maillog WHERE isspam=\"1\" AND to_domain='$%domain' GROUP BY from_domain ORDER BY 'Spam Hits' DESC LIMIT 10;");

?>
<h4>Total Statistics:</h4>
<table>
  <tr class="first">
   <td>Total Messages</td>
   <td>Total Spam</td>
   <td>Total Clean</td>
   <td>High Scoring Spam</td>
   <td>White Listed Messages</td>
   <td>Black Listed Spam</td>
   <td>Virus Infected</td>
   <td>Bad Content Messages</td>	
  </tr>    
    	
  <tr class="second"> 
   <td> 
    <?php 
        while($row = mysql_fetch_array($AllMSG))
          {
            $AllMessages = $row['AllMSG'];
            echo $AllMessages ;
          }
      ?>
    </td>
    <td>     
         <?php
           while($row = mysql_fetch_array($TSpamMSG))
             {
              $TotalSpam = $row['TSpamMSG'];
              $SpamPer = round(($TotalSpam * 100 ) / $AllMessages,1);
              echo $TotalSpam . "<br>" . "(".$SpamPer.")"."%";
             }
         ?>   
    </td>
    <td>   
        <?php 
          while($row = mysql_fetch_array($CleanMSG))
            {
             $AllCleanMessages = $row['CleanMSG'];
             $CleanPer = round(($AllCleanMessages * 100 ) / $AllMessages,1);
              echo $AllCleanMessages . "<br>" . "(".$CleanPer.")"."%"; 
            }
        ?>   
    </td>
    <td>
	<?php
           while($row = mysql_fetch_array($HSpamMSG))
             {
              $HighSpam = $row['HSpamMSG'];
              $HSpamPer = round(($HighSpam * 100 ) / $AllMessages,1);
              echo $HighSpam . "<br>" . "(".$HSpamPer.")"."%";
             }
         ?> 
    </td>
    <td>
	<?php
           while($row = mysql_fetch_array($WMSG))
             {
              $WhiteMessages = $row['WMSG'];
              $WhiteMSGPer = round(($WhiteMessages * 100 ) / $AllMessages,1);
              echo $WhiteMessages . "<br>" . "(".$WhiteMSGPer.")"."%";
             }
         ?> 
    </td>		         
     <td>
	<?php
           while($row = mysql_fetch_array($BSpamMSG))
             {
              $BlackSpam = $row['BSpamMSG'];
              $BSpamPer = round(($BlackSpam * 100 ) / $AllMessages,1);
              echo $BlackSpam . "<br>" . "(".$BSpamPer.")"."%";
             }
         ?>
    </td>
     <td>   
        <?php
          while($row = mysql_fetch_array($VirusMSG))
            {
             $VirusMessages = $row['VirusMSG'];
             $VirusSpamPer = round(($VirusMessages * 100 ) / $AllMessages,3);
             echo $VirusMessages . "<br>" . "(".$VirusSpamPer.")"."%"; 
            }
        ?>   
    </td>
      <td>       
          <?php
          while($row = mysql_fetch_array($BC))
            {
             $BC = $row['BC'];
             $BCPer = round(($BC * 100 ) / $AllMessages,3);
             echo $BC . "<br>" . "(".$BCPer.")"."%"; 
            }	
        ?>   

        
      </td>
                                                                 
</table><br>

<h4>Per Mailbox Monthly Average Statistics:</h4>
<table>
 <tr class="first">
  <td>Total Mailboxes Protected </td>
   <td>Monthly Total Average</td>
   <td>Monthly Spam Messages Average</td>
   <td>Monthly Clean Messages Average</td>
   <td>Monthly Virus Messages Average</td>
  </tr> 

 <tr class="second"> 
   <td> 
    <?php 
        while($row = mysql_fetch_array($TMailbox))
          {
            $TotalMailbox = $row[TMailbox];
            echo $TotalMailbox ;
          }
      ?>
    </td>
    <td> 
    <?php 
        $AVGMonthTotal = round(($AllMessages / $TMailbox),1);
	 echo $AVGMonthTotal;
      ?>
    </td>									
    <td> 
    <?php 
        $AVGMonthSpam = round(($TotalSpam / $TMailbox),1);
	 echo $AVGMonthSpam;
      ?>
    <td> 
    <?php 
        $AVGMonthClean = round(($AllCleanMessages / $TMailbox),1);
	 echo $AVGMonthClean;
      ?>
	
    </td>
    <td>
     <?php 
        $AVGMonthVirus = round(($VirusMessages / $TMailbox),1);
	 echo $AVGMonthVirus;
      ?>
    </td>
</table><br>
<h4>Per Mailbox Daily Average Statistics:</h4>
<table>
 <tr class="first">
  <td>Total Mailboxes Protected </td>
   <td>Daily Total Messages Average</td>
   <td>Daily Spam Messages Average</td>
   <td>Daily Clean Messages Average</td>
   <td>Daily Virus Messages Average</td>
  </tr> 
<?php 
$fd_tm_ts = mktime (0, 0, 0, date ('m'), 1, date ('Y'));       
$fd_nm_ts = mktime (0, 0, 0, date ('m')+1, 1, date ('Y'));  
$nr_days = ($fd_nm_ts - $fd_tm_ts)/86400
?>
 <tr class="second"> 
   <td> 
    <?php
            echo $TotalMailbox ;
    ?>
   </td>
   <td>	
	<?PHP
	$AVGDailyTotal = round(($AVGMonthTotal / $nr_days),1); 
	echo $AVGDailyTotal; 
	?>
   </td>
   <td>	
	<?PHP
	$AVGDailySpam = round(($AVGMonthSpam / $nr_days),1); 
	echo $AVGDailySpam; 
	?>
   </td>
      <td>	
	<?PHP
	$AVGDailyClean = round(($AVGMonthClean / $nr_days),1); 
	echo $AVGDailyClean; 
	?>
   </td>
   </td>
      <td>	
	<?PHP
	$AVGDailyVirus = round(($AVGMonthVirus / $nr_days),3); 
	echo $AVGDailyVirus; 
	?>
   </td>
 <tr>
</table>
<?php mysql_close($con);?>
</body>
</html>