Differences between revisions 16 and 17
Revision 16 as of 2007-07-08 20:25:13
Size: 6594
Editor: JamesKeating
Comment:
Revision 17 as of 2009-09-20 23:16:26
Size: 6598
Editor: localhost
Comment: converted to 1.6 markup
Deletions are marked like this. Additions are marked like this.
Line 13: Line 13:
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) [[BR]]'''

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].
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) <<BR>>'''

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]].
Line 30: Line 30:
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]]
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>>
Line 52: Line 52:
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]. 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]].

Stats Plugin

NOTE: This module requires SpamAssassin 3.1+

Stats Plugin provides real-time statistics genereated by SpamAssassin. Statistics are stored inside of a MySQL table (defined as stats) which contains the following information:

$day $username $domain $ham $spam

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.

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:

Warning: The code below is only compatible with MySQL 4.1+! (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.

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:

You will need to replace the following variables with settings for your configuration

# 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

Then the plugin will need to be added to your init.pre or other .pre file:

# 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 site.

=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;

Contact

Use this plugin at your own risk. I cannot guarantee it will not cause you issues!

If you have any problems with this plugin, suggestions, or would like to submit modifications to it please contact me at:

jamesk -at- okeating -dot- net

StatsPlugin (last edited 2009-09-20 23:16:26 by localhost)