1Email数据、帐号、邮件数据挖掘PostFix2012_01
来源:互联网 发布:数据库建模实例 编辑:程序博客网 时间:2024/06/05 03:14
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 | |
- 1Email数据、帐号、邮件数据挖掘PostFix2012_01
- 数据挖掘笔记1
- 《数据挖掘》总结1
- 数据挖掘-1-基础
- 数据挖掘工作指南(1)
- 文本数据挖掘--1
- 数据挖掘1
- 1 数据挖掘基础
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- 数据挖掘
- VCC、VDD和VSS
- win7开始菜单快捷命令
- GIS数据管理之“谈革命说民主”
- Linux下查看并发连接数
- 对replication slave,replication client的一点说明
- 1Email数据、帐号、邮件数据挖掘PostFix2012_01
- Oracle 判断字符串为''还是Null
- 一个研究生的职业规划
- js 拖动层效果[实例]
- douban
- 程序员的10种级别,你是那一级呢?
- 从rect到rgn的函数,圆角效果
- Tomcat实现动态context切换
- 世界前50名大型期刊及新动向