SpamStats - A Spam Statistics Reporting Package

[NightCoder]

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,

[falko]

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

[Ben]

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

[NightCoder]

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]

[till]

@NightCoder. The statistics looks great. I will try out the package in the next days!

[Ben]

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 o

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

[NightCoder]

Originally Posted by Ben

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.

[falko]

Originally Posted by Ben

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

Yes, it will.

[Ben]

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

[NightCoder]

Originally Posted by Ben

@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.

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).

Originally Posted by Ben

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

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;

$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)

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.