Spamassassin 2.50 & SQL logging

[Julian Field]

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