SQL logging

[Randy Herban]

Another option might be to use a syslog that logs to mysql directly
(msyslogd) instead of other scripts to hack mailscanner or parse logs after
the fact.

Just an idea.

-Randy

-----Original Message-----
From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
Sent: Friday, February 21, 2003 3:12 PM
To: MAILSCANNER at JISCMAIL.AC.UK
Subject: Re: SQL logging


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