1Email数据、帐号、邮件数据挖掘PostFix2012_01

gaofeng0210

show tables;

+-----------------+

| Tables_in_mail |

+-----------------+

| admin          |超级管理员帐号

| blackclient    |空（黑名单？）

| connectlog     |连接(帐号-IP对应表)

| datadir        |空

| domain         |域名&帐号

| domainlog      |

| iplog          |ip日志

| lastline       |最后在线

| loginlog       |登录日志

| mail_log       |空?

| maillist       |空

| maillog        |邮件日志-(邮件源地址,目的地址等

| maillog_history |每小时的日志

| maillog_tmp    |邮件日志的临时文件

| mydest         |cass.org.cn 目的

| mytrans        |cass.org.cn 10.1.8.3

| property       |空

| sendlog        |发送(邮件)日志

| spamlog        |垃圾(邮件)日志

| timemail       |空

| user           |用户

| userlog        |用户日志

| viruslog       |病毒日志

连机最多的邮件帐号：

select email,count(*) as a1 from connectlog wheretime like "%2012-01-07%" group by email order by a1 desc limit 9;

连机最多的邮件帐号：

select email,count(*) as a1 from connectlog wheretime like "%2012-01-07%" group by email order by a1 desc limit 9;

邮件帐号连接日志：

select * from connectlog limit 9;

显示日期目录：datadir

ip连接显示：

select * from iplog order by date desc limit 9 ;

select count(*) as a1,ip from iplog where datelike "%2012-01-07%"  group byip order by a1 desc limit 9;

lastline: 最后一行

select * from lastline limit 9;

loginlog:

mail_log:（空）

select * from mail_log limit 99;

maillist： (空)

邮件列表

maillog：（邮件日志）

maillog

接收邮件帐号：

select mto,count(*) as a1 from maillog where datelike "%2012-01-07%" group by mto order by a1 desc limit 9;

maillog_history:(邮件拒绝）

select * from maillog_history where date like"%2012-01-05%" limit 9;

sendlog: （发送日志）

select email,count(*) as a1 from sendlog wheredate like "%2012-01-07%" group by email  order by a1 desc limit 9;

+--------------------------+-----+

| email                   | a1  |

+--------------------------+-----+

| rtyrdudthfffxddh@126.com | 173 |

| cgfjgjgjvcgdfb@126.com   | 172 |

| fcfhdhfhgxcvvxg@163.com  | 148 |

| hfxdgdsggfvfg@gmail.com  | 137 |

| jinhong@cass.org.cn      | 38 |

| jusjiang@cass.org.cn     |  28|

| buping@cass.org.cn       | 25 |

| ky_law@cass.org.cn       | 25 |

| PostMaster@qq.com        | 21 |

+--------------------------+-----+

9 rows in set (0.09 sec)

select email,count(num) as a1 from sendlog wheredate like "%2012-01-07%" group by email  order by a1 desc limit 9;

+--------------------------+-----+

| email                    | a1  |

+--------------------------+-----+

| rtyrdudthfffxddh@126.com | 173 |

| cgfjgjgjvcgdfb@126.com   | 172 |

| fcfhdhfhgxcvvxg@163.com  | 148 |

| hfxdgdsggfvfg@gmail.com  | 137 |

| jinhong@cass.org.cn      | 38 |

| jusjiang@cass.org.cn     | 28 |

| buping@cass.org.cn       | 25 |

| ky_law@cass.org.cn       | 25 |

| PostMaster@qq.com        | 21 |

+--------------------------+-----+

9 rows in set (0.10 sec)

spamlog:

select mfrom,count(num) as a1 from spamlog wheredate like "%2012-01-07%" group by mfrom  order by a1 desc limit 9;

+-------------------------------+-----+

| mfrom                         | a1  |

+-------------------------------+-----+

| rtyrdudthfffxddh@126.com      | 173 |

| cgfjgjgjvcgdfb@126.com        | 172 |

| fcfhdhfhgxcvvxg@163.com       | 150 |

| hfxdgdsggfvfg@gmail.com       | 137 |

|                               |  95 |

| customer@promotion.pingan.com |  20 |

| chenghe689@126.com            | 20 |

| admin@system.mail             | 19 |

| easgrfftesfgfddd@aol.com      | 18 |

+-------------------------------+-----+

9 rows in set (0.06 sec)

user:(用户帐号)

select count(*) from user;

+----------+

| count(*) |

+----------+

|     4034 |

+----------+

| no | date                | domain_id | datadir_no | uid| id                  | real_id |crypt         | bak_crypt | name    | quota       | quota_s | quota_c | store | home                      | maildir                           | status| shad | spamnum |spam_lasttime       |

+----+---------------------+-----------+------------+-----+---------------------+---------+---------------+-----------+---------+-------------+---------+---------+-------+---------------------------+-----------------------------------+--------+------+---------+---------------------+

|  1 |2011-12-24 20:09:24 |         1 |          0 | 48 | deng123@cass.org.cn | NULL   | Z9LlXRH4rIgOM |           |deng123 | 1048576000S |    1000 |       0 |    0 | /mail/cass.org.cn/deng123 | /mail/cass.org.cn/deng123/Maildir |1      | 0   |    163 | 2012-01-07 17:08:06 |

userlog:用户日志

mysql> select * from userlog order by date desclimit 19;

+-----+-----------+-------+--------+---------------------+----------------------+-----------------+------+

| no  |domain_id | admin | action | date               | email                | ip              | cont |

+-----+-----------+-------+--------+---------------------+----------------------+-----------------+------+

| 266 |         0 |      |        | 2012-01-07 19:51:48 |xuj@cass.org.cn      | 114.248.92.168  |     |