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:
| + | ===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 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. |
| | | |
− | ====Manual report generation====
| + | 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 | | 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]] |