SQL logging

[Julian Field]

At 17:32 21/02/2003, you wrote:
>Julian,
>
>That would be perfect!
>
>How about hiding the config option instead - created as CustomLogging which
>is called at the end of the batch that tests to see if
>InitCustomLogging/CustomLogging/EndCustomLogging has been defined or not and
>calls them if they are - possible?? - at least it would save on confusion in
>the config file...

I'll just put the "Always Evaluated Last" in the "Advanced" section at the
bottom so most people won't try touching it until they start searching for
a way to do this. I would rather not have "hidden" config options. As the
option's value is ignored anyway, very little processing has to be done to
evaluate it for every message. I would expect the overhead to be a tiny
fraction of 1% of the time it takes to do the whole loop.

>-----Original Message-----
>From: Julian Field [mailto:mailscanner at ECS.SOTON.AC.UK]
>Sent: 21 February 2003 17:18
>To: MAILSCANNER at jiscmail.ac.uk
>Subject: Re: Spamassassin 2.50 & SQL logging
>
>
>Sounds like you all need a
>          Always Looked Up Last
>parameter, whose value was ignored. This will be evaluated for every
>message, right at the end of the message batch loop. If it is just set to
>"yes" or "no" the overhead will be minimal for people that don't use it for
>anything. It is there specifically to have "side-effects" so you can do
>your logging.
>
>Any better suggestions for the name?
>
>At 16:51 21/02/2003, you wrote:
> >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
>
>--
>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