Spamassassin 2.50 & SQL logging

[Steve Freegard]

Hi Julian,

Argh... This is turning into a big thread!!

The 'blank' config option that could pick up _all_ the $message->{*}
variables, would be the best - as the call to the file only has to happen
once, won't need munging together, and can easily be imported to a database
if it's comma-seperated.

Thanks for the comments, and tips on how I need to do this.

Regards,
Steve.

-----Original Message-----
From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
Sent: 21 February 2003 16:52
To: MAILSCANNER at JISCMAIL.AC.UK
Subject: Re: Spamassassin 2.50 & SQL logging


At 16:34 21/02/2003, you wrote:
>Hi Julian,
>
>I like your idea about writing the data to a text file and then
periodically
>reading it into MySQL as this would do exactly what I want and never runs
>the risk of the database being unavailable - it would also be useful as one
>could do a 'tail -f' on the file and watch the traffic going through.
>
>What would be the best configuration option to use to do this? - ideally
I'd
>like to be able to record date, time, id, size, from, to, subject, sascore,
>spamwhitelisted, isspam, issaspam, isrblspam, ishigh, spamreport plus the
>virus and other reports.

For spam you probably want to catch the "Spam Actions" and "High Scoring
Spam Actions" keywords.
For viruses then you could use "Deliver Silent viruses".

Ideally I guess I could add a configuration value that effectively did
nothing but got evaluated for every message right at the end of the loop.
Would that be worth doing? (and what could I call the conf file parameter?)

>I'm pretty much a Perl beginner but don't mind getting my hands dirty - I
>take it that I would open the file handle in the Init sub, write to it in
>the 'main' sub and close in the End sub??

That's right.

>  - my only other question would be
>how do you reference the filehandles and variable between the subroutines
as
>my OO experience with Perl is 0... I should be able to work out the rest.

Just declare variables outside of any function using "my".

>I'm thinking of putting this together and posting it for anyone else that
>wants to do similar as me for graphing daily reports, showing top users,
top
>mail size per user, average spam score etc. that can be run as
>daily/monthly/weekly/yearly reports from the database.

I think that would be much appreciated. It might even find its way into the
distribution...

>-----Original Message-----
>From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
>Sent: 21 February 2003 16:00
>To: MAILSCANNER at jiscmail.ac.uk
>Subject: Re: Spamassassin 2.50 & SQL logging
>
>
>At 15:41 21/02/2003, you wrote:
> >On Fri, 2003-02-21 at 14:33, Julian Field wrote:
> > > At 13:06 21/02/2003, you wrote:
> > > >Also - I was wondering if anyone had tried getting MailScanner to log
>to a
> > > >MySQL database?? - I was looking at the CustomConfig.pm and was
>thinking
> > > >that I could create a custom function that would connect to the
> > database and
> > > >do something like 'INSERT INTO maillog VALUES
> > > >('$message->{id}','$message->{size}','$message->{from}' etc..)' - or
> > is this
> > > >just a really bad idea??  The existing software we use does this into
>an
> > > >Access database but we don't use it because it causes too much
> > overhead, but
> > > >I thought Perl's DBI/DBD and MySQL would probably be much more
>efficient
> > > >than Access/ODBC!
> > >
> > > You are still talking a database "insert" for every batch of messages.
> > > That's going to carry a fair sized overhead. How about writing them to
a
> > > file and then periodically pushing the file data into a database?
> >
> >How reasonable would it be to just insert entries when a virus or spam
> >is found?
>
>Again, it all depends how fast your database is. You could keep the db
>connection open permanently, so it *should* be pretty quick. You would have
>to hook it into one of the configuration parameters that gets used fairly
>late on in the batch processing, and use a Custom Function for that
>parameter that had the side-effect of logging all sorts of things about
>messages.
>--
>Julian Field
>www.MailScanner.info
>MailScanner thanks transtec Computers for their support
>
>
>**********************************************************************
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom they
>are addressed. If you have received this email in error please notify
>the system manager.
>
>This footnote also confirms that this email message has been swept by
>MIMEsweeper for the presence of computer viruses.
>
>www.lbsltd.co.uk
>**********************************************************************

--
Julian Field
www.MailScanner.info
MailScanner thanks transtec Computers for their support


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.lbsltd.co.uk
**********************************************************************