[Greylist-users] I need graylisting stats

[Barb Dijker]

SELECT count(blocked_count) FROM relayreport WHERE passed_count = 0
    AND record_expires < NOW() - interval 0 day
    AND record_expires > NOW() - interval 1 day;

That will give you a total of all the attempts that were not retried 
within the retry time (auto_record_life_secs) for a 24 hour period.  
Shift the 0-1 days to get different days.  For example, the above gives 
you the immediately prior 24 hours (if you run it about the same time 
your run the maintenance script).  If you do:

SELECT count(blocked_count) FROM relayreport WHERE passed_count = 0
    AND record_expires < NOW() - interval 1 day
    AND record_expires > NOW() - interval 2 day;

You get another 24 hours before.  You can go back as far as you want.  
You can run this count in the maintenance script to be reported from 
cron nightly.  If your auto_record_life_secs has been adjusted bigger, 
you might want to use create_time in your select rather than 
record_expires.  But the records are not moved from the relaytofrom 
table to relayreport until record_expires.  So be mindful of which 
table you query.

We take the total msgsfr and msgsto stats reported by sendmail 
(mailstats) and graph it with mrtg.  We also graph identified spam 
(from spamd syslog) vs. dnsbl (from mailstats connection msgsrej).  
Both graphs are incredibly telling comparing the before and after 
greylisting.  They're also very useful for identifying something stupid 
going on.

Barb Dijker
Netrack, Inc., 3080 Valmont Rd Ste 200, Boulder CO 80301
303.938.0188, toll free 1.888.9Netrack, fax 303.938.0177
www.netrack.net