SQL logging

[Steve Freegard]

Julian,

Glad you like the idea, and fine about the 'Always Evaluated Last' config
option.

You're right - tab seperation would be a _lot_ easier to handle - if you
could do a quick example Custom function that I could use as a base it would
really help - can you include an example of how to explode out the
recepients/virus reports arrays as that would save me a huge amount of time
(reading through the 'Camel' book) so I can use it as a base and tweak it to
get the values I'd like and then get on with writing the schema for the
database.

Thanks again,
Steve.


-----Original Message-----
From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
Sent: 21 February 2003 17:29
To: MAILSCANNER at jiscmail.ac.uk
Subject: Re: SQL logging


Please note the Subject: change as we aren't talking about SA 2.50 any more!

At 17:25 21/02/2003, you wrote:
>Argh... This is turning into a big thread!!

But it's a very good idea!

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

I can't do one Custom Function call per batch, only 1 per message. But that
could easily keep the file open in between calls. You then have an hourly
job which "tail"s the file to read and store the hour's values in your db.

You can write your logging data into a file in any format you like. Given
CSV's full spec (quotes, embedded commas, embedded quotes, etc..) you might
find something like tab-separated easier to read automatically.

If people need some help getting started and can't help each other, I'll
write a skeleton Custom Function for you which logs a few parameters about
every message to a file.

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

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