MailWatch Error: no rows retrieved from database

Discussion in 'Server Operation' started by sergio.arnaldo, Aug 28, 2009.

  1. sergio.arnaldo

    sergio.arnaldo New Member

    Hello Guys,

    I have recently installed MailScanner SpamSnake from the howto provided in HowtoForge.

    I believe I have followed the howto correctly but at the end I see no messages with MailWatch. The error is: "Error: No rows retrieved from database". I have been thinking this has to do with permissions and with meaningful help of Rocky I went through some steps again but with success.

    I also think that MailScanner is not logging into MySQL... Well, I am not sure...

    Details of my system are:
    Ubuntu Server 9.04
    MailScanner 4.75.11
    MaiWatch 1.0.4
    MySQL-Sever 5.1.30really5.0.75-0ubuntu10.2
    Spamassassin 3.2.5-4
    ClamAV 0.95.1+dfsg-1ubuntu1.2

    Everything else seems to work fine....
    Please help
     
  2. Rocky

    Rocky New Member

    Hey,

    What's in your postfix log?
     
  3. sergio.arnaldo

    sergio.arnaldo New Member

    Hello Rick,
    Please find below my postfix log (mail.log):

    Aug 31 10:07:07 spamsnake postfix/smtpd[25236]: connect from unknown[201.240.5.184]
    Aug 31 10:07:08 spamsnake postfix/smtpd[24855]: connect from unknown[95.58.39.60]
    Aug 31 10:07:08 spamsnake postfix/smtpd[24459]: connect from mail.hg.co.mz[196.28.239.152]
    Aug 31 10:07:08 spamsnake postfix/pickup[26725]: F03C2B1C4A6: uid=0 from=<root>
    Aug 31 10:07:08 spamsnake postfix/cleanup[24281]: F03C2B1C4A6: message-id=<20090831080708.F03C2B1C4A6@spamsnake.xxxxx.yyy.zz>
    Aug 31 10:07:08 spamsnake postfix/qmgr[29408]: F03C2B1C4A6: from=<root@xxxxx.yyy.zz>, size=1333, nrcpt=1 (queue active)
    Aug 31 10:07:09 spamsnake postfix/smtpd[26639]: connect from unknown[212.174.19.14]
    Aug 31 10:07:09 spamsnake postfix/smtpd[24459]: NOQUEUE: reject: RCPT from mail.gg.com.zz[196.28.239.152]: 450 4.1.8 <sguivala@missionpharma.local>: Sender address rejected: Domain not found; from=<sguivala@missionpharma.local> to=<sergio.guivala@xxxxx.yyy.zz> proto=ESMTP helo=<SERVER.missionpharma.local>
    Aug 31 10:07:09 spamsnake postfix/smtp[26165]: F03C2B1C4A6: to=<root@xxxxx.yyy.zz>, orig_to=<root>, relay=192.168.253.3[192.168.253.3]:25, delay=0.23, delays=0.03/0/0/0.2, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 93A0C1E1814E)
    Aug 31 10:07:09 spamsnake postfix/qmgr[29408]: F03C2B1C4A6: removed
    Aug 31 10:07:09 spamsnake postfix/policy-spf[26179]: handler sender_policy_framework: is decisive.
    Aug 31 10:07:09 spamsnake postfix/policy-spf[26179]: : Policy action=PREPEND Received-SPF: neutral (artemisiastyle.com: Domain does not state whether sender is authorized to use 'Torri-ovaizarg@artemisiastyle.com' in 'mfrom' identity (mechanism '?all' matched)) receiver=spamsnake.xxxxx.yyy.zz; identity=mailfrom; envelope-from="Torri-ovaizarg@artemisiastyle.com"; helo=74-94-36-190-Philadelphia.hfc.comcastbusiness.net; client-ip=74.94.36.190
    Aug 31 10:07:09 spamsnake MailScanner[26650]: SpamAssassin cache hit for message 1BB89B1C4A3.9C218
    Aug 31 10:07:09 spamsnake postfix/smtpd[24459]: disconnect from mail.gg.com.zz[196.28.239.152]
    Aug 31 10:07:09 spamsnake MailScanner[26601]: New Batch: Found 3 messages waiting
    Aug 31 10:07:09 spamsnake MailScanner[26601]: New Batch: Scanning 1 messages, 292644 bytes
    Aug 31 10:07:09 spamsnake MailScanner[26601]: Message 3AA90B1C470.5C779 from 196.38.50.4 (lucyl@stgroup.co.za) to insecm.go.zz is too big for spam checks (292644 > 200000 bytes)
    Aug 31 10:07:09 spamsnake MailScanner[26650]: Virus and Content Scanning: Starting
    Aug 31 10:07:09 spamsnake postfix/smtpd[25612]: connect from bzq-219-134-196.static.bezeqint.net[62.219.134.196]
    Aug 31 10:07:09 spamsnake MailScanner[26601]: Virus and Content Scanning: Starting
    Aug 31 10:07:09 spamsnake postfix/smtpd[25899]: disconnect from unknown[196.38.50.4]
    Aug 31 10:07:09 spamsnake postfix/policy-spf[26705]: handler sender_policy_framework: is decisive.
    Aug 31 10:07:09 spamsnake postfix/policy-spf[26705]: : Policy action=PREPEND Received-SPF: neutral (wnnlimited.com: Domain does not state whether sender is authorized to use 'arlowena2002@wnnlimited.com' in 'mfrom' identity (mechanism '?all' matched)) receiver=spamsnake.xxxxx.yyy.zz; identity=mailfrom; envelope-from="arlowena2002@wnnlimited.com"; helo=25-xdsl.anitex.by; client-ip=213.184.241.25
    Aug 31 10:07:09 spamsnake MailScanner[26650]: Requeue: 1BB49B1C494.12EF4 to 0C8E3B1C4A5
    Aug 31 10:07:09 spamsnake postfix/smtpd[25155]: NOQUEUE: reject: RCPT from 74-94-36-190-Philadelphia.hfc.comcastbusiness.net[74.94.36.190]: 554 5.7.1 Service unavailable; Client host [74.94.36.190] blocked using zen.spamhaus.org; http://www.spamhaus.org/query/bl?ip=74.94.36.190; from=<Torri-ovaizarg@artemisiastyle.com> to=<vmunguambe@mitssau.go.zz> proto=ESMTP helo=<74-94-36-190-Philadelphia.hfc.comcastbusiness.net>
    Aug 31 10:07:09 spamsnake MailScanner[26650]: Requeue: 1BB89B1C4A3.9C218 to CBF5CB1C494
    Aug 31 10:07:09 spamsnake MailScanner[26650]: Uninfected: Delivered 2 messages
    Aug 31 10:07:09 spamsnake postfix/qmgr[29408]: 0C8E3B1C4A5: from=<amahuaie_2006@yahoo.com.br>, size=110585, nrcpt=1 (queue active)
    Aug 31 10:07:09 spamsnake postfix/qmgr[29408]: CBF5CB1C494: from=<amahuaie_2006@yahoo.com.br>, size=110586, nrcpt=1 (queue active)
    Aug 31 10:07:09 spamsnake MailScanner[26650]: Deleted 2 messages from processing-database
    Aug 31 10:07:09 spamsnake MailScanner[26742]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Aug 31 10:07:09 spamsnake MailScanner[26601]: Requeue: 3AA90B1C470.5C779 to A38EFB1C4A3
    Aug 31 10:07:09 spamsnake postfix/qmgr[29408]: A38EFB1C4A3: from=<lucyl@stgroup.co.za>, size=292073, nrcpt=1 (queue active)
    Aug 31 10:07:09 spamsnake MailScanner[26601]: Uninfected: Delivered 1 messages
    Aug 31 10:07:09 spamsnake MailScanner[26601]: Deleted 1 messages from processing-database
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Read 856 hostnames from the phishing whitelist
    Aug 31 10:07:10 spamsnake postfix/smtpd[25614]: connect from unknown[95.208.234.237]
    Aug 31 10:07:10 spamsnake postfix/policy-spf[26720]: handler sender_policy_framework: is decisive.
    Aug 31 10:07:10 spamsnake postfix/policy-spf[26720]: : Policy action=PREPEND Received-SPF: permerror (mitssau.go.zz: Junk encountered in mechanism 'a:mail') receiver=spamsnake.xxxxx.yyy.zz; identity=mailfrom; envelope-from="don@mitssau.goz.zz"; helo=ppp-124-120-34-116.revip2.asianet.co.th; client-ip=124.120.34.116
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Read 6124 hostnames from the phishing blacklist
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Config: calling custom init function MailWatchLogging
    Aug 31 10:07:10 spamsnake postfix/smtp[26743]: A38EFB1C4A3: to=<bmatsule@gtadf.go.cd>, relay=192.168.253.3[192.168.253.3]:25, delay=37, delays=37/0.01/0/0.22, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 8BD0E1E1814E)
    Aug 31 10:07:10 spamsnake postfix/qmgr[29408]: A38EFB1C4A3: removed
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Using SpamAssassin results cache
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Connected to SpamAssassin cache database
    Aug 31 10:07:10 spamsnake MailScanner[26742]: Enabling SpamAssassin auto-whitelist functionality...
    Aug 31 10:07:11 spamsnake postfix/smtp[26028]: 0C8E3B1C4A5: enabling PIX workarounds: disable_esmtp delay_dotcrlf for aa.b.cc.ss.rr
     
  4. sergio.arnaldo

    sergio.arnaldo New Member

    I really cannot understand why I can't see any rows with MailWatch.
    The only thing it reads are the logs...

    With MailWatch I notice that all tables are empty except geoip_country and sa_rules. users and whitelist have 1 row erach (the mailwatch user and the 127.0.0.1 address to whitelist).
    Why isn't the database been filled?
    Can you help me to check whether mailscanner is logging MySQL????

    Please help!!
     
  5. topdog

    topdog HowtoForge Supporter

    You need to check the configuration of your Mailwatch logging module, its being called by mailscanner but for some reason it does not actually log to DB.

    Verify the username, password and database settings.
     
  6. sergio.arnaldo

    sergio.arnaldo New Member

    Guys, please help me... I went through all the steps again.
    I re-cheked the username, password and database settings but I sitill see no rows with MailWatch... This is driving me crazy!!

    In MailWatch.pm there is the following code:
    my($dbh);
    my($sth);
    my($hostname) = hostname;
    my $loop = inet_aton("127.0.0.1");
    my $server_port = 11553;
    my $timeout = 3600
    ;

    I cannot see port 11553 open with nmap on localhost. Should this port be open on localhost?

    Below is my mail.log - MailScanner is not logging into MySQL
    Please, please help! I don't know what else I should do have this working.

    Sep 2 19:47:52 spamsnake MailScanner[4400]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Read 856 hostnames from the phishing whitelist
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Read 6856 hostnames from the phishing blacklist
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Config: calling custom init function MailWatchLogging
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Using SpamAssassin results cache
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Connected to SpamAssassin cache database
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Expired 10 records from the SpamAssassin cache
    Sep 2 19:47:52 spamsnake MailScanner[4400]: Enabling SpamAssassin auto-whitelist functionality...
    Sep 2 19:47:54 spamsnake MailScanner[4400]: Connected to processing-messages database
    Sep 2 19:47:54 spamsnake MailScanner[4400]: Found 459659 messages in the processing-messages database
    Sep 2 19:47:54 spamsnake MailScanner[4400]: Using locktype = flock
    Sep 2 19:47:57 spamsnake MailScanner[4403]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Read 856 hostnames from the phishing whitelist
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Read 6856 hostnames from the phishing blacklist
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Config: calling custom init function MailWatchLogging
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Using SpamAssassin results cache
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Connected to SpamAssassin cache database
    Sep 2 19:47:57 spamsnake MailScanner[4403]: Enabling SpamAssassin auto-whitelist functionality...
    Sep 2 19:47:59 spamsnake MailScanner[4403]: Connected to processing-messages database
    Sep 2 19:47:59 spamsnake MailScanner[4403]: Found 459659 messages in the processing-messages database
    Sep 2 19:47:59 spamsnake MailScanner[4403]: Using locktype = flock
    Sep 2 19:48:02 spamsnake MailScanner[4406]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Read 856 hostnames from the phishing whitelist
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Read 6856 hostnames from the phishing blacklist
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Config: calling custom init function MailWatchLogging
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Using SpamAssassin results cache
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Connected to SpamAssassin cache database
    Sep 2 19:48:02 spamsnake MailScanner[4406]: Enabling SpamAssassin auto-whitelist functionality...
    Sep 2 19:48:04 spamsnake MailScanner[4406]: Connected to processing-messages database
    Sep 2 19:48:04 spamsnake MailScanner[4406]: Found 459659 messages in the processing-messages database
    Sep 2 19:48:04 spamsnake MailScanner[4406]: Using locktype = flock
    Sep 2 19:48:07 spamsnake MailScanner[4409]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Read 856 hostnames from the phishing whitelist
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Read 6856 hostnames from the phishing blacklist
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Config: calling custom init function MailWatchLogging
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Using SpamAssassin results cache
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Connected to SpamAssassin cache database
    Sep 2 19:48:07 spamsnake MailScanner[4409]: Enabling SpamAssassin auto-whitelist functionality...
    Sep 2 19:48:09 spamsnake MailScanner[4409]: Connected to processing-messages database
    Sep 2 19:48:09 spamsnake MailScanner[4409]: Found 459659 messages in the processing-messages database
    Sep 2 19:48:09 spamsnake MailScanner[4409]: Using locktype = flock
    Sep 2 19:48:12 spamsnake MailScanner[4412]: MailScanner E-Mail Virus Scanner version 4.75.11 starting...
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Read 856 hostnames from the phishing whitelist
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Read 6856 hostnames from the phishing blacklist
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Config: calling custom init function MailWatchLogging
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Using SpamAssassin results cache
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Connected to SpamAssassin cache database
    Sep 2 19:48:12 spamsnake MailScanner[4412]: Enabling SpamAssassin auto-whitelist functionality...
    Sep 2 19:48:14 spamsnake MailScanner[4412]: Connected to processing-messages database
    Sep 2 19:48:14 spamsnake MailScanner[4412]: Found 459659 messages in the processing-messages database
    Sep 2 19:48:14 spamsnake MailScanner[4412]: Using locktype = flock
     
  7. topdog

    topdog HowtoForge Supporter

    This is what you need to be checking
    Code:
    my($db_name) = 'mailscanner';
    my($db_host) = 'localhost';
    my($db_user) = 'mailwatch';
    my($db_pass) = 'password';
     
  8. sergio.arnaldo

    sergio.arnaldo New Member

    topdog,
    I really appreciate your reply.

    As I mentioned before I've re-checked the username, password and database settings but I sitill see no rows with MailWatch...

    How can I test or debug mailscanner logging into MySQL?
    Are there any other tests that I should perform as regarding this issue?
    Please help.
     
  9. topdog

    topdog HowtoForge Supporter

    Yes you can, edit the MailWatch.pm file and uncomment DBI->trace as indicated below. Then restart MailScanner and look at the log file /root/dbitrace.log

    Code:
    # Trace settings - uncomment this to debug
    DBI->trace(2,'/root/dbitrace.log');
     
  10. sergio.arnaldo

    sergio.arnaldo New Member

    topdog,

    I can't get any logging in that file....
     
  11. topdog

    topdog HowtoForge Supporter

    Can you post you MailWatch.pm file.
     
  12. sergio.arnaldo

    sergio.arnaldo New Member

    package MailScanner::CustomConfig;

    use strict;
    use DBI;
    use Sys::Hostname;
    use Storable(qw[freeze thaw]);
    use POSIX;
    use Socket;
    Here it is:

    # Trace settings - uncomment this to debug
    DBI->trace(2,'/root/dbitrace.log');

    my($dbh);
    my($sth);
    my($hostname) = hostname;
    my $loop = inet_aton("127.0.0.1");
    my $server_port = 11553;
    my $timeout = 3600;


    # Modify this as necessary for your configuration
    my($db_name) = 'mailscanner';
    my($db_host) = 'localhost';
    my($db_user) = 'mailwatch';
    my($db_pass) = 'mailwatch';

    sub InitMailWatchLogging {
    my $pid = fork();
    if ($pid) {
    # MailScanner child process
    waitpid $pid, 0;
    MailScanner::Log::InfoLog("Started SQL Logging child");
    } else {
    # New process
    # Detach from parent, make connections, and listen for requests
    POSIX::setsid();
    if (!fork()) {
    $SIG{HUP} = $SIG{INT} = $SIG{PIPE} = $SIG{TERM} = $SIG{ALRM} = \&ExitLogging;
    alarm $timeout;
    $0 = "MailWatch SQL";
    InitConnection();
    ListenForMessages();
    }
    exit;
    }
    }

    sub InitConnection {
    # Set up TCP/IP socket. We will start one server per MailScanner
    # child, but only one child will actually be able to get the socket.
    # The rest will die silently. When one of the MailScanner children
    # tries to log a message and fails to connect, it will start a new
    # server.
    socket(SERVER, PF_INET, SOCK_STREAM, getprotobyname("tcp"));
    setsockopt(SERVER, SOL_SOCKET, SO_REUSEADDR, 1);
    my $addr = sockaddr_in($server_port, $loop);
    bind(SERVER, $addr) or exit;
    listen(SERVER, SOMAXCONN) or exit;

    # Our reason for existence - the persistent connection to the database
    $dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, {PrintError => 0, AutoCommit =>$
    if (!$dbh) {
    MailScanner::Log::WarnLog("Unable to initialise database connection: %s", $DBI::errstr);
    }
    $sth = $dbh->prepare("INSERT INTO maillog (timestamp, id, size, from_address, from_domain, to_address, to_domain, su$
    MailScanner::Log::WarnLog($DBI::errstr);
    }


    sub ExitLogging {
    # Server exit - commit changes, close socket, and exit gracefully.
    close(SERVER);
    $dbh->commit;
    $dbh->disconnect;
    exit;
    }

    sub ListenForMessages {
    my $message;
    # Wait for messages
    while (my $cli = accept(CLIENT, SERVER)) {
    my($port, $packed_ip) = sockaddr_in($cli);
    my $dotted_quad = inet_ntoa($packed_ip);

    # reset emergency timeout - if we haven"t heard anything in $timeout
    # seconds, there is probably something wrong, so we should clean up
    # and let another process try.
    alarm $timeout;
    # Make sure we"re only receiving local connections
    if ($dotted_quad ne "127.0.0.1") {
    close CLIENT;
    next;
    }
    my @in;
    while (<CLIENT>) {
    # End of normal logging message
    last if /^END$/;
    # MailScanner child telling us to shut down
    ExitLogging if /^EXIT$/;
    chop;
    push @in, $_;
    }
    my $data = join "", @in;
    my $tmp = unpack("u", $data);
    $message = thaw $tmp;

    next unless defined $$message{id};

    # Check to make sure DB connection is still valid
    InitConnection unless $dbh->ping;

    # Log message
    $sth->execute(
    $$message{timestamp},
    $$message{id},
    $$message{size},
    $$message{from},
    $$message{from_domain},
    $$message{to},
    $$message{to_domain},
    $$message{subject},
    $$message{clientip},
    $$message{archiveplaces},
    $$message{isspam},
    $$message{ishigh},
    $$message{issaspam},
    $$message{isrblspam},
    $$message{spamwhitelisted},
    $$message{spamblacklisted},
    $$message{sascore},
    $$message{spamreport},
    $$message{virusinfected},
    $$message{nameinfected},
    $$message{otherinfected},
    $$message{reports},
    $$message{ismcp},
    $$message{ishighmcp},
    $$message{issamcp},
    $$message{mcpwhitelisted},
    $$message{mcpblacklisted},
    $$message{mcpsascore},
    $$message{mcpreport},
    $$message{hostname},
    $$message{date},
    $$message{"time"},
    $$message{headers},
    $$message{quarantined});

    # this doesn't work in the event we have no connection by now ?
    if (!$sth) {
    MailScanner::Log::WarnLog("$$message{id}: MailWatch SQL Cannot insert row: %s", $sth->errstr);
    } else {
    MailScanner::Log::InfoLog("$$message{id}: Logged to MailWatch SQL");
    }

    # Unset
    $message = undef;

    }
    }

    sub EndMailWatchLogging {
    # Tell server to shut down. Another child will start a new server
    # if we are here due to old age instead of administrative intervention
    socket(TO_SERVER, PF_INET, SOCK_STREAM, getprotobyname("tcp"));
    my $addr = sockaddr_in($server_port, $loop);
    connect(TO_SERVER, $addr) or return;

    print TO_SERVER "EXIT\n";
    close TO_SERVER;
    }

    sub MailWatchLogging {
    my($message) = @_;
    # Don't bother trying to do an insert if no message is passed-in
    return unless $message;

    # Fix duplicate 'to' addresses for Postfix users
    my(%rcpts);
    map { $rcpts{$_}=1; } @{$message->{to}};
    @{$message->{to}} = keys %rcpts;

    # Get rid of control chars and tidy-up SpamAssassin report
    my $spamreport = $message->{spamreport};
    $spamreport =~ s/\n/ /g;
    $spamreport =~ s/\t//g;

    # Same with MCP report
    my $mcpreport = $message->{mcpreport};
    $mcpreport =~ s/\n/ /g;
    $mcpreport =~ s/\t//g;

    # Workaround tiny bug in original MCP code
    my($mcpsascore);
    if (defined $message->{mcpsascore}) {
    $mcpsascore = $message->{mcpsascore};
    } else {
    $mcpsascore = $message->{mcpscore};
    }

    # Set quarantine flag - this only works on 4.43.7 or later
    my($quarantined);
    $quarantined = 0;
    if ( (scalar(@{$message->{quarantineplaces}}))
    + (scalar(@{$message->{spamarchive}})) > 0 )
    {
    $quarantined = 1;
    }

    # 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(",",@report_array);

    # Fix the $message->{clientip} for later versions of Exim
    # where $message->{clientip} contains ip.ip.ip.ip.port
    my $clientip = $message->{clientip};
    $clientip =~ s/^(\d+\.\d+\.\d+\.\d+)(\.\d+)$/$1/;
    # Integrate SpamAssassin Whitelist/Blacklist reporting
    if($spamreport =~ /USER_IN_WHITELIST/) {
    $message->{spamwhitelisted} = 1;
    }
    if($spamreport =~ /USER_IN_BLACKLIST/) {
    $message->{spamblacklisted} = 1;
    }

    # Get the first domain from the list of recipients
    my($todomain,@todomain);
    @todomain = @{$message->{todomain}};
    $todomain = $todomain[0];

    # Place all data into %msg
    my %msg;
    $msg{timestamp} = $timestamp;
    $msg{id} = $message->{id};
    $msg{size} = $message->{size};
    $msg{from} = $message->{from};
    $msg{from_domain} = $message->{fromdomain};
    $msg{to} = join(",", @{$message->{to}});
    $msg{to_domain} = $todomain;
    $msg{subject} = $message->{subject};
    $msg{clientip} = $clientip;
    $msg{archiveplaces} = join(",", @{$message->{archiveplaces}});
    $msg{isspam} = $message->{isspam};
    $msg{ishigh} = $message->{ishigh};
    $msg{issaspam} = $message->{issaspam};
    $msg{isrblspam} = $message->{isrblspam};
    $msg{spamwhitelisted} = $message->{spamwhitelisted};
    $msg{spamblacklisted} = $message->{spamblacklisted};
    $msg{sascore} = $message->{sascore};
    $msg{spamreport} = $spamreport;
    $msg{ismcp} = $message->{ismcp};
    $msg{ishighmcp} = $message->{ishighmcp};
    $msg{issamcp} = $message->{issamcp};
    $msg{mcpwhitelisted} = $message->{mcpwhitelisted};
    $msg{mcpblacklisted} = $message->{mcpblacklisted};
    $msg{mcpsascore} = $mcpsascore;
    $msg{mcpreport} = $mcpreport;
    $msg{virusinfected} = $message->{virusinfected};
    $msg{nameinfected} = $message->{nameinfected};
    $msg{otherinfected} = $message->{otherinfected};
    $msg{reports} = $reports;
    $msg{hostname} = $hostname;
    $msg{date} = $date;
    $msg{"time"} = $time;
    $msg{headers} = join("\n",@{$message->{headers}});
    $msg{quarantined} = $quarantined;

    # Prepare data for transmission
    my $f = freeze \%msg;
    my $p = pack("u", $f);

    # Connect to server
    while (1) {
    socket(TO_SERVER, PF_INET, SOCK_STREAM, getprotobyname("tcp"));
    my $addr = sockaddr_in($server_port, $loop);
    connect(TO_SERVER, $addr) and last;
    # Failed to connect - kick off new child, wait, and try again
    InitMailWatchLogging();
    sleep 5;
    }
    # Pass data to server process
    MailScanner::Log::InfoLog("Logging message $msg{id} to SQL");
    print TO_SERVER $p;
    print TO_SERVER "END\n";
    close TO_SERVER;
    }

    1;
     
  13. sergio.arnaldo

    sergio.arnaldo New Member

    Please ignore the 9th line "Here it is:"..... it's my mistake...
     
  14. topdog

    topdog HowtoForge Supporter

    That looks fine i cannot think of anything else.
     
  15. sergio.arnaldo

    sergio.arnaldo New Member

    Then I am completely lost...

    You know, I don't even get any error.. this is very strange.. sometimes I think that that file is not being used...
     
  16. topdog

    topdog HowtoForge Supporter

    What is the full path to the Mailwatch.pm file ?
     
  17. sergio.arnaldo

    sergio.arnaldo New Member

    the path is:
    /opt/MailScanner/etc/CustomFunctions/MailWatch.pm
     
  18. topdog

    topdog HowtoForge Supporter

    Is that the correct path because i know under Centos its at /usr/lib/MailScanner/MailScanner/CustomFunctions/Mailwatch.pm
     
  19. sergio.arnaldo

    sergio.arnaldo New Member

    Yes indeed, it is the correct path on Ubuntu...
     
  20. sergio.arnaldo

    sergio.arnaldo New Member

    Dear all,

    It is all working fine now.

    topgog, you were definitely right with the CustomConfig dir path.
    I did as the howto states... but it is wrong!!!!! The real path is /opt/MailScanner/lib/MailScanner/CustomFunctions/ this is what I do have in MailWatch.pm I have to admit I did not pay attention to this before and only today I have corrected this.

    Thank you Rock and topdog for all you patience with me.

    Regards,
    /Sergio
     

Share This Page