mail log parsing script in need of makeover

Dear unix forum members,

I'm working on a script that will parse a mail machine's logs and print a list of email addresses in this format:

sender@domain,recipient@domain

The logs look something like this:

06:50:04 0048317AC863: client=localhost.com[127.0.0.1]
06:50:04 0048317AC863: message-id=<user@domain>
06:50:04 0048317AC863: from=<user@domain>,
06:50:04 0048317AC863: to=<user@domain>,
06:50:06 0048317AC863: to=<user@domain>,
06:50:18 0048317AC863: to=<user@domain>,
06:50:18 0048317AC863: to=<user@domain>,
06:50:18 0048317AC863: removed

The "from" and "to" are on different lines and there is another challenge which is that the results should be limited to messages who have 5 or fewer recipients.

I thought it would be easy enough, and I wrote a script that first gets a list of the tag numbers ( 0048317AC863:) which belong to messages with 5 or fewer recipients

#!/bin/sh
grep "to=<" /data/log/maillog | grep postfix | grep -vi noqueue | awk '{print $6}' | sort |uniq -c > all_ids

cat all_ids |awk '{print " "$1, $2}' | egrep " 1 | 2 | 3 | 4 | 5 " | cut -f 3 -d " " > ids

Very crude and spaghetti like...and even worse is the FOR loop that follows, which involves grepping through the entire 4000mb maillog file 33,000 times in order to print the sender and recipient addresses.

Needless to say, its not an efficient script, there must be a better way. Please help!! Any responses are appreciated, maybe someone can just point me in the right direction?
Thanks,
JJ

The script which parses the log file to find the id:s might as well be a little more complex, and extract the final information you want while it's reading through the file anyway. If you're not very familiar with scripting languages, though, that's going to be a bit of a learning curve. I would pick Perl but there is certainly ample facilities for this sort of processing in awk as well.

In a typical mail long, there's rarely more than a few transactions going on at the same time, so memory requirements are probably not a problem. Just write and flush a transaction as soon as you see it's done, and you should never have more than a few in memory at any one time.

Without a more representative log snippet, it's a bit shaky, but here's a first start.

