[mrtg] Spamassassin and MRTG: a solution

[Rainer Sokoll]

Hi,

if you want to graph your Spamasssasin statistics on your central mail
hub(s), here is my solution:

I use:
- sendmail
- spamassassin (http://spamassasin.org/)
- syslog-ng (http://www.balabit.com/products/syslog_ng/)
- mysql
- MRTG, of course

On the mail hubs, I forward all maillogs to a central log server running
syslog-ng. This syslogd writes all logs into a mysql database. I
borrowed the code from http://vermeer.org/syslog/.
Now for mrtg:

-----8<------
Title[mailgate-spam]: Spam on mailgate.intershop.de
MaxBytes[mailgate-spam]: 5000
AbsMax[mailgate-spam]: 10000
Options[mailgate-spam]: gauge,nopercent,dorelpercent
Target[mailgate-spam]: `/usr/local/share/it/mrtg/bin/spamstats.sh
mailgate`
PageTop[mailgate-spam]: <H1>Spam on mailgate.intershop.de</H1>
        <P>We assume that a Spam level > 5 is real spam
YLegend[mailgate-spam]: Mails
ShortLegend[mailgate-spam]: mails
Legend1[mailgate-spam]: Spam
Legend2[mailgate-spam]: processed mails
LegendI[mailgate-spam]: &nbsp;Spam:&nbsp;
LegendO[mailgate-spam]: &nbsp;processed:&nbsp;
-----8<------

spamstats.sh:

-----8<------
#!/bin/sh
export MYSQL="/usr/bin/mysql -uremoved -premoved"
$MYSQL syslog < /usr/local/share/it/mrtg/bin/spamstats.mysql.$1 | \
  awk '{print $4}' | \
  awk 'BEGIN{i=j=0;FS="(";RS="/"}{i++;if($2>5){j++}}END{print j;print i}'
-----8<------

spamstats.mysql.mailgate:

-----8<------
select msg from logs where date=curdate() and time > from_unixtime(unix_timestam p()-300,'%H:%i') and host="mailgate" and msg like "spamd%clean message%";
-----8<------

That's all.

Since I am neither an expert in awk nor in mysql, I am pretty sure that
there are more elegant solutions, but I thought that someono of you can
use this as a skeleton for his own solution.

Rainer

--
Unsubscribe mailto:mrtg-request at list.ee.ethz.ch?subject=unsubscribe
Archive     http://www.ee.ethz.ch/~slist/mrtg
FAQ         http://faq.mrtg.org    Homepage     http://www.mrtg.org
WebAdmin    http://www.ee.ethz.ch/~slist/lsg2.cgi