...
$day $username $domain $ham $spam $total
Each entry is rotated daily, meaning when the day changes a new entry is placed into the table with the new date and the following fields are set to zero: ham, spam, total. The table also contains an entry which retains totals for the system for each day. This entry has the username and domain replaced by the string "$TOTALS".
To Begin Using
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: This has only been used The code below is only compatible with MySQL 4.0.24! BR1+! (Just like most of the SQL code for SpamAssassin 3.1)
If you are using MySQL 4.0 and below you can download the old plugin, which contains a possible race condition here.
No Format |
---|
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',
total 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 you 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 |
...
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 site - http://www.okeating.net/spamassassin/Stats.pm:.
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). and total number of messages for that individual. There is also a total field which also rotates daily. It contains the total number of hams, spams, and messages that the system has seen for that day. This entry is notated with the $TOTALS username and $TOTALS domain name. =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_total = 'total'; my $f_username = 'username'; my $f_domain = 'domain'; my $f_day = 'day'; my $isspam; my $user; my $domain; my $tot_user = '$TOTALS'; my $tot_domain = '$TOTALS'; 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}); my $user_exists = &get_current_entry($user, $domain, $dbh, $table, $f_username, $f_domain, $f_day); my $total_exists = &get_current_entry($tot_user, $tot_domain, $dbh, $table, $f_username, $f_domain, $f_day); if ($dbh) { &execute_stats($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $user_exists); &execute_stats($tot_user, $tot_domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $total_exists); $dbh->disconnect(); } else { die "stats: SQL error: " . DBI->errstr . "\n"; } 0; } sub get_current_entry { my ($user, $domain, $dbh, $table, $f_username, $f_domain, $f_day) = @_; my $sql = "SELECT $f_username from $table where $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()"; dbg("stats: Executing $sql"); my $sth = $dbh->prepare($sql); if ($sth) { my $rv = $sth->execute(); dbg("stats: rv contains $rv"); if ($rv eq "0E0") { dbg("stats: Entry does not exist for $user\@$domain"); return 0; } else { dbg("stats: Entry already exists for $user\@$domain"); return 1; } } else { die "stats: SQL error: " . $dbh->errstr . "\n"; } } sub execute_stats { my ($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $exists) = @_; my $sql; if ($exists) { $sql = "UPDATE $table SET $f_total = $f_total + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()"; } else =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, { $sqlsetting = "INSERT into $table ($f_day,$f_username,$f_domain,$f_spam,$f_ham,$f_total) VALUES (curdate(),'$user','$domain',0,0,1)"; } dbg("stats: config: SQL executing $sql"); my $sth = $dbh->prepare($sql> 'use_stats', default => 1, type => $Mail::SpamAssassin::Conf::CONF_TYPE_NUMERIC, }); ifpush ($sth)@cmds, { my $rvsetting = $sth->execute(); > 'user_stats_dsn', type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING, }); ifpush ($rv)@cmds, { setting => 'user_stats_sql_username', dbg("stats: Stats change for $user\@$domain"); if ($isspam)type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING, }); push (@cmds, { setting => 'user_stats_sql_password', type $sql = "UPDATE $table SET $f_spam = $f_spam + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()";=> $Mail::SpamAssassin::Conf::CONF_TYPE_STRING, }); push (@cmds, { setting => 'user_stats_sql_table', type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING, } else }); $conf->{parser}->register_commands(\@cmds); } sub check_end { my ($self, $params) = @_; my $pms = $params->{permsgstatus}; return 0 $sql = "UPDATE $table SET $f_ham = $f_ham + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()"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 $sth$dbuser = $dbh->prepare($sql)$main->{conf}->{user_stats_sql_username}; my $dbpass if ($sth) { $rv = $sth->execute(); if ($rv) { = $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: UpdatedUser: Spam$user TypeDomain: $sql$domain"); dbg("stats: Message Score: }$score out of $required_score"); if ($score >= $required_score ) { $isspam = 1; } else { $isspam else= {0; } dbg("stats: IsSpam is $isspam"); my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0}); if ($dbh) { die "stats: SQL error $sql\n".$sth->errstr."\n"; &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 } else { die "stats: SQL error $sql\n".$sth->errstr."\n"; } } else { . "\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) { die dbg("stats: Updated SQL$column for error$user $sql\n".$sth->errstr."\n"; }$domain"); } else { die "stats: SQL error: " . $dbh->errstr . "\n"; } } 1; |
Contact
Use this plugin at your own risk. I cannot gurantee guarantee it will not cause you issues!
...