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://sourcefor
ge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
AMaViS-user lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amav
is.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/ho
wto/
|
