SQL logging

[Julian Field]

That would be my preferred option too, but I don't think MailScanner is
currently outputting as much log info as they would like.

At 20:54 21/02/2003, you wrote:
>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

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