| Line 63: |
Line 63: |
| | | | |
| | ==Logging to a MySQL database== | | ==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: | + | In order to log your data to a MySQL database, you will need to create a database named 'mailstats'. |
| − | <nowiki>#############################################################################
| + | |
| − | #
| + | There is a script to create the mailstats database held in Brian Read's contrib area, courtesy of Hartmut Trepkau. |
| − | # Table structure for MySQL table for saving data
| + | |
| − | #
| + | http://mirror.contribs.org/smeserver/contribs//bread/mailstats/sql%20create/ |
| − | # Database : `mailstats`
| + | |
| − | #
| + | You can use this to create the database and tables, and it will also set the configuration entry to enable the saving to the database. |
| − | # --------------------------------------------------------
| + | |
| − | #
| + | Note that the create-mysql.sh script will delete any previous database (and the associated data) of the name "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== | | ==Additional Notes== |
