SQL Redux

[Julian Field]

You want MailWatch.
Hopefully someone else can comment on its exact current state.

At 20:32 01/08/2003, you wrote:
>Ok, so um, for those of us who aren't keeping several patchset branches in
>our heads but who now want to start sql logging, what are the options?
>Could someone do a quick round-up?
>
>I need to provide a web interface that provides plenty of detail about each
>message by middle of next week and I'm wondering whether I should do my own
>thing with a flat file log (since I have little time) or configure in
>support for one of the existing sql logging mechanisms.
>
>"Mailwatch for Mailscanner" seems to be at version 0.2 but I recall mentions
>of patches for bugs post 0.2.  Is there a later version available?
>
>There's the sql logging code already in CustomConfig.pm, but is there a web
>interface for built yet for the tables it creates?  And I'm assuming I'll
>need the latest version of mailscanner to get the fixes listed in this
>thread.
>
>"David While's Mailstats" looks nice (though I'm not doing virus scanning
>and don't particularly care about geo-locating stuff), but I need a
>per-message interface, and mailstats seems to be more for performance
>reporting than for log analysis.
>
>The mailscanner-mrtg package again is for performance reporting, not log
>analysis.
>
>...So...
>
>What's my best bet for a web interface to logged data that includes such
>things as subject, recipients, spam tests, etc in the short term?
>
>-t.
>
>
> >-----Original Message-----
> >From: Kearney, Rob [mailto:RKearney at AZERTY.COM]
> >Sent: Thursday, July 31, 2003 10:47 AM
> >To: MAILSCANNER at JISCMAIL.AC.UK
> >Subject: Re: SQL Redux
> >
> >
> >oh.. and yes..
> >
> >thanks for the code tippets.. I'll have to change this. as I'm
> >not good with
> >perl  either.
> >
> >-rob
> >
> >-----Original Message-----
> >From: Steve Freegard [mailto:steve.freegard at lbsltd.co.uk]
> >Sent: Thursday, July 31, 2003 11:19 AM
> >To: 'Kearney, Rob '; 'MAILSCANNER at JISCMAIL.AC.UK '
> >Subject: RE: SQL Redux
> >
> >
> >Hi Rob,
> >
> >I'm not really much good with Perl (maybe Julian can back me
> >up on this) -
> >but my understanding is that in calling your SQLRTLogging
> >procedure without
> >the Init & End procedures will mean that the
> >connection/disconnection/prepare and execution of the SQL will
> >happen for
> >every message batch processed by MailScanner which would slow things up
> >quite considerably depending on the volume of messages you processes.
> >
> >The most expensive processes are connecting and preparing the
> >statement, so
> >it's better only to do this once (per child), then running the prepared
> >statements once per message batch.
> >
> >A better way is to have:
> >
> >InitSQLRTLogging:  (this is done once per MailScanner child)
> > - Connect to the database
> > - Prepare each SQL statement required
> >
> >SQLRTLogging: (done once for each message batch)
> > - Tidy-up the data to make it suitable for SQL
> > - Execute the prepared statements
> >
> >EndSQLRTLogging: (done once as each child dies)
> > - Disconnect from the database
> >
> >Cheers,
> >Steve.
> >
> >-----Original Message-----
> >From: Kearney, Rob
> >To: MAILSCANNER at JISCMAIL.AC.UK
> >Sent: 31/07/03 15:56
> >Subject: Re: SQL Redux
> >
> >here is what we did for SQL logging, to bypass temp-file stuff.
> >
> >Just took the SQLLogging and made SQLRTLogging, to write
> >directly to DB,
> >We
> >have not noticed any degradation in performance
> >Basically, we took the functions of SQLLogging and
> >EndSQLLogging and put
> >them together.
> >(dont forget Init and End scripts also
> >
> >---
> >sub SQLRTLogging {
> >  my($message) = @_;
> >  my($dbh);
> >  $dbh =
> >DBI->connect("DBI:mysql:mailscanner:localhost:mysql_socket=/var
> >/database
> >/mys
> >ql/mysql.sock",
> >                      "mailscanner", "mailscanner",
> >                      {'PrintError' => 0})
> >   or MailScanner::Log::DieLog("Cannot connect to the database: %s",
> >                                $DBI::errstr);
> >
> >  my $id = $message->{id};
> >  my $size = $message->{size};
> >  my $from = $message->{from};
> >  my ($from_user, $from_domain);
> >
> >  # split the from address into user and domain bits.
> >  # This may be unnecessary for you; we use it to more easily determine
> >  # inbound vs outbound email in a multi-domain environment.
> >  # HINT: refine queries using SQL 'join' with a table containing local
> >  # domains.
> >
> >  ($from_user, $from_domain) = split /\@/, $from;
> >
> >  my @to   = @{$message->{to}};
> >  my $subject = $message->{subject};
> >  my $clientip = $message->{clientip};
> >  my $archives = join(',', @{$message->{archiveplaces}});
> >  my $isspam = $message->{isspam};
> >  my $ishighspam = $message->{ishigh};
> >  my $sascore = $message->{sascore};
> >  my $spamreport = $message->{spamreport};
> >
> >  # Get rid of control chars and tidy-up SpamAssassin report
> >  $spamreport =~ s/\n/ /g;
> >  $spamreport =~ s/\t//g;
> >
> >  # Get timestamp, and format it so it is suitable to use with MySQL
> >  my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
> >localtime();
> >  my($timestamp) = sprintf("%d-%02d-%02d %02d:%02d:%02d",
> >                           $year+1900,$mon+1,$mday,$hour,$min,$sec);
> >
> ># maillog_mail insert
> >    my @fields=($timestamp, $id, $size, $from_user, $from_domain,
> >                $subject, $clientip, $archives, $isspam, $ishighspam,
> >                $sascore, $spamreport);
> >    map { s/\'/\\'/g } @fields;
> >
> >    # Insert @fields into a database table
> >    my($sth) = $dbh->prepare("INSERT INTO maillog_mail (time, msg_id,
> >size,
> >from_user, from_domain, subject, clientip, archives, isspam,
> >ishighspam,
> >sascore, spamreport) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
> >
> >$sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fiel
> >ds[4],$fi
> >elds
> >[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fi
> >elds[11])
> >or
> >MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
> >
> >
> >  my($file, $text);
> >  while(($file, $text) = each %{$message->{allreports}}) {
> >    $file = "the entire message" if $file eq "";
> >    # Use the sanitised filename to avoid problems caused by people
> >forcing
> >    # logging of attachment filenames which contain nasty SQL
> >instructions.
> >    $file = $message->{file2safefile}{$file} or $file;
> >    $text =~ s/\n/ /;  # Make sure text report only contains 1 line
> >    $text =~ s/\t/ /; # and no tab characters
> >
> >    my @fields = ($id, $file, $text);
> >    map { s/\'/\\'/g } @fields;
> >
> >    my($sth) = $dbh->prepare("INSERT INTO maillog_report (msg_id,
> >filename,
> >filereport) VALUES (?,?,?)");
> >    $sth->execute($fields[0],$fields[1],$fields[2]) or
> >MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
> >
> >  }
> >
> >  for (@to) {
> >    # again, split the recipient's email into user and domain halves
> >first.
> >    # see comment above about splitting the email like this.
> >
> >    my ($to_user, $to_domain);
> >    ($to_user, $to_domain) = split /\@/, $_;
> >    my @fields = ($id, $to_user, $to_domain);
> >    map { s/\'/\\'/g } @fields;
> >    my($sth) = $dbh->prepare("INSERT INTO maillog_recipient (msg_id,
> >to_user, to_domain) VALUES (?,?,?)");
> >    $sth->execute($fields[0],$fields[1],$fields[2]) or
> >MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
> >  }
> >
> >  # Close database connection
> >  $dbh->disconnect();
> >
> >}
> >
> >  MailScanner::Log::InfoLog("Ending SQL Real-Time Logging");
> >}
> >
> >1;
> >
> >
> >
> >-rob
> >
> >--
> >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 sender and delete the message from your mailbox.
> >
> >This footnote also confirms that this email message has been swept by
> >MailScanner (www.mailscanner.info) for the presence of
> >computer viruses.
> >

--
Julian Field
www.MailScanner.info
Professional Support Services at www.MailScanner.biz
MailScanner thanks transtec Computers for their support