Getting stats from hMailServer Greylisting
Greylisting is one of the many powerful spam blocking tools at your disposal and is built right into hMailServer. Although hMailServer does have a logging option which the popular AWStats application can read, neither this or any of the other standard logging options in hMailServer give you any real insite into just how effective Greylisting is. Fortunately though all this information is stored in the hMailServer database in the greylisting triplets table, and in this article we'll be looking at how to use a few SQL queries in phpMyAdmin to extract some useful data from this.
FIRST THINGS FIRST
The obvious prerequisites to this article are to have hMailServer up and running as well as phpMyAdmin. For this have a look at my article on setting up your own email server with hMailServer as well as how to install and configure phpMyAdmin on IIS. If you are yet to install anything, you may also want to check out my guides on how to install and configure MySQL 5 on Windows as well as installing PHP 5 on IIS in 5 simple steps.
AN OVERVIEW OF GREYLISTING
The very important thing to understand about Greylisting is that it does absolutely no examination of an emails content to verify if it is spam or not as it works entirely before the delivery of the email content takes place. Greylisting works by telling all initial email delivery attempts to try again later and then taking the sending SMTP server IP address, sending email address and reciepient email address (a.k.a the Greylisting Triplet) and storing that in a database. Upon receiving this try again later message an RFC compliant email server MUST re-queue this email for delivery which should be no less than 30 minutes, however as long as the next delivery attempt is greater than your defined defer period the email will go straight through as will all subsequent deliveries with the same details.
The effect this will have on legitimate users is a slight delay in email delivery, however spammers whose operation depends on the distribution of vast quantities of email rarely have the capacity to be able to re-queue email and therefore are blocked. The best thing is that all this is done before any email delivery takes place which means you save significant resources on CPU/Memory intensive AV and spam analysis.
As eluded to above, there are a few other important Greylisting settings. These being your defer time (should be less than 30 minutes), days before removing unused records and days before removing used records. A balance must be found here, however in general you'll find you won't need an extended defer time (10~15 minutes is usually plenty) as spammers generally never retry or retry almost straight away. As for removing used/unused records the general rule of thumb I use is to find the interval between emails for the least commonly used legitimate accounts and set this deletion period to somewhere around this. Conversely you also need to ensure you don't keep the records too long as should the same spamming email server try another run of spam delivery within this deletion time then the delivery will be successful. Because of this it is common to have a relatively short deletion period for unused records and fairly lengthy deletion period for used records. The only real caveat here is that if your deletion policies are different your stats will not be accurate when using the SQL queries shown in this article.
SO HOW MUCH SPAM IS STOPPED BY GREYLISTING?
So how do we gauge this? The essential statistic for determining the effectiveness of your Greylisting implementation is the total delivery attempts compared with the amount of one time delivery attempts as well as deliveries with multiple blocked attempts where there are no successful deliveries. This last stat can be a bit confusing, however all it identifies are servers that retried delivery multiple times and gave up before your defer period had expired. Basically it is aimed at tripping up spammers who retry instantly as the current SMTP RFC states that a server should continue to retry delivery for 4~5 days before giving up.
To get these stats log into your phpMyAdmin instance and select your hMailServer database, then paste the following SQL code into the SQL tab and hit the go button.
SELECT COUNT(*) AS Total,
SUM(IF(`glpassedcount` = 0 AND `glblockedcount` = 0, 1, 0)) AS SingleAttempt,
SUM(IF(`glblockedcount` > 0 AND `glpassedcount` = 0, 1, 0)) AS MultiBlockedNoPass
Depending on your delivery defer and deletion policies for used/unused records you should get some numbers like this in return.
Total SingleAttempt MultiBlockedNoPass
2824 2483 3
With my deletion policy set to 60 days for used/unused records what this is saying is that out of 2824 email delivery attempts, 2483 of them were only attempted once and there were 3 retried delivery attempts which gave up before the defer time had expired (which as explained above is against RFC recommendations).