SQL for supplement MFC

[mychrislo_ironport]

Purpose

Check "Parmacy", "Parmacist" etc. email which has sent over 30 msgs within the last 24 hours.


How
mysql> select subject, remote_hostname, inet_ntoa(remote_ip), count(remote_ip) from messages where subject like '%pharm%' and timestamp > unix_timestamp(date_sub(now(), interval 1 day)) group by subject having count(remote_ip) > 30;


Why do it need it?
Some users do not enjoy free anti-spam service. And yet, mbox-full bouncing email causes some spamcop complain issue

[mychrislo_ironport]

Another simple one, with result.

Top 10 email subject for the last 7 days.

mysql> select subject, count(remote_ip) from messages where timestamp > unix_timestamp(date_sub(now(), interval 7 day)) group by subject order by 2 desc limit 0,10;

+-------------------------------------------+------------------+
| subject | count(remote_ip) |
+-------------------------------------------+------------------+
| NULL | 88009 |
| RE: | 38483 |
| quality timepieces made by Swiss jewelers | 6376 |
| RE: September 70% OFF | 4755 |
| FW: | 4188 |
| failure notice | 3096 |
| Payment Reminder | 3067 |
| =?big5?B?qfqz+Lhtt3669A==?= | 2862 |
| Are you wanting a bigger penis? | 2779 |
| RE:hard and big dick is real | 2769 |
+-------------------------------------------+------------------+
10 rows in set (1 min 29.08 sec)