| Line 1: |
Line 1: |
| | + | === Version === |
| | + | {{ #smeversion: smeserver-mailstats}} |
| | + | |
| | ==Mailstats== | | ==Mailstats== |
| | Brian Read's mailstats contrib analyzes your qpsmtpd log files and sends a periodic email to the address you specify summarizing your server's email activity. | | Brian Read's mailstats contrib analyzes your qpsmtpd log files and sends a periodic email to the address you specify summarizing your server's email activity. |
| Line 5: |
Line 8: |
| | | | |
| | Read the original announcement here: http://forums.contribs.org/index.php?topic=40651.0 | | Read the original announcement here: http://forums.contribs.org/index.php?topic=40651.0 |
| | + | |
| | + | {{Note box| For GeoIP reporting you may need updated rpms and GeoIP plugin - please see bugs below and the GeoIP wiki page https://wiki.contribs.org/GeoIP}} |
| | | | |
| | ==Installation== | | ==Installation== |
| Line 21: |
Line 26: |
| | config setprop mailstats Status ("enabled"|"disabled") | | config setprop mailstats Status ("enabled"|"disabled") |
| | ===Column Header=== | | ===Column Header=== |
| | + | (defaults to "auto") |
| | 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) | | 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") | | config setprop mailstats <column header> ("yes"|"no"|"auto") |
| | + | |
| | ===QpsmtpCodes=== | | ===QpsmtpCodes=== |
| | Show league table of qpsmtd result codes (default to "enabled") | | Show league table of qpsmtd result codes (default to "enabled") |
| Line 60: |
Line 67: |
| | Note: a number is interpreted as the hour of the day (0-23) | | Note: a number is interpreted as the hour of the day (0-23) |
| | | | |
| − | ==Logging to a MySQL database==
| + | ==Additional Notes== |
| − | 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:
| + | ===Handling @*.u Logfiles=== |
| − | <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>
| |
| − | | |
| − | ===Additional Notes===
| |
| − | ====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: | | 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.'' | | * ''.s: This file is completely processed and safely written to disk.'' |
| Line 199: |
Line 77: |
| | However in the early days of an installation, there will be only the "current" file, this leads to a nightly error message: | | However in the early days of an installation, there will be only the "current" file, this leads to a nightly error message: |
| | | | |
| − | ''Can't open /var/log/qpsmtpd/*.s: No such file or directory'' | + | ''Can't open /var/log/qpsmtpd/@*: No such file or directory'' |
| | | | |
| | This is only a minor issue as it 'solves' itself once logrotate creates the .u or .s files. | | This is only a minor issue as it 'solves' itself once logrotate creates the .u or .s files. |
| | + | |
| | + | You can eliminate this issue by forcing a qpsmtpd log rotation as described at http://wiki.contribs.org/Email_Statistics#Force_first_log_rotation |
| | + | |
| | + | ===Manual report generation=== |
| | + | |
| | + | You can use the following command to generate a report |
| | + | perl /usr/bin/mailstats.pl /var/log/qpsmtpd/@* /var/log/qpsmtpd/current |
| | + | |
| | + | == Bug Reporting & Tracking == |
| | + | Please raise bugs under the SME-Contribs section in {{BugzillaFileBug|product=|component=|title=bugzilla}}and select the '''smeserver-mailstats''' component or use this link: {{BugzillaFileBug|product=SME%20Contribs|component=smeserver-mailstats|title=smeserver-mailstats}} |
| | + | |
| | + | === smeserver-mailstats - Outstanding bugs === |
| | + | {{#bugzilla:columns=id,product,version,status,summary |sort=id|order=desc |component=smeserver-mailstats|noresultsmessage="No open bugs found."}} |
| | | | |
| | | | |
| Line 207: |
Line 98: |
| | [[Category:Contrib]] | | [[Category:Contrib]] |
| | [[Category:Mail]] | | [[Category:Mail]] |
| | + | [[Category:Administration:Monitoring]] |
