Ok, here we go. After configuring Amavis+SQL it is useful and a MUST to make reports to demostrate that amavis antispam solution is working.
I was reading README.sql-mysql file and I do next: 1. To have gauss graph in order to demostrate probability of mail that is arriving with each grade and to demostrate that that we are stopping spam (not virri, nor badh, nor forbidden only spam) about X grade: SELECT count(*) as cnt, avg(bspam_level) as spam_avg, std(bspam_level) as spam_std FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id; How ever I'm not very sure about this because Virii and Blocked also have bpsam_level to 0. 1a. I'm thinking this is better because only I have Spam and Clean (grade is valid). SELECT count(*) as cnt, avg(bspam_level) as spam_avg, std(bspam_level) as spam_std FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id WHERE (content='S' or content='C'); 1b. Same than 1a but by day: SELECT count(*) as cnt, avg(bspam_level) as spam_avg, std(bspam_level) as spam_std, date(time_iso) as dd FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id WHERE (content='S' or content='C') GROUP BY dd; 2. Mean & STD Desviaton for SPAM by domain in order to know who is top spammer: SELECT count(*) as cnt, avg(bspam_level), sender.domain FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id WHERE content='S' GROUP BY sender.domain ORDER BY cnt DESC; 3. extract how many emails are per day: SELECT count(*), date(time_iso) as dd FROM msgs GROUP BY dd; (if date() is changed by month then will be by month, usefull for anual reports) 4. Total of mails marked as SPAM, Virii, Clean, Blocked to make a pie chart later: SELECT count(*) as cnt, content FROM msgs LEFT JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid=sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid=recip.id GROUP BY content ORDER BY cnt DESC; 5. Extract how many emails have been classfied (and how): select count(*), content from msgs GROUP BY content; 6. Extract classified (msgs.content) per day: SELECT COUNT(content), DATE(time_iso) AS dd, content FROM msgs WHERE content = 'S' GROUP BY dd UNION SELECT COUNT(content), DATE(time_iso) AS dd, content FROM msgs WHERE content = 'C' GROUP BY dd UNION SELECT COUNT(content), DATE(time_iso) AS dd, content FROM msgs WHERE content = 'V' GROUP BY dd UNION SELECT COUNT(content), DATE(time_iso) AS dd, content FROM msgs WHERE content = 'B' GROUP BY dd ; This It could be optimized, but I'm not a SQL expert. Well, it would be nice to have a set of SQL other than statistics to have useful information. Please, comment TIA LD Well, in fact I think these are main. ------------------------------------------------------------------------------ _______________________________________________ AMaViS-user mailing list AMaViS-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/amavis-user AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3 AMaViS-HowTos:http://www.amavis.org/howto/
