SQL logging

[Julian Field]

And line 720 of MessageBatch.pm should of course have 1 less ")" at the end
of it.
:-)

At 20:06 21/02/2003, you wrote:
>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
>>**********************************************************************
>
>
>
>
>
>--
>Julian Field
>www.MailScanner.info
>MailScanner thanks transtec Computers for their support

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