Spamassassin 2.50 & SQL logging

[Steve Freegard]

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.

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

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.

Thanks,
Steve.

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