perl -nae 'if ($F[2] eq "removed") { &flush($F[1]); next; }
if ($F[2] =~ m/^from=(.*)/) { $s{$F[1]} = $1; next; }
if ($F[2] =~ m/^to=(.*),/) { push @{$r{$F[1]}}, $1; next; }
sub flush {
  my ($id) = @_;
  if (@{$r{$id}} <= 5) { map { print "$s{$id}$r{$id}->[$_]\n" } 0..$#{$r{$id}} }
  delete $s{$id};
  undef $r{$id}
}' logfile

This keeps a hash %s of senders and an array @{$r} of receipients for each sender. When a queue entry is removed, it is printed if it has 5 or fewer recipients, and removed from the hash and the array. The use of references makes it a bit hard to read, I'm afraid. It would be simpler but also a bit less efficient if there was just one long string for each sender, which then might or might not be printed. (Or, ahem, use Python.)

Thanks for the advice era!

I was thinking perl would be the way to go, but you also mention python. What would be the specific advantage of using python in this situation?

I'm not very familiar with python, but i've used perl a bit and I like it. I'm also in a bit of a hurry to produce this script so I'm not likely to try and tackle it in python.

Anyway, your snippet is more than enough and much appreciated!

I haven't used python much myself, but it does seem more elegant when it comes to handling nested data structures. If you can get by with the Perl then by all means use that.

OK, this is what I have for the perl script so far. I got into trouble towards the end, and it doesn't like what i'm trying to do with the variable.

#!/usr/local/bin/perl
#use strict;
use locale;
use DBI;
use Cwd ;

my %sender_emails = () ;
my %recipient_emails = () ;
my %recipient_count = () ;

$logfile = '/data/log/maillog';

open(LOG, $logfile);
while (<LOG>)
{
($msgMon, $msgDay, $msgTime, $msgHost, $msgCmd, $QID, $from_to) = split(/\s+/, $_) ;

    next if \(/from=<>/\) ;
    next if \(/from=<root>/\) ;

    if \(\($_ =~ /from=</\) && \($_ =~ /qmgr/\)\)
    \{
            \($tmpString, $from\) = split\("from=<", $_\);
            \($from,$tmpString\)  = split\(">", $from\);
            $sender_emails \{$QID\} = $from;
    \}
    elsif \(\($_ =~ /to=</\) && \($_ =~ /smtp/\)\)
    \{
            \($tmpString, $to\) = split\("to=<", $_\);
            \($to,$tmpString\) = split\(">", $to\);
            $recipient_emails \{$QID\} = $recipient_emails \{$QID\} .  "$to "   ;
            $recipient_count \{$QID\}\+\+   ;
    \}

}
close(LOG);

foreach $myQID (keys %sender_emails)
{
$myto = $recipient_emails{$myQID} ;
$myfrom = $sender_emails{$myQID} ;
$tocount = $recipient_count{$myQID} ;
next if $tocount >= 6;
foreach $rcpt_group (values %sender_emails)
{
($1, $2, $3, $4, $5) = split(/\s+/, $_);
@rcpt = ("$1", "$2", "$3", "$4", "$5");
{
foreach $rcpt (@rcpt)
{
print $myfrom . "," . $rcpt . \n;
}
}
}
}

This is a working version of the last portion of the script.

foreach $myQID (keys %sender_emails)
{
$myto = $recipient_emails{$myQID} ;
$myfrom = $sender_emails{$myQID} ;
$tocount = $recipient_count{$myQID} ;
next if $tocount >= 6;
{
print $myfrom . "," . $myto . \n;
}
}

the only problem is that it prints out lines with message recipients greater than one in the following fashion.

sender@domain,recipient1@domain recipient2@domain etc.

when I ultimately need:

sender@domain,recipient1@domain
sender@domain,recipient2@domain
sender@domain,recipient3@domain
and so on...

this is what the log entries actually look like.

May 14 01:08:38 mail11 postfix/smtpd[86997]: 21F9C17ADDEB: client=domain.com[127.0.0.1]
May 14 01:08:38 mail11 postfix/cleanup[87530]: 21F9C17ADDEB: message-id=<00ec01c8b580$73d85d60$da0ba8c0@domain>
May 14 01:08:38 mail11 postfix/qmgr[9455]: 21F9C17ADDEB: from=<user@domain>, size=18310, nrcpt=3 (queue active)
May 14 01:08:39 mail11 postfix/smtp[86884]: 21F9C17ADDEB: to=<user@domain>, relay=domain [127.0.0.1]:25, delay=1, delays=0.21/0/0.45/0.39, dsn=2.0.0, status=sent (250 ok: Message 149052398 accepted)
May 14 01:08:39 mail11 postfix/smtp[87444]: 21F9C17ADDEB: to=<user@domain>, relay=domain.com[127.0.0.1]:25, delay=1.8, delays=0.21/0/1.1/0.51, dsn=2.0.0, status=sent (250 Ok: queued as E572B24807B)
May 14 01:08:39 mail11 postfix/smtp[87444]: 21F9C17ADDEB: to=<user@domain>, relay=mail.domain.com[127.0.0.1]:25, delay=1.8, delays=0.21/0/1.1/0.51, dsn=2.0.0, status=sent (250 Ok: queued as E572B24807B)
May 14 01:08:39 mail11 postfix/qmgr[9455]: 21F9C17ADDEB: removed

As always any comments, criticisms, and questions are welcome and appreciated.
-JJ

Please use code tags for legibility. Fortunately I get proper indentation when I quote your message so it's not completely unreadable.

use locale;
use DBI;
use Cwd ;

You don't seem to be using the DBI stuff or Cwd; also doubtful what the locale is for. The logs don't use locale-dependent formatting, do they?

foreach $myQID (keys %sender_emails)
{
        $myto = $recipient_emails{$myQID} ;
        $myfrom = $sender_emails{$myQID} ;
        $tocount = $recipient_count{$myQID} ;
        next if $tocount >= 6;
        foreach $rcpt_group (values %sender_emails)
        {
                ($1, $2, $3, $4, $5) = split(/\s+/, $_);
                @rcpt = ("$1", "$2", "$3", "$4", "$5");
                {
                        foreach $rcpt (@rcpt)
                        {
                                print $myfrom . "," . $rcpt . \n;
                        }
                }
        }
}

You can certainly defer all processing until you have read the whole log file, but like I suggested earlier, it might be more memory-efficient to process and forget queue entries as you see them, assuming that "removed" is a good pattern for seeing when you have a full entry.

Assigning to $1 $2 etc seems wrong, I guess you might be able to do it but that's definitely not recommended. Why don't you assign the result of the split directly to the list @rcpt anyway? If you want to restrict it to just five fields and throw away the rest, you can restrict the split, or splice away the remainder after splitting. And I guess you want to be splitting $rcpt_group, not $_.

Better yet, collect the formatted output already in the initial loop, and just don't print it if the count is too large. I wanted to avoid having two variables and needlessly collect information which was not going to be printed, so I used references to lists instead, but this is certainly a workable solution as well.

Using the string join (dot) operator on a string just for printing it is mildly inefficient, you can just print a list without first gluing together its elements into a single string.

You need to double-quote the \n to make it into a newline; as it is, it's a reference to an undefined symbol (you should have used strict after all!) with a blank print value; that's why you aren't getting any newlines in your output.

To reiterate, that's basically as if you had has an identifier UNDEFINED_SYMBOL and used a backslash to produce a reference to it: \UNDEFINED_SYMBOL. Perl is unacceptable forgiving about these things when you don't use strict and use warnings, for legacy reasons -- you should always use strict and use warnings for new scripts (I wrote "nontrivial" scripts but all scripts appear trivial when you start optimistically working on them). I confess that I too sinned against this -- much against my better conscience.

Here's an attempt at combining our efforts. The test data you posted is still not very useful; the recipients should be unique, so you can see which ones are being printed, and there should be multiple transactions, some with too many recipients, so we can test that too. But this does seem to do ... something.

#!/usr/local/bin/perl

use strict;
use warnings;

my %sender_emails = () ;
my %recipient_emails = () ;
my %recipient_count = () ;

while (<>)
{
    # Do this before split for efficiency
    next if (/from=<>/) ;
    next if (/from=<root>/) ;

    my ($msgMon, $msgDay, $msgTime, $msgHost, $msgCmd, $QID, $from_to)
	= split(/\s+/, $_) ;

    if (/qmgr/ && /from=<([^<>]+)>/)
    {
	my $from = $1;
	$sender_emails {$QID} = $from;
    }
    elsif (/smtp/ && /to=<([^<>]+)>/)
    {
	my ($to) = $1;
	# Preformat output the way we want it
	$recipient_emails {$QID} .= $sender_emails{$QID} . "," . $to . "\n";
	$recipient_count {$QID}++;
    }
    elsif (m/: removed/)
    {
	if ($recipient_count{$QID} <= 5)
	{
	    print $recipient_emails{$QID};
	}
	# Flush this key from memory; we're done with it
	delete $sender_emails{$QID};
	delete $recipient_emails{$QID};
	delete $recipient_count{$QID};
    }
}

I took out the hard-coded mail log path; I'd keep it outside the script, just to make it easier to test, or run it on older or just other log files than the main one.

Sorry about the lame test data, it was very generic...I didn't want to disclose email addresses of our customers. Thats probably why your end version of the script didn't provide consistent output. I was testing with different data of course, so maybe my next script should be one which parses a log and scrambles the email and IP addresses, so I can provide more representative test data when posting. :rolleyes: Anyhow, ERA, your advice was great and just what I needed to help guide me through this.

Here is the code that I ended up with after taking some of your suggestions and adding a hash to get rid of duplicates.

#!/usr/local/bin/perl

my %sender_emails = () ;
my %recipient_emails = () ;
my %recipient_count = () ;
my %uniqueEntries = ();

# location of logfile

$logfile = '/data/log/maillog';

open(LOG, $logfile);

while (<LOG>)
{
($msgMon, $msgDay, $msgTime, $msgHost, $msgCmd, $QID, $from_to) = split(/\s+/, $_) ;

        next if (/from=<>/) ;
        next if (/from=<root>/) ;

        if (($_ =~ /from=</) && ($_ =~ /qmgr/))
        {
                ($tmpString, $from) = split("from=<", $_);
                ($from,$tmpString)  = split(">", $from);
                $sender_emails {$QID} = $from;
        }
        elsif (($_ =~ /to=</) && ($_ =~ /smtp/))
        {
                ($tmpString, $to) = split("to=<", $_);
                ($to,$tmpString) = split(">", $to);
                $recipient_emails {$QID} = $recipient_emails {$QID} .  "$to "   ;
                $recipient_count {$QID}++   ;

        }
}

close(LOG);

foreach $myQID (keys %sender_emails)
{
        my %uniqueRcpt  = () ;

        $myto = $recipient_emails{$myQID} ;
        $myfrom = $sender_emails{$myQID} ;
        $tocount = $recipient_count{$myQID} ;

        next if $tocount >= 6;

        @rcpt = split(/\s+/, $myto);

        foreach $myrcpt(@rcpt)
        {
                $uniqueRcpt {$myrcpt} = $myfrom;
        }

        foreach $myrcpt (keys %uniqueRcpt)
        {

                $myfrom = $uniqueRcpt{$myrcpt} ;

                $senderRcptKey =  $myfrom . ":"  . $myrcpt ;

                $uniqueEntries{$senderRcptKey}  = 1 ;
        }
}

$outbound_emails = '/data/whitelisting/outbound_emails';
open(OBE,">$outbound_emails");

foreach $myPair (keys %uniqueEntries)
{

        print $myPair . "\n" ;
        print OBE $myPair . "\n" ;

}

close(OBE);

Its perhaps not the most efficient script but it runs in the middle of the night on mail machines which are behind a load balancer. So I got away with it for now, but I will certainly try to update it when my perl scripting skills have improved. Thanks again for all your help!