[Greylist-users] Daily report SQL code

[Dennis Wynne]

Someone asked about this off the list. I thought I had shared this with the 
list before, but perhaps not. Here is the SQL code and shell script to make 
a report for "today" for triplets with passed count of 0.


The SQL query is:

use relaydelay;
select distinct mail_from, ',', 
rcpt_to,',',passed_count,',',relay_ip,',',last_update
from relaytofrom rtf
where locate('yourdomaingoeshere.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;


You can run it from a shell script (if you don't mind putting the UID and 
password in the script) like this (put the above SQL into a file to use for 
input e.g. reportscript.sql):

mysql --user=dbuser --password=password  < reportscript.sql > report.txt

The resulting file will look like this:

mail_from       ,       rcpt_to ,       passed_count    ,       relay_ip,
   last_update
<travel at travelads18.com>        ,       <user at yourdomain.com>  ,       0    
64.86.166.231   ,       2007-03-07 00:49:07
<giddiestworshipful at cbnapavalley.com>   ,       <user2 at yourdomain>    , 0
   ,       59.29.44.205    ,       2007-03-06 22:03:43

Let me know if you have any questions,

Dennis