1Email数据、帐号、邮件数据挖掘PostFix2012_01
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 emailorder 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 emailorder 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 mfromorder 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| |
页:
[1]