| 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 62: |
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:
| |
| − | <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=== | | ===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: |
| Line 201: |
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=== | | ===Manual report generation=== |
| | | | |
| | You can use the following command to generate a report | | You can use the following command to generate a report |
| − | perl /usr/bin/spamfilter-stats-7.pl /var/log/qpsmtpd/@* /var/log/qpsmtpd/current | + | 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 214: |
Line 98: |
| | [[Category:Contrib]] | | [[Category:Contrib]] |
| | [[Category:Mail]] | | [[Category:Mail]] |
| | + | [[Category:Administration:Monitoring]] |
