[prev in list] [next in list] [prev in thread] [next in thread] 

List:       amavis-user
Subject:    Re: [AMaViS-user] SQL lookups and maddr table
From:       Mark Martinec <Mark.Martinec+amavis () ijs ! si>
Date:       2006-02-24 17:39:59
Message-ID: 200602241839.59518.Mark.Martinec+amavis () ijs ! si
[Download RAW message or body]

Paolo,

> >> Why domain in maddr table is in 'subdomain fields in reverse' format?
> > To facilitate producing a quick by-domain summary report by a single
> > SELECT.

> I think this kind of query is not in the documentation, right?

The schema is documented, the rest is up to your needs, imagination
and documentation on SQL language.

> Do you have any ready-to-use examples for this kind of reports?

One general example of a sophisticated SELECT is near the end of README.sql.

If you don't already have this key indexed, this might be
beneficial for queries on maddr.domain:
  CREATE INDEX maddr_idx_domain ON maddr (domain);

Here are some example on using domain-in-reverse field:

# 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;

# most spamy domains, 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
  GROUP BY sender.domain 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 cnt > 100
  ORDER BY sender.domain DESC LIMIT 100;


-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
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/
[prev in list] [next in list] [prev in thread] [next in thread]