Spamassassin 2.50 & SQL logging

[Steve Freegard]

Hi Sean,

I too am an Oracle DBA amongst other things - I would be happy to have
assistance from anyone who would like to do similar.  I was going to
primarily focus my efforts on MySQL to begin with as importing CSV data into
it is trivial from a LOAD DATA INFILE statement.

What ideally would you like to record/achieve?

I'll put together a document of what I would like to do over the weekend -
and we can decide how to split the work.  The first hurdle is getting the
CSV written out from MailScanner using the CustomConfig.pm

Regards,
Steve.

-----Original Message-----
From: Sean Closson [mailto:sean at DIGISILK.NET]
Sent: 21 February 2003 16:44
To: MAILSCANNER at jiscmail.ac.uk
Subject: Re: Spamassassin 2.50 & SQL logging


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.

Sean Closson
sean at digisilk.net
MCSE / MCDBA / GSEC / CISSP



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