[Greylist-users] Up and running on the real sever - and I have

[Dennis Wynne]

===== Original Message from greylist-users at lists.puremagic.com (Greylisting 
Users and Developers Discuss) at 2/15/06 11:39 am
>4) Does anyone have any reporting scripts that they can share?  My users
>would like me to give them a report of any mail that was seen, but not
>passed (no retires in the allowed time) so they can see if they missed
>anything. I think I could do these myself, but I am hoping someone already
>has done some report stuff and will share it. Any other reports would be
>nice as well - and if there is a way to serve them up HTTP for local users
>that would be even better.
>

Here is what I came up with:

use relaydelay;

select distinct mail_from, ',', 
rcpt_to,',',passed_count,',',relay_ip,',',last_update from relaytofrom rtf
where locate('domain_in_question.com', rcpt_to) > 0
  and origin_type = 'AUTO'
  and not exists
  (select rcpt_to
   from relaytofrom sub
   where rtf.mail_from = sub.mail_from
     and rtf.rcpt_to = sub.rcpt_to
     and passed_count > 0)
order by rcpt_to, mail_from;


This shows me the auto-added triplets that have passed 0 messages "today". 
The extra code (not exists) removes triplets from the result that passed on 
a different IP.

I send the results of the query to an admin assistant who breaks the list up 
and sends each user their bounces.  I have had to manually whitelist a few 
customer's machines due to their mail servers not following specs. Most 
important, the boss is happy that if we miss some mail we will know :-)

Dennis