Spamassassin 2.50 & SQL logging

[Julian Field]

At 16:43 21/02/2003, you wrote:
>I share a similar desire to record important stats into a database.  I'm
>a DBA so it's a natural avenue for me!  I also consider myself a Perl
>beginner, but I've written a few scripts that do some pretty intense
>munging of CSV files, so perhaps I have something to contribute.
>If you would like to coordinate on this, I'd be happy to work with you.
>If you think it would be too close to the blind leading the blind, I
>understand that too.

I can give you a bit of help using a DB from Perl, I've got some good
sample code somewhere.

>On Fri, 2003-02-21 at 10:34, Steve Freegard 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.
> >
> > 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
> > **********************************************************************

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