SpamStats - A Spam Statistics Reporting Package

Hello,



Briefly;

I have re-engineered a program that I wrote to report on Spam Statistics for my own personal server, over the past few days, so that it now works as a drop-in ISPConfig package for all ISPConfig users; i.e. Tools -> Reports -> Spam Statistics

However, the module depends on a small SpamAssassin plugin, and I'd like you to consider its inclusion in ISPConfig.



Details;

See the Project SpamStats page for the package file and all of the detailed details;
http://midnightcode.org/projects/spamstats/

SpamStats functions within ISPConfig by querying the ISPConfig database for all of the system accounts (email accounts) associated with the current ISPConfig user. It then pulls all of the tallies accrued by those users, for the given time period, from the spam.stats table in the database. This spam.stats table is populated by a third-party SpamAssassin plugin. The result is quite presentable, particularly if you have Flash installed in your browser.

The third-party SpamAssassin plugin is James Keating's StatsPlugin, you can get all of the details for his plugin here;
http://wiki.apache.org/spamassassin/StatsPlugin

If you download the SpamStats ISPConfig Package, and unzip it, you'll find a web/install/README.txt file that explains how I think the StatsPlugin software should be added to ISPConfig; less the credentials consideration (see below).


What I would like to discuss is the permanent inclusion of the StatsPlugin SpamAssassin code, into the base ISPConfig install. Ideally, it should create the MySQL "spamuser" account with a reasonably random password - and it should populate that within the SpamAssassin configuration, appropriately, at ISPConfig install.

The SpamStats module inherits the ISPConfig database username and password from the PHP session, so it is ultimately arbitrary what the username/password credentials are for the StatsPlugin - but they need to be otherwise unknown to the hosted user population, or the stats can be destroyed or altered by malicious users.


Are you willing/able to incorporate James' plugin into ISPConfig?


Thanks,

 

Thanks, I've added this to out To-Do list.

 

Great thing, I will test it.

@Falko / Till: will /home/admispconfig/ispconfig/tools/spamassassin/local.cf etc be overwritten with an update of ISPConfig?

@NightCoder: Maybe it's better to remove the linebrakes in 5,6,7 of the readme, some user's might just copy and paste them...

 

Good news

Thanks guys,


@falko; This is good news, thanks for your help!

@Ben; Good thought. With falko's info, though, I'll prolly just drop the web/install" directory from the next version of the package. In the version that's up, I didn't want users to do a "next-next-next" install, because I have no idea how many versions of Perl have been deployed by the various versions of ISPConfig, and this changes the directory names. If a user is keen to do a blind-faith install with the current package, then there is a shell script in that install directory that will run the README.txt instructions auto-magically. It works on the systems I built, but may not on others.

@all;
It dawned on me yesterday that you'd have to install the package and run it for a while before you could get an impression of what it was like and about - which seemed daft, because that's what would you need to know before you chose to install it. So I've snapped a couple of screen shots from the two existing installations - the pre-ISPConfig version which has plenty of data in it, and the ISPConfig version so that you can see how it integrates. These are in the "Screen Shots" section of the Project SpamStats site (http://midnightcode.org/projects/spamstats/). I've attached one ISPConfig integration screen-shot to this post, in a size that the Forum will accept, the external shots are not resized, just cropped.

[post-edited to fix stupid auto-URL detected link]

 

It dawned to me as well after wondering why the stats showing 0, the DB got written and a while of debugging your script

 

heh .. d'oh! ;-)

Hmm, while I think of it, since my first post I've also done some reading through the user forums (all of the new content since October or so), and it seems there are people running ISPConfig through SpamAssassin but without using the native ISPConfig piping-n-stuff (using spamc and spamd instead, I believe). If anyone is using a config that results in all mail being delivered by one user, like a daemon account or root, as opposed to the recipient user, like web2_user, then the stats will not count the user mail as you'd expect. I.e. all stats will be tallied under that one user - root, for example.

When I first wrote the stats progam it was for a server where all users were delivered via one user (the sendmail system account). Thus the stats on that system are representative of all users of that system (as a total). When I packaged it for ISPConfig, I inherited ISPConfig's per-user SpamAssassin delivery, which is what makes the account-based tally's possible. In a later version I'll try and find a way to make this a toggle in SpamStats.

