...
To begin using the Stats Plugin you will need to create a table for the plugin to write to. Here is the necessary schema: BR
Warning: The code below is only compatible with MySQL 4.1+! (Just like most of the SQL code for SpamAssassin 3.1) BRunmigrated-wiki-markup
If you are using MySQL 4.0 and below you can download the old plugin, which contains a possible race condition \[http://www.okeating.net/spamassassin/Stats.pm-4.0.x here\].
| No Format |
|---|
CREATE TABLE stats ( day date NOT NULL default'', username varchar(100) NOT NULL default '', domain varchar(100) default '', spam int(20) default '0', ham int(20) default '0', PRIMARY KEY (day,username,domain) ) TYPE=MyISAM |
Once the table has been created you will need to add the following options into your local.cf or other configuration file: BR
- You will need to replace the following variables with settings for your configuration* BR
| No Format |
|---|
# Configure SQL for statistical storage use_stats 1 user_stats_dsn DBI:mysql:spamassassin_beta:sql_hostname user_stats_sql_username sql_username user_stats_sql_password sql_password user_stats_sql_table sql_table |
...
| No Format |
|---|
# Stats Plugin - store stats in a MySQL DB # loadplugin Mail::SpamAssassin::Plugin::Stats |
...
Finally, we need to put the plugin inside of the [SpamAssassin] plugin directory. You can copy the code below, but I would recommend downloading it from my \[http://www.okeating.net/blog/2006/01/spamassassin-stats-plugin-upda-1.html site\].
| No Format |
|---|
=head1 NAME
package Mail::SpamAssassin::Plugin::Stats - Keep Real Time SpamAssassin Stastics
=head1 SYNOPSIS
loadplugin Mail::SpamAssassin::Plugin::Stats
use_stats 1
user_stats_dsn DBI:mysql:spamassassin_beta:sql_hostname
user_stats_sql_username sql_user
user_stats_sql_password sql_pass
user_stats_sql_table stats
=head1 DESCRIPTION
This SpamAssassin plugin records real-time, user-level statistics. The statistics are stored inside of a MySQL database and are rotated daily. Each entry inside the table contains the current date, user, domain, number of hams (legitimate mail) and the number of spams (unsolicited mail).
=cut
package Mail::SpamAssassin::Plugin::Stats;
use strict;
use warnings;
use bytes;
use Mail::SpamAssassin;
use Mail::SpamAssassin::Logger;
use vars qw(@ISA);
@ISA = qw(Mail::SpamAssassin::Plugin);
sub new {
my ($class, $mailsa) = @_;
$class = ref($class) || $class;
my $self = $class->SUPER::new($mailsa);
bless ($self, $class);
$self->set_config($mailsa->{conf});
$self;
}
sub set_config {
my ($self, $conf) = @_;
my @cmds = ();
push (@cmds, {
setting => 'use_stats',
default => 1,
type => $Mail::SpamAssassin::Conf::CONF_TYPE_NUMERIC,
});
push (@cmds, {
setting => 'user_stats_dsn',
type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
});
push (@cmds, {
setting => 'user_stats_sql_username',
type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
});
push (@cmds, {
setting => 'user_stats_sql_password',
type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
});
push (@cmds, {
setting => 'user_stats_sql_table',
type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
});
$conf->{parser}->register_commands(\@cmds);
}
sub check_end {
my ($self, $params) = @_;
my $pms = $params->{permsgstatus};
return 0 unless ($pms->{conf}->{use_stats});
dbg("stats: Executing stats-plugin");
my $dsn = $self->{main}->{conf}->{user_stats_dsn};
if (!defined($dsn)) {
dbg("stats: no DSN specified; HALT!");
return 1;
}
require DBI;
my $main = $self->{main};
my $dbuser = $main->{conf}->{user_stats_sql_username};
my $dbpass = $main->{conf}->{user_stats_sql_password};
my $table = $main->{conf}->{user_stats_sql_table};
my $f_spam = 'spam';
my $f_ham = 'ham';
my $f_username = 'username';
my $f_domain = 'domain';
my $f_day = 'day';
my $isspam;
my $user;
my $domain;
my $username = $self->{main}->{username};
$username = lc($username);
my $score = $pms->{score};
my $required_score = $main->{conf}->{required_score};
dbg("stats: Splitting $username based on @");
($user,$domain) = split /@/,$username;
if (!defined($domain)) {
$domain = '';
}
dbg("stats: User: $user Domain: $domain");
dbg("stats: Message Score: $score out of $required_score");
if ($score >= $required_score ) {
$isspam = 1;
}
else {
$isspam = 0;
}
dbg("stats: IsSpam is $isspam");
my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
if ($dbh) {
&execute_stats($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_username, $f_domain, $f_day, $isspam);
$dbh->disconnect();
}
else {
die "stats: SQL error: " . DBI->errstr . "\n";
}
0;
}
sub execute_stats {
my ($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_username, $f_domain, $f_day, $isspam) = @_;
my $column = ($isspam) ? $f_spam : $f_ham;
my $sql = "INSERT into $table set $f_day = curdate(), $f_username='$user', $column=1";
$sql .= ", $f_domain='$domain'" if $domain;
$sql .= " ON DUPLICATE KEY UPDATE $column = $column + 1";
dbg("stats: config: SQL executing $sql");
my $rv = $dbh->do($sql);
if ($rv) {
dbg("stats: Updated $column for $user $domain");
}
else {
die "stats: SQL error: " . $dbh->errstr . "\n";
}
}
1;
|
...