ANNOUNCE: MailWatch for MailScanner 0.3 Beta
Peter C. Ndikuwera
pndiku at DSMAGIC.COM
Fri Sep 5 15:19:21 IST 2003
Hi all,
Failed to get this working on a SuSE 7.0 system. Just like with previous
mailwatch versions I had to cook up my own Logging function (see
attached). It's based on Julian's work in the original CustomConfig.pm
and seems to work fine for me!
Comments?
Peter
On Fri, 2003-09-05 at 15:33, Chris Campbell wrote:
> Very nice... I *finally* got it hacked up enough to work on rh 7.2 and rh
> 7.3 (I was getting the same mysql server died error these bsd kids were
> getting)
>
>
> But, here is a question for you....
>
> What are the odds we can grab and insert into the sql db the sendmail relay
> log......and the status.....
> I am looking for the relay part and whether is was Sent, Queued, etc.....
>
>
>
>
> .....................................
> Christopher S. Campbell
> UNIX Admin
> First Albany Corp
> 518.447.8544
> chris.campbell at fac.com
>
>
>
>
>
> Steve Freegard
> <steve.freegard at LBS To: MAILSCANNER at JISCMAIL.AC.UK
> LTD.CO.UK> cc:
> Sent by: Subject: ANNOUNCE: MailWatch for MailScanner 0.3 Beta
> MailScanner mailing
> list
> <MAILSCANNER at JISCMA
> IL.AC.UK>
>
>
> 09/05/03 08:08 AM
> Please respond to
> MailScanner mailing
> list
>
>
>
>
>
>
> Hi all,
>
> I've (finally!) released a beta of 0.3 - you can download it from
> http://www.sourceforge.net/projects/mailwatch
>
> If you haven't already noticed - the project has been moved to Sourceforge,
> so I encourage everyone who uses MailWatch to sign-up for the Mailing Lists
> that are available and to use the other features such as the Feature
> Requests, Bug Tracking, Forums and Patches from this point onward.
>
> Changes in this release:
>
> - New MailWatch.pm file that contains the MailWatch SQL Logging code.
>
> - Changed the SQL Logging procedure names from SQLLogging to
> MailWatchLogging
> to save confusion as to which versions people are using.
>
> - Updated MailWatchLogging procedures to better handle MySQL death and
> subsequent restart without needing to restart MailScanner.
>
> - Message headers now displayed on the Message Detail page.
>
> - OpenRBL lookup address fixed (OpenRBL had updated their site).
>
> - Spam Action(s) displayed next to Spam/High Scoring Spam on the Message
> Detail page.
>
> - New 'Quarantine Manager' allows quarantined messages to be released to
> recipient(s), deleted or learnt/unlearnt by SpamAssassin as Spam or Ham.
>
> - Major speed-ups on page display.
>
> - Added extra Virus regular expressions and modified the existing to drop
> the requirement of 'Include Scanner Name in Reports' in
> MailScanner.conf.
>
> - New Sendmail inbound/outbound queue display.
>
> - Fixed the display of the 'Blocked Files' percentage in Today's Totals.
>
> - Fixed the volume display in the reports to use the average over the
> reporting period e.g. if you receive 500Mb of mail on average per day
> but
> you occasionally spike at 1Gb - the reports will display the volume in
> Mb.
>
> - Added new 'MySQL status' page to the 'Other' page.
>
> - Fixed 'SpamAssassin Rule Hits' report not display any data under some
> installations of MailScanner.
>
> - New reports 'Top Mail Relays' and 'Top Sender Domains by
> Quantity/Volume'.
>
> - Added 'hostname' the the list of available filters to allow people with
> multiple scanners report only on a specific one.
>
> Kind regards,
> Steve
>
> --
> 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.
-------------- next part --------------
#
# MailWatch for MailScanner
# Copyright (C) 2003 Steve Freegard (smf at f2s.com)
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#
use DBI;
use Sys::Hostname;
# Trace settings - uncomment this to debug
# DBI->trace(2,'/root/dbitrace.log');
my($dbh);
my($sth);
my($hostname) = hostname;
# Modify this as necessary for your configuration
my($db_name) = "mailscanner";
my($db_host) = "localhost";
my($db_user) = "root";
my($db_pass) = "";
sub InitMailWatchLogging {
MailScanner::Log::InfoLog("Initialising MailWatch Logging temp file");
$logfile1 = IO::File->new_tmpfile or die "IO::File->new_tmpfile: $!";
}
# Shutdown. Write all the log entries to the SQL database, then close
# the temporary log files. Closing them will also delete them as they were
# created with tmpfile().
sub EndMailWatchLogging {
my(@fields);
MailScanner::Log::InfoLog("Ending SQL Logging temp output " .
"and flushing to database");
# Create database connection
my($dbh);
# Connect to the database
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host",
$db_user, $db_pass,
{PrintError => 0});
or MailScanner::Log::DieLog("Cannot connect to the database: %s",
$DBI::errstr);
# Rewind to start of logfile1
$logfile1->flush();
seek($logfile1, 0, 0)
or MailScanner::Log::DieLog("EndSQLLogging seek: %s", $!);
while(<$logfile1>) {
chomp;
@fields = split(/\t/);
# Work through each field protecting any special characters such as '
# The line below replaces ' with \'
# @fields = map { s/\'/\\'/g } @fields;
# Set any empty strings to NULL so the SQL insert works correctly
@fields = map { ($_ eq '')?'NULL':"$_" } @fields;
# Insert @fields into a database table
my($sth) = $dbh->prepare("INSERT INTO maillog VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
$sth->execute($fields[0],$fields[1],$fields[2],$fields[3],$fields[4],$fields[5],$fields[6],$fields[7],$fields[8],$fields[9],$fields[10],$fields[11],$fields[12],$fields[13],$fields[14],$fields[15],$fields[16],$fields[17],$fields[18],$fields[19],$fields[20],$fields[21],$fields[22]) or
MailScanner::Log::DieLog("Cannot insert row: %s", $DBI::errstr);
}
# Close database connection
$dbh->disconnect();
# Close and delete the temporary files (deletion is done automatically)
$logfile1->close();
MailScanner::Log::InfoLog("Database flush completed");
}
# Write all the log information for 1 message to the temporary file.
# For messages with reports, write 1 line for each report.
sub MailWatchLogging {
my($message) = @_;
# Get rid of control chars and tidy-up SpamAssassin report
my $spamreport = $message->{spamreport};
$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);
my($date) = sprintf("%d-%02d-%02d",$year+1900,$mon+1,$mday);
my($time) = sprintf("%02d:%02d:%02d",$hour,$min,$sec);
# Also print 1 line for each report about this message. These lines
# contain all the info above, + the attachment filename and text of
# each report.
my($file, $text, @report_array);
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
push (@report_array, $text);
}
# Sanitize reports
my $reports = join(",", at report_array);
my $headers = join('\n',@{$message->{headers}});
# Print 1 line for each message.
print $logfile1 join("\t",
$timestamp,
$message->{id},
$message->{size},
$message->{from},
join(',', @{$message->{to}}),
$message->{subject},
$message->{clientip},
join(',', @{$message->{archiveplaces}}),
$message->{isspam},
$message->{ishigh},
$message->{issaspam},
$message->{isrblspam},
$message->{spamwhitelisted},
$message->{sascore},
$spamreport,
$message->{virusinfected},
$message->{nameinfected},
$message->{otherinfected},
$reports,
'entandikwa.ds.co.ug',
$date,
$time,
$headers
). "\n";
}
1;
More information about the MailScanner
mailing list