Postfix SASL Sender Statistics

In some cases we’d like to know, how much has been sent by our Postfix server on a per account basis.

I assume you have a SASL authenticated Postfix server with MySQL backend

For installing pflogsumm please go to http://www.howtoforge.com/mail_statistics_mailgraph_pflogsumm

For the extended stats, lets replace /usr/local/sbin/postfix_report.sh with /usr/local/sbin/postfix_report.php

don’t forget to change the password and recipient e-mail

<?php
$con = mysql_connect('localhost','postconnect','*******');

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$report = fopen('report.txt', 'w');
$mailname = shell_exec('cat /etc/mailname');
shell_exec('gunzip /var/log/mail.log.0.gz');

mysql_select_db("mail", $con);

$accs = mysql_query("select email from users");

fwrite($report,"Sent\tAccount\n");
while($accs_row = mysql_fetch_array($accs))
  {
        $count=intval(shell_exec('cat /var/log/mail.log.0|grep sasl_username|grep '.$accs_row['email'].'|wc -l'));
        fwrite($report,$count."\t".$accs_row['email']."\n");
  }
$pflogsumm=shell_exec('pflogsumm /var/log/mail.log.0');
fwrite($report,"\n".'More details from pflogsumm...'."\n".$pflogsumm);
fclose($report);
shell_exec('cat report.txt | mail -s "Mail Statistics at '.$mailname.'" user@my.domain');
shell_exec('rm report.txt;gzip /var/log/mail.log.0');
mysql_close($con);
?>

obviously you have to install some extra modules too

apt-get install php5 php5-mysql

and modify /etc/crontab

0 7 * * * php /usr/local/sbin/postfix_report.php &> /dev/null
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s