|
|
| Line 23: |
Line 23: |
| | Brian Read's mailstats.pl script analyzes your qpsmtpd log files and sends a daily email to "admin" summarizing that day's email activity. | | Brian Read's mailstats.pl script analyzes your qpsmtpd log files and sends a daily email to "admin" summarizing that day's email activity. |
| | | | |
| − | =====Download & Installation=====
| + | Full details can be found at [[mailstats]] |
| − | yum install --enablerepo=smecontribs smeserver-mailstats
| |
| − | | |
| − | =====Configuration=====
| |
| − | The latest release (v0.6.20) supports several SME database options:
| |
| − | * Create the service
| |
| − | config set mailstats service
| |
| − | * '''Status'''
| |
| − | config setprop mailstats Status ("enabled"|"disabled")
| |
| − | * '''Column Header''' entries are created and set to "yes" if a non zero count is detected (they may not exist before). "Yes" means that that column is then always shown. If you delete the entry or set it to "auto" then it will only show if a non zero occurs again (and then get set to "yes").(enable, supress or only show if nonzero)
| |
| − | config setprop mailstats <column header> ("yes"|"no"|"auto")
| |
| − | * '''QpsmtpCodes'''
| |
| − | config setprop mailstats QpsmtpdCodes ("enabled"|"disabled")
| |
| − | * '''SpamAssassin Rules'''
| |
| − | config setprop mailstats SARules ("enabled"|"disabled")
| |
| − | * '''JunkMailList'''
| |
| − | config setprop mailstats JunkMailList ("enabled"|"disabled")
| |
| − | * '''SpamAssassin Rule Percent Threshold''' for report cutoff
| |
| − | config setprop mailstats SARulePercentThreshold (0.5)
| |
| − | * '''Email to send report'''
| |
| − | config setprop mailstats Email (admin) - email to send report
| |
| − | * '''Save data to MySQL database (default is "no")
| |
| − | config setprop mailstats SaveDataToMySQL ("yes"|"no")
| |
| − | * '''MySQL server hostname (default is "localhost"'''
| |
| − | config setprop mailstats server <hostname>
| |
| − | * '''MySQL server port (default is "3306")'''
| |
| − | config setprop mailstats DBPort <####>
| |
| − | * '''Reporting Interval'''
| |
| − | config setprop mailstats Interval ("day"|"week"|"fortnight"|"month"|"#####"))
| |
| − | Note: a number is interpreted as seconds
| |
| − | * '''Base'''
| |
| − | config setprop mailstats Base ("Midnight"|Midday"|"Now"|"##")
| |
| − | Note: a number is interpreted as the hour of the day (0-23)
| |
| − | | |
| − | In order to log your data to a MySQL database, you will need to create a database named 'mailstats'. Here are the notes from spamfilter-stats-7.pl on what is required:
| |
| − | <nowiki>#############################################################################
| |
| − | #
| |
| − | # Table structure for MySQL table for saving data
| |
| − | #
| |
| − | # Database : `mailstats`
| |
| − | #
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `ColumnStats`
| |
| − | #
| |
| − | #
| |
| − | #CREATE TABLE `ColumnStats` (
| |
| − | # `ColumnStatsid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `timeid` int(11) NOT NULL default '0',
| |
| − | # `descr` varchar(20) NOT NULL default '',
| |
| − | # `count` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`ColumnStatsid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `JunkMailStats`
| |
| − | #
| |
| − | #CREATE TABLE `JunkMailStats` (
| |
| − | # `JunkMailstatsid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `user` varchar(12) NOT NULL default '',
| |
| − | # `count` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) default NULL,
| |
| − | # PRIMARY KEY (`JunkMailstatsid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | #
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `SARules`
| |
| − | #
| |
| − | #CREATE TABLE `SARules` (
| |
| − | # `SARulesid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `rule` varchar(50) NOT NULL default '',
| |
| − | # `count` bigint(20) NOT NULL default '0',
| |
| − | # `totalhits` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`SARulesid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `SAscores`
| |
| − | #
| |
| − | #CREATE TABLE `SAscores` (
| |
| − | # `SAscoresid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `acceptedcount` bigint(20) NOT NULL default '0',
| |
| − | # `rejectedcount` bigint(20) NOT NULL default '0',
| |
| − | # `hamcount` bigint(20) NOT NULL default '0',
| |
| − | # `acceptedscore` decimal(20,2) NOT NULL default '0.00',
| |
| − | # `rejectedscore` decimal(20,2) NOT NULL default '0.00',
| |
| − | # `hamscore` decimal(20,2) NOT NULL default '0.00',
| |
| − | # `totalsmtp` bigint(20) NOT NULL default '0',
| |
| − | # `totalrecip` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`SAscoresid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `VirusStats`
| |
| − | #
| |
| − | #CREATE TABLE `VirusStats` (
| |
| − | # `VirusStatsid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `descr` varchar(40) NOT NULL default '',
| |
| − | # `count` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`VirusStatsid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | #
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `date`
| |
| − | #
| |
| − | #CREATE TABLE `date` (
| |
| − | # `dateid` int(11) NOT NULL auto_increment,
| |
| − | # `date` date NOT NULL default '0000-00-00',
| |
| − | # PRIMARY KEY (`dateid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | #
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `domains`
| |
| − | #
| |
| − | #CREATE TABLE `domains` (
| |
| − | # `domainsid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `domain` varchar(40) NOT NULL default '',
| |
| − | # `type` varchar(10) NOT NULL default '',
| |
| − | # `total` bigint(20) NOT NULL default '0',
| |
| − | # `denied` bigint(20) NOT NULL default '0',
| |
| − | # `xfererr` bigint(20) NOT NULL default '0',
| |
| − | # `accept` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`domainsid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `qpsmtpdcodes`
| |
| − | #
| |
| − | #CREATE TABLE `qpsmtpdcodes` (
| |
| − | # `qpsmtpdcodesid` int(11) NOT NULL auto_increment,
| |
| − | # `dateid` int(11) NOT NULL default '0',
| |
| − | # `reason` varchar(40) NOT NULL default '',
| |
| − | # `count` bigint(20) NOT NULL default '0',
| |
| − | # `servername` varchar(30) NOT NULL default '',
| |
| − | # PRIMARY KEY (`qpsmtpdcodesid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | # --------------------------------------------------------
| |
| − | #
| |
| − | # Table structure for table `time`
| |
| − | #
| |
| − | #CREATE TABLE `time` (
| |
| − | # `timeid` int(11) NOT NULL auto_increment,
| |
| − | # `time` time NOT NULL default '00:00:00',
| |
| − | # PRIMARY KEY (`timeid`)
| |
| − | #) ENGINE=MyISAM DEFAULT CHARSET=latin1;
| |
| − | #
| |
| − | #############################################################################</nowiki>
| |
| − | | |
| − | =====Handling @*.u Logfiles=====
| |
| − | Multilog occasionally generates log files that end in ".u" instead of ".s". The [http://cr.yp.to/daemontools/multilog.html multilog manual] says this:
| |
| − | * ''.s: This file is completely processed and safely written to disk.''
| |
| − | * ''.u: This file was being created at the moment of an outage. It may have been truncated. It has not been processed.''
| |
| − | | |
| − | The latest version of mailstats.cron will process "@*" and "current", so will catch all log files. Earlier versions only processed "*.s" and "current".
| |
| | | | |
| | ====qplogtail==== | | ====qplogtail==== |
Email Statistics
How to get statistics about the performance of your mail server
Built-In Statistical Reports
Server-Manager
Some email statistics can be seen from server-manager under "Administration", "Mail log file analysis"
qplogsumm.pl
SME 7.2 and later include the 'logterse' plugin to qpsmtpd as well as the 'qplogsumm.pl' statistics script.
qplogsumm.pl updates /var/log/qpsmtpd/state with per-plugin statistics for any qpsmtpd plugin that appears in a qpsmtpd logterse entry each time the qpsmtpd log is rotated.
Sample output here
e-smith-viewlogfiles-1.8.0-4 (released Nov 28 2007) or later will allow you to 'View' /var/log/qpsmtpd/state from 'View log files' in the server-manager (earlier versions conceal all files named "state" - Bug 3416).
Currently, qplogsumm.pl is disabled by default. Enable it with
config setprop qpsmtpd qplogsumm enabled
signal-event email-update
Contribs & Addons
Brian Read's spamfilter-stats-7.pl
Brian Read's mailstats.pl script analyzes your qpsmtpd log files and sends a daily email to "admin" summarizing that day's email activity.
Full details can be found at mailstats
qplogtail
qplogtail is a script intended to help monitor /var/log/qpsmtpd/current and extract a concise but meaningful display of what the server is up to.
qplogtail extracts 5 kinds of information:
- Normal connections:
28545 Accepted connection 4/30 from 86.139.2.73 ...
- Errors in violation of Instances:
5146 Too many connections: 40 >= 40. Waiting one second.
- Errors in violation of InstancesPerIP:
5320 hosts_allow plugin: Too many connections from 212.100.229.201: 6 > 5Denying connection.
- Messages blocked by any qpsmtpd plugin:
15751 logging::logterse plugin: ` 82.210.181.241 241-pra-6.acn.waw.pl 241-pra-6.acn.waw.pl <Glasteinzhza@ask-it-here.com> dnsbl 903 http://www.spamhaus.org/query/bl?ip=82.210.181.241 msg denied before queued
- Messages queued for delivery:
15587 logging::logterse plugin: ` 128.220.32.40 miami.deuvis.com miami.deuvis.com <aapple@deuvis.com> <c.wolf@ncxr.org> queued <200709270344.l8R3iq0b010299@deuvis.com> No, hits=-2.6 required=5.0_
Each smtp transaction will generate two lines of output containing:
msgid remote_ip x/40
msgid remote_ip dispostion details
Sample output:
# qplogtail
14185 213.37.31.24 dnsbl msg denied before queued
14262 64.233.184.243 1/40
14262 64.233.184.243 queued No, hits=-101.1 required=5.0_
14320 71.8.114.81 1/40
14320 71.8.114.81 dnsbl msg denied before queued
14349 200.63.233.145 1/40
14349 200.63.233.145 dnsbl msg denied before queued
To install:
cd /usr/local/bin
wget -O qplogtail http://bugs.contribs.org/attachment.cgi?id=1379
chmod 755 qplogtail
To run:
qplogtail
Direct comments or questions to Bugzilla:3418
