Geison,
> I would like to select from some database the top #10 ip / domain
> spammers. In according this information i would not receive messages
> from these senders
The README.sql-pg (and README.sql-mysql) list some interesting
SQL queries, if you have logging to SQL enabled in amavisd.conf.
Some examples of a query:
-- mail from last two minutes:
SELECT
UNIX_TIMESTAMP()-msgs.time_num AS age, SUBSTRING(policy,1,2) as pb,
content AS c, dsn_sent as dsn, ds, bspam_level AS level, size,
SUBSTRING(sender.email,1,18) AS s,
SUBSTRING(recip.email,1,18) AS r,
SUBSTRING(msgs.subject,1,10) AS subj
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 IS NOT NULL AND UNIX_TIMESTAMP()-msgs.time_num < 120
ORDER BY msgs.time_num DESC;
-- clean messages ordered by count, grouped by domain:
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='C'
GROUP BY sender.domain ORDER BY cnt DESC LIMIT 50;
-- top spamy domains with >10 messages, sorted by spam average,
-- grouped by domain:
SELECT count(*) as cnt, avg(bspam_level) as spam_avg, 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 bspam_level IS NOT NULL
GROUP BY sender.domain HAVING count(*) > 10
ORDER BY spam_avg DESC LIMIT 50;
-- sender domains with >100 messages, sorted on sender.domain:
SELECT count(*) as cnt, avg(bspam_level) as spam_avg, 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
GROUP BY sender.domain HAVING count(*) > 100
ORDER BY sender.domain DESC LIMIT 100;
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
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/
