687
edits
Changes
From SME Server
Jump to navigationJump to searchCreate separate document for 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.
===Download & Installation===
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====
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====
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====
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)
===Logging to a MySQL database===
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====
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".
----
[[Category:Contrib]]
[[Category:Mail]]
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.
===Download & Installation===
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====
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====
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====
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)
===Logging to a MySQL database===
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====
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".
----
[[Category:Contrib]]
[[Category:Mail]]
