SQL logging

[Julian Field]

Here are the patches to make the "Always Looked Up Last" option work, so
you can set it to a Custom Function to do all your logging.

Sample code for the Custom Function itself will follow later, when I've had
a chance to write some.

At 17:48 21/02/2003, you wrote:
>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
>**********************************************************************
-------------- next part --------------
A non-text attachment was scrubbed...
Name: MessageBatch.pm.patch
Type: application/octet-stream
Size: 625 bytes
Desc: not available
Url : http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030221/aff562f7/MessageBatch.pm.obj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ConfigDefs.pl.patch
Type: application/octet-stream
Size: 664 bytes
Desc: not available
Url : http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030221/aff562f7/ConfigDefs.pl.obj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: MailScanner.conf.patch
Type: application/octet-stream
Size: 902 bytes
Desc: not available
Url : http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030221/aff562f7/MailScanner.conf.obj
-------------- next part --------------
A non-text attachment was scrubbed...
Name: MailScanner.patch
Type: application/octet-stream
Size: 529 bytes
Desc: not available
Url : http://lists.mailscanner.info/pipermail/mailscanner/attachments/20030221/aff562f7/MailScanner.obj
-------------- next part --------------
--
Julian Field
www.MailScanner.info
MailScanner thanks transtec Computers for their support