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>
