Spamassassin statistics per user in SQL
The last few days I've been busy reconfiguring my mailserver (postfix) and spamkiller (spamassasin). I wanted to have per-user controls over the spam filtering, and I wanted the spam to be placed in a spambox on the server (instead of clients having to filter and move spam). This was a bit difficult since I use virtual mail users, not real system users.
Eventually I succeeded, using maildrop (http://www.courier-mta.org/maildrop/) instead of the postfix virtual delivery agent. Maildrop now invokes spamassassin based on a per-user (virtual user) config file, and drops spam to INBOX.Spambox. Cool!
However, I also wanted some nice statistics of the filtering. It would be nice to see what percentage of the mail coming through the server is spam, and which users get the most spam. I couldn't find a nice solution for this however. The solution that came the closest was sa-stats.pl (http://0wned.it/view/2007/03/17/sastats_in_cvs/). It prints out the statistics I want, based on logfile analysis. However, it doesn't collect statistics over a longer period.
So what I did is this: I modified sa-stats.pl to save the found statistics into a MySQL database. Using MySQLs aggregate functions (SUM, AVG, COUNT) it is very easy to get statistics of spam and ham, and totals. I wrote a PHP program to print these statistics.
So, here is the modified script, and here the SQL file that creates the tables it uses. Don't forget to change the database settings in the pl file around line 148!
And here is a screenshot of what the output looks like.
Enjoy!
