Proposed Mysql logging

[Gerard Earley <ge...@whitecurve.com>]

I am debating building a logging tool to record the spamassassin scan
results to a MySQL DB.

This would allow Admins to build get a more graphical view of how their
Spamassassin is being used.
PLEASE NOTE: This is NOT a proposal to replace the standard debugging
but rather to record already produced information in a new form.

The DB structure I'm planning would be something like :

Example mail:
2005-10-03 13:43:22 [21988] I: processing message
<43...@whitecurve.com> for user@domainname.com:513.
2005-10-03 13:43:25 [21988] I: result: . -5 -
ALL_TRUSTED,BAYES_00,HTML_MESSAGE,HTML_TITLE_EMPTY
scantime=2.8,size=15282,mid=<43...@domainname.com>,bayes=4.00800836963811e-10,autolearn=ham


MESSAGE (to hold the details of each message scanned)
Column name 	Data type 	Description 	 From example
ID 	int 	A unique ID. 	123
timescanned 	datetype YYYY-MM-DD HH:MM:SS 	Time the scan was performed
2005-10-03 13:43:25
pid 	int 	Process IP of the spamassassin scanning process 	21988
score 	int(3,2) 	  	-5.9
threshold 	int(2,2) 	  	5.0
scantime 	int(2,2) 	Time taken to scan the message 	2.8
size 	int 	Size of scanned message 	15282
mid 	varchar(255) 	The unique message ID of the message
<43...@domain.com>
bayes 	varchar(255) 	Bayes string returned for the message.
4.00800836963811e-10
autolearn 	char(4) 	whether the mail is a ham or spam 	ham


PASSEDRULE (Details on every rule passed by a message)
Column name 	Data type 	Description 	 
ID 	int 	A unique ID. 	 
message_ID 	int 	A foreign key which is the ID of the message. 	 
rule 	varchar(50) 	The name of the test passed. 	 


RULES (Description on each rule)
Column name 	Data type 	Description 	 
ID 	int 	the unique ID for each test passed. 	 
passedrule_ID 	varchar(50)
	A foreign key which is the ID of the passedrule table. 	 
describe 	varchar(50) 	Description of the test
	 
type
	varchar(6)
	What the rule checks "body", "header", "uri", "meta" or "rawbody"
	
score
	int(2,2)
	The score for a rule
	
rulefile
	varchar(50)
	The file containing the rule
	
rule
	varchar(255)
	the text of the actual rule
	



There are three ways that I can see to do this.

   1. Plugin which dumps the info from directly within SA itself.
   2. A script ala sa-learn which parses an existing log file.
   3. A logger perl module like File.pm, Stderr.pm or Syslog.pm.


So what I'm wondering now is

    * Has this been done before?
    * Which of the the above solutions would:
          o be the least expensive in system resources (CPU, I/O)
          o be the easiest to implement (require the least hacking of
            existing SA files)
          o be able to have access to the needed information (as in
            plugins seems only to be about the scanning process, not the
            reporting of results and may not easily have access to the
            needed data)

Many thanks.


Regards
    Gerard Earley
    Technical Director
    Whitecurve.com <http://www.whitecurve.com>
    Internet Design Development

PS) Our email security-cert was renewed on 1/8/2005 and only emails
containing this serial number "0F:3A:F0" should be considered valid.

Re: Proposed Mysql logging

[Michael Parker <pa...@pobox.com>]

Gerard Earley wrote:

>I am debating building a logging tool to record the spamassassin scan
>results to a MySQL DB.
>
>
>  
>
I've had this on my TODO list for awhile, glad to see someone looking at it.

>   1. Plugin which dumps the info from directly within SA itself.
>  
>
Do this.  There may not be a plugin hook at the appropriate place.  If
not, then feel free to add one and supply a patch, I'll seriously
consider it.

Then the implementation details don't really matter that much.

Michael