Ehnancement for SqlGrey users in MailWatch

Questions and answers about how to do stuff
Post Reply
nicola.piazzi
Posts: 388
Joined: 23 Apr 2015 09:45

Ehnancement for SqlGrey users in MailWatch

Post by nicola.piazzi »

When we use graylist can be useful to have these 2 lines in MailWatch
Cattura.PNG
Cattura.PNG (10.13 KiB) Viewed 1652 times
They tell GreyList waiting queue at 5 and 30 minutes
And tell today delayed messages ang % delayed of total messages

To do this 2 steps :
Add mailwatch user to sqlgrey database
mysqlp=$(grep MYSQLROOTPWD /etc/eFa/MySQL-Config | sed 's/.*://') ; mysqldump -u root -p$mysqlp
use sqlgrey;
grant select on *.* to 'mailwatch'@'localhost' ;


Put this code in functions.php (Blue is original code and Green ia added code)
cp /var/www/html/mailscanner/functions.php /var/www/html/mailscanner/functions.php.ori
vi /var/www/html/mailscanner/functions.php


echo ' <tr><td>' . __('hscomcp03') . '</td><td>' . number_format(
$row->highmcp
) . '</td><td>' . $row->highmcppercent . '%</td></tr>' . "\n";
}


// # GREYLIST QUEUE
$q5 = "SELECT COUNT(*) AS g5 FROM sqlgrey.connect WHERE first_seen >= DATE_SUB(NOW(),INTERVAL 5 MINUTE)" ;
$q30 = "SELECT COUNT(*) AS g30 FROM sqlgrey.connect WHERE first_seen >= DATE_SUB(NOW(),INTERVAL 30 MINUTE)" ;
$r5 = dbquery($q5); $r30 = dbquery($q30);
$rr5 = $r5->fetch_object(); $rr30 = $r30->fetch_object();
echo ' <tr><td style="white-space:nowrap">GreyQueue (5 / 30 min):</td><td align="right">' . $rr5->g5 . '</td><td align="right">' . $rr30->g30 . '</td></tr>' . "\n";

// # GREYLIST DELAYED
$qd = "SELECT SUM(CASE WHEN headers LIKE '%X-Greylist: delayed%' THEN 1 ELSE 0 END) AS ct, ROUND(SUM(CASE WHEN headers LIKE '%X-Greylist: delayed%' THEN 1 ELSE 0 END)/COUNT(*)*100,1) AS cp FROM maillog WHERE clientip NOT LIKE '10.%' AND date = CURRENT_DATE()" ;
$rd = dbquery($qd);
$rrd = $rd->fetch_object();
echo ' <tr><td style="white-space:nowrap">Delayed Today (n / %):</td><td align="right">' . $rrd->ct . '</td><td align="right">' . $rrd->cp . '%</td></tr>' . "\n";



echo '</table>' . "\n";
}
}

function printNavBar()
{
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: Ehnancement for SqlGrey users in MailWatch

Post by pdwalker »

I *LIKE* it.

Thanks Nicola!
Post Reply