If you (not you Ben, but anyone reading this in the future) have a tally issue, this is the most likely cause. To validate your host configuration, after a few test messages, dump/view the spam.stats table to see which user has been receiving tally increments.

Stock ISPConfig should work just dandy.

 

Yes, it will.

 

Good to know.

@nightcoder: No the stats / graphs are not working for me as well, due to the script associates one user to be the "counter" for all mails. I normally log in as admin, so that won't help.
What I meant with my post, was that I found out how your script's working (any why I did not see any graphs, even after chaning some lines) for me.

I think for a standard ISPConfig setup it might be better to change the way of calculating data by a user, e.g. an admin should be able to see a sum of all users etc....

 

Hmm. Okay, there are two parts here; are you saying -
1) the MySQL table spam.stats is only showing rows for one mail user for the entire system, or
2) the MySQL table spam.stats shows rows for many mail users but the ISPConfig user only sees statistics for an individual mail user


If we're talking a stock ISPConfig build, then I'll assume it's "2" for the rest of this post. Whether or not you receive counts for many users is dependant upon your SpamAssassin configuration which isn't part of my script (that's James' plugin, which is really just getting user info from the running SpamAssassin instance).

I don't seek out whether someone is an "admin" (for their ISPConfig account, as opposed to the entire ISPConfig system) or not. The effect should be right though, thanks to ss_ispconfig_user() (see libraries/db.lib.php) I.e. pseudo example;

Code:

$username = "Customerx";
$query_string = ss_ispconfig_user($username);
echo htmlspecialchars($query_string);

.. should turn "Customerx" into "username = 'webx_user1' OR username = 'webx_user2' OR username = 'webx_user3'"

You should be able to test the raw case in MySQL with the following (EDIT: This query is to be used against your ISPConfig database which defaults to "db_ispconfig", though the name can also be found in the ISPConfig session variable $go_info["server"]["db_name"]; if you're having trouble finding it)

Code:

SELECT isp_isp_user.user_username FROM isp_isp_user, isp_nodes, sys_user WHERE sys_user.username = 'Customerx' AND (isp_nodes.userid=sys_user.doc_id) AND isp_nodes.doctype_id = 1014 AND (isp_isp_user.doc_id = isp_nodes.doc_id);

The user "admin" isn't a parent to every email account, so the user "admin" will see diddly. But that query should be returning all of the child mail users for the parent ISPConfig user. Does this work for you? Or have I missed your configuration/issue? If I have, can you provide an anonymized example and walk me through it?

If there's something that I can change in my script to make life easier, I'll bang the code out.

 

Well it's 2.) that means every web_* user has a row.

So I will just try how it looks like with a normal user.

 

okay, thanks - lemme know how it goes. I'll have a think on that admin flag suggestion and see what can be done about making it automatically more intuitive.

 

I can confirm Ben's observation - when you're logged in as admin, you don't see any graphs, while it's working when you're logged in as a normal user.

 

To make it work for admin as well (showing the statistics for all users), you must modify the function ss_find_ispconfig_users() in web/libraries/db.lib.php as follows:

Code:

  function ss_find_ispconfig_users($username) {
    global $database_ispconfig;

    $users = array();
    ss_dbselect($database_ispconfig);
    [I][COLOR="Red"]if($username == 'admin'){
      $query = ss_dbquery("SELECT user_username FROM isp_isp_user");
    } else {[/COLOR][/I]
      $query = ss_dbquery("SELECT isp_isp_user.user_username " .
                     "FROM isp_isp_user, isp_nodes, sys_user " .
                     "WHERE sys_user.username = '%s' AND " .
                     "(isp_nodes.userid=sys_user.doc_id) AND " .
                     "isp_nodes.doctype_id = 1014 AND " .
                     "(isp_isp_user.doc_id = isp_nodes.doc_id)",
                     $username);
    [I][COLOR="Red"]}[/COLOR][/I]
    $result = ss_dbuse($query, "");
    while($user=mysql_fetch_array($result, MYSQL_ASSOC)) {
      if(!is_array($user))
        continue;
      $users[] = $user["user_username"];
    }
    mysql_free_result($result);

    return $users;
  }

@NightCoder: Maybe you can change this in your source code?

 

To correct you - no it will not. Or let's say in my case, after upgrading to 2.2.21 the file was completely unchanged (/home/admispconfig/ispconfig/tools/spamassassin/etc/mail/spamassassin/local.cf)

ok the path is a bit longer, but the above mentiones does not exist for me...

 

SpamStats Version 1.1

Indeed

Sorry for the delay, I have changed jobs and .. well .. the new company isn't up to speed yet, but they're getting there

I've posted a SpamStats version 1.1 package online. You can upgrade directly to this URL if you are so inclined;
http://midnightcode.org/projects/spamstats/code/spamstats-1.1.pkg

Version 1.1 includes two changes; Falko's "admin" change (above), and one more user-friendly fix - where days that users receive both no mail and no SPAM (I didn't realise such email accounts still existed) a "zero" value is inserted to ensure that the rendered chart is actually sensible to view.

That's it.


Whether you want to read on depends on whether you care about the difference between "all" users meaning all *system* users in your host or all *ISP account* users in your host. This is a special case consideration;

I.e. In my spam database, I have a "root" user logged daily, where cron and other system email messages are being forwarded and logged as non-spam en-route.

Falko's admin change wasn't actually what I was going to code up, but as Falko's was easier I thought I'd throw it together anyway. I was going to try to integrate a search for all system users, as opposed to all account holders. Thus, if I have an ISP with accounts user1, user2, user3, etc, then Falko's change makes the user "admin" pull spam statistics for user1+user2+user3+etc. That is what I have coded into version 1.1.

However, I intended to provide a match on the admin property of the user (so usernames that are not literally "admin" but which are "admin" users get to see total figures), and I did not intend to match against the ISPConfig accounts, instead passing a wild-card further down the query chain. This would then mean that statistics would not only be returned for user1+user2+user3, but also for root (as an example).


If there's any interest in me updating SpamStats to return stats for *all* accounts including those system accounts shuffling mail, then please post back here. If there's not, then the "admin" friendly version of the SpamStats package is now available, and should meet your needs.



Thanks for your help, Falko and all.

 

very nice NC, im gonna try this out

 

Thanks GJ.


For others who happen upon this thread prior to the Spam Assassin mods being shipped as defaults in ISPConfig, I have just provided some information to a private message in this forum system, to another user, that I'll reproduce here. The following is one method for getting access to the README.txt file that is inside of the Install Package;

1) cd /tmp
2) wget http://midnightcode.org/projects/spamstats/code/spamstats-1.1.pkg
3) unzip spamstats-1.1.pkg
4) more web/install/README.txt

Hope this helps others,

 

After reinstalling on a new Server I have some problems to get spamstats running.

MySQL Database is not populating - any ideas how i can check if there are any errors when trying to insert new rows in the database?

 

Testing your setup

Hello Feanwulf,


Sounds problematic .. a quick search on my hosts and I can't see where these errors get logged. So, a few diagnostics for you in case there's something you haven't thought of;

1. Make sure your SpamAssassin is configured for Statistics;

2. Make sure you can use the spam statistics database as that user;

3. Make sure you can Insert a record;

4. Make sure you can Update a record;

So, if 1 fails to show useful info, then your SpamAssassin is not configured for Statistics logging (see the README.txt for the installation details, and the supplied local.cf for the SpamAssassin config changes).

Tests 2, 3 and 4 all have the same rough remedy; If 2 fails to let you connect, then you either have no MySql user or the passwords don't match or that Database doesn't exist or your user doesn't have rights to use the Database. If 3 fails to let you insert a record, then your user doesn't have rights to "insert" on that Database. And if 4 fails to let you update a record, then your user doesn't have rights to "update" on that Database. In any of these events, see the README.txt for the installation details, and the supplied create.sql for the MySql config changes.

If all of the tests are working, then verify that your users are configured for Spam filtering, and that SpamAssassin is detecting Spam on your system.


Lemme know if that doesn't solve your problem ...


The only other issue that comes to my mind is that "Stats.pm" may be missing, or installed in the wrong Perl directory;

Let me know how you go,