Difference between revisions of "Mailstats"

From SME Server
Jump to navigation Jump to search
 
(27 intermediate revisions by 5 users not shown)
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 15: Line 20:
 
The configuration options for v 0.6.21 have been listed below.   
 
The configuration options for v 0.6.21 have been listed below.   
  
===Create the service===
+
===Create the report db entry===
  config set mailstats service
+
  config set mailstats report
 +
 
 
===Status===
 
===Status===
 
  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")
 
  config setprop mailstats QpsmtpdCodes ("enabled"|"disabled")
 
  config setprop mailstats QpsmtpdCodes ("enabled"|"disabled")
 
See this note for more information about the qpsmtpd LogLevel setting: [[Updating_to_SME_7.2#qpsmtpd_LogLevel]]
 
See this note for more information about the qpsmtpd LogLevel setting: [[Updating_to_SME_7.2#qpsmtpd_LogLevel]]
  
 
===SpamAssassin rules===
 
===SpamAssassin rules===
 +
Show league table of Spamassasin rules applied. (default to "enabled")
 
  config setprop mailstats SARules ("enabled"|"disabled")
 
  config setprop mailstats SARules ("enabled"|"disabled")
  
 
===JunkMailList===
 
===JunkMailList===
 +
Show listing of each user and number of junkmail emails left, in descending order of number (default to "enabled")
 
  config setprop mailstats JunkMailList  ("enabled"|"disabled")
 
  config setprop mailstats JunkMailList  ("enabled"|"disabled")
 +
 
===SpamAssassin Rule Percent Threshold===
 
===SpamAssassin Rule Percent Threshold===
 
Set a threshold for report cutoff
 
Set a threshold for report cutoff
Line 45: Line 57:
 
(default is "3306")'''
 
(default is "3306")'''
 
  config setprop mailstats DBPort <####>
 
  config setprop mailstats DBPort <####>
===Reporting Interval===
+
===Reporting Interval ===
 +
Length of time to report on (default is "day")
 
  config setprop mailstats Interval ("day"|"week"|"fortnight"|"month"|"#####"))
 
  config setprop mailstats Interval ("day"|"week"|"fortnight"|"month"|"#####"))
Note: a number is interpreted as seconds
+
Note: a number is interpreted as seconds.  Also note that lengthening the report interval does not change the interval summary at the moment, this means you will still get an hourly summary over whatever period you run the report.
 +
 
 
===Base===
 
===Base===
 +
What time to base the report (default is "Midnight")
 
  config setprop mailstats Base ("Midnight"|Midday"|"Now"|"##")
 
  config setprop mailstats Base ("Midnight"|Midday"|"Now"|"##")
 
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 191: 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 199: Line 98:
 
[[Category:Contrib]]
 
[[Category:Contrib]]
 
[[Category:Mail]]
 
[[Category:Mail]]
 +
[[Category:Administration:Monitoring]]

Latest revision as of 07:09, 14 April 2020

Version

Contrib 10:
Devel 9:
Contrib 9:
smeserver-mailstats
The latest version of smeserver-mailstats is available in the SME repository, click on the version number(s) for more information.


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.

The latest version also supports storing your mail server statistics in a MySQL database. You could potentially configure several servers to store their statistics in one central database in order to get consolidated statistics from several servers.

Read the original announcement here: http://forums.contribs.org/index.php?topic=40651.0


  Note:
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

yum install --enablerepo=smecontribs smeserver-mailstats

Configuration

If you install mailstats you should review the latest documentation, which is kept in the script itself. You can review the script after installation using:

less /usr/bin/spamfilter-stats-7.pl

The configuration options for v 0.6.21 have been listed below.

Create the report db entry

config set mailstats report

Status

config setprop mailstats Status ("enabled"|"disabled")

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)

config setprop mailstats <column header> ("yes"|"no"|"auto")

QpsmtpCodes

Show league table of qpsmtd result codes (default to "enabled")

config setprop mailstats QpsmtpdCodes ("enabled"|"disabled")

See this note for more information about the qpsmtpd LogLevel setting: Updating_to_SME_7.2#qpsmtpd_LogLevel

SpamAssassin rules

Show league table of Spamassasin rules applied. (default to "enabled")

config setprop mailstats SARules ("enabled"|"disabled")

JunkMailList

Show listing of each user and number of junkmail emails left, in descending order of number (default to "enabled")

config setprop mailstats JunkMailList  ("enabled"|"disabled")

SpamAssassin Rule Percent Threshold

Set a 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

Length of time to report on (default is "day")

config setprop mailstats Interval ("day"|"week"|"fortnight"|"month"|"#####"))

Note: a number is interpreted as seconds. Also note that lengthening the report interval does not change the interval summary at the moment, this means you will still get an hourly summary over whatever period you run the report.

Base

What time to base the report (default is "Midnight")

config setprop mailstats Base ("Midnight"|Midday"|"Now"|"##")

Note: a number is interpreted as the hour of the day (0-23)

Additional Notes

Handling @*.u Logfiles

Multilog occasionally generates log files that end in ".u" instead of ".s". The 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".

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/@*: No such file or directory

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 bugzilla and select the smeserver-mailstats component or use this link: smeserver-mailstats


smeserver-mailstats - Outstanding bugs

IDProductVersionStatusSummary (3 tasks)
11959SME Contribs10.0CONFIRMEDerror in messages log
11806SME Contribs10.0UNCONFIRMEDmailstats email arrived this morning but empty
10251SME ContribsFuturCONFIRMEDno more spamassassin tag stats