Page 1 of 1

How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 8:57 am
by mr00t
hello everyone,

when I examined the zimbra.log file, I noticed that a user sent 75,000 emails. "cat /var/log/zimbra.log | grep -i" sasl_username "| awk '{print $ 9}' | sort | uniq -c | sort -n
"I don't see this user when I use it. I see it in the zimbra.log file

When I searched the zimbra.log file, I noticed that he added 50 people to the "to" section and sent mail. so I have to do a filtering so that the user can calculate the total number of mail ("to" + "cc") cents.
Is it possible to do so? can you help me?
The script you wrote for sasl_username was successful, but does not find the user making the spam.

my script:

Code: Select all

#!/bin/sh

now=$(date)
value=`cat exclude.txt`
cat /var/log/zimbra.log | sed -n 's/.*sasl_username=//p' | sort | uniq -c | sort -nr > spamMailList.txt
filePath="spamMailList.txt"
while  IFS= read -r line;
do
 
IFS=' ' read -r -a array <<< "$line  sed 's,^ *,,; s, *$,,'" 
 
if [ ${array[0]} -gt 100 ]
then
        if  !(grep -q "${array[1]}" <<< "${value[@]}")
        then
                if  !(grep -q "@" <<< "${array[1]}")
                then
                array[1]="${array[1]}@domain.com"
                fi
        su - zimbra -c "zmprov modifyAccount ${array[1]} zimbraAccountStatus closed"
#      perl pfdel.pl ${array[1]}
        echo  "${array[1]}" >> exclude.txt
      echo "# $now - ${array[1]}  mail 'Closed'  queue 'cleaned' " >>log.txt
        fi
fi
done < "$filePath"


Thank you.

Re: How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 11:09 am
by phoenix
Why don't you start with the ZCS version (always post these details) by giving the full output of the following command:

Code: Select all

zmcontrol -v


Have you checked if your server is an open relay and have you checked if it's compromised (there's a forum thread on that topic)?

Re: How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 11:21 am
by mr00t
hi,
sorry I added the version information.
zimbra version : Release 8.8.15_GA_3869.RHEL7_64_20190917004220 RHEL7_64 FOSS edition, Patch 8.8.15_P2

Re: How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 3:22 pm
by pup_seba
Hi,

I don't have a straight answer to that, but I remember when I was playing around with elastic+zimbra, that the easiest way I've found was to convert the number under "nrcpt" to an integer and add it up. https://github.com/Zimbra-Community/zimbra-elasticstack

Also, maybe a good way would be to just use policyD to limit the number of mails a user can send per time? I also remember that you are able to see the total amount of mails in the db from policyD, which may be easier than parsing zimbra.log.

When something like that happens, I usually use "postqueue -p" to try to find out who is sending the mails. I do the parsing manually (mostly greps and cut) and then just clean all those queues once I've got the message IDs.

Again, not a straight answer...just ideas. Hope it helps you somehow.

Good luck!

Re: How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 4:39 pm
by axslingr
The daily mail report to admin shows the top 50 senders by message count.

Lance

Re: How do I calculate the number of emails a user sends?

Posted: Wed Nov 13, 2019 8:41 pm
by JDunphy
mr00t wrote:hello everyone,

when I examined the zimbra.log file, I noticed that a user sent 75,000 emails. "cat /var/log/zimbra.log | grep -i" sasl_username "| awk '{print $ 9}' | sort | uniq -c | sort -n
"I don't see this user when I use it. I see it in the zimbra.log file


See if this gets you any closer.

Code: Select all

grep -i RelayedOutbound /var/log/zimbra.log | grep -v dkim_s | awk '{printf "sender %s recipients: %s\n",$14,$16}'

Also... zimbra's zmdailyreport calls pflogsumm.pl so you might look at that for ideas. I don't think zmdailyreport is what you want because it includes the top senders for external users also.

Code: Select all

%/opt/zimbra/common/bin/pflogsumm.pl -d today /var/log/maillog

I tried something a little different by looking at /var/log/maillog looking for bounces to detect compromised accounts or accounts sending lots of email. I only print out the account names if they have more than 20 bounces. I haven't seen any compromised accounts but I do get a heads up when one of our users does a mailing to their contacts list. :-)

Jim

Re: How do I calculate the number of emails a user sends?

Posted: Thu Nov 14, 2019 2:08 am
by JDunphy
Commands above into small perl script that sorts by the sender and how many email's they sent by counting recipients. By default it will use all your zimbra.log* files so switch that out if you only want todays. Tested on 8.7.11 when you digitally sign your email and have clamav enabled. On our servers, we see the sending entry twice so we ignore the duplicates (dkim lines).

Code: Select all

#!/usr/bin/perl
#
# usage: totalEmail.pl
# summary: count the number of recipients that each accounts has sent email to
#

%sender_list = ();  #ip list

chdir "/var/log";
for (glob 'zimbra.log*')
#for (glob 'zimbra.log')
{

  # audit.log is always todays stuff
  #print "***** Opening file $_","\n";
  if ($_ eq 'zimbra.log')
  {
     $audit_log = 1;
     open (IN, sprintf("cat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }
  else
  {
     $audit_log = 0;
     open (IN, sprintf("zcat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }

  while (<IN>)
  {
   if (m#RelayedOutbound#)
   {
      my $recipcnt = 0;

      next if (m#dkim_s#);   # messasges are listed twice (first via clamav then dkim signed)

      ($sender, $recipients) = m#[^<]+<([^>]+)>[^<]+(.*)\s+Queue-ID#;
                $recipcnt = $recipients =~ tr/,/,/;
      $sender_list{$sender} += $recipcnt;   # count number or recipients

      #print "sender $sender, recipients $recipients count: $sender_list{$sender}\n";
   }
   }
close (IN);
}

# print out totals per sender
printSenders();

sub printSenders
{
   my $sender = ();

   for $sender (sort {$sender_list{$b} <=> $sender_list{$a}} keys %sender_list)
   {
      print "$sender sent: $sender_list{$sender}\n";
   }
}

output:

Code: Select all

relay3:~:41> totalEmail.pl
user3@example.com sent: 189
user1@example.com sent: 124
user2@example.com sent: 63
user0@example.com sent: 37
admin@example.org sent: 16
user4@example.com sent: 11
user5@example.com sent: 1

Another method of watching unusual activity for user accounts. Possible enhancements - add a count threshold where you only print the totals or do some action after this condition is met.

HTH,

Jim

Re: How do I calculate the number of emails a user sends?

Posted: Thu Nov 14, 2019 6:32 am
by mr00t
hi,
Firstly, thank you.
I started the script. Result: Successful. but I see the number the user sends. (sent=1, recipients=0) there is no number of recipients in it. my user sent one mail. fifty people added to the receiver part. in total
I need to see 51 e-mails. I see one. How can I see the sum of sending and receiving.


JDunphy wrote:Commands above into small perl script that sorts by the sender and how many email's they sent by counting recipients. By default it will use all your zimbra.log* files so switch that out if you only want todays. Tested on 8.7.11 when you digitally sign your email and have clamav enabled. On our servers, we see the sending entry twice so we ignore the duplicates (dkim lines).

Code: Select all

#!/usr/bin/perl
#
# usage: totalEmail.pl
# summary: count the number of recipients that each accounts has sent email to
#

%sender_list = ();  #ip list

chdir "/var/log";
for (glob 'zimbra.log*')
#for (glob 'zimbra.log')
{

  # audit.log is always todays stuff
  #print "***** Opening file $_","\n";
  if ($_ eq 'zimbra.log')
  {
     $audit_log = 1;
     open (IN, sprintf("cat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }
  else
  {
     $audit_log = 0;
     open (IN, sprintf("zcat %s |", $_))
       or die("Can't open pipe from command 'zcat $filename' : $!\n");
  }

  while (<IN>)
  {
   if (m#RelayedOutbound#)
   {
      my $recipcnt = 0;

      next if (m#dkim_s#);   # messasges are listed twice (first via clamav then dkim signed)

      ($sender, $recipients) = m#[^<]+<([^>]+)>[^<]+(.*)\s+Queue-ID#;
                $recipcnt = $recipients =~ tr/,/,/;
      $sender_list{$sender} += $recipcnt;   # count number or recipients

      #print "sender $sender, recipients $recipients count: $sender_list{$sender}\n";
   }
   }
close (IN);
}

# print out totals per sender
printSenders();

sub printSenders
{
   my $sender = ();

   for $sender (sort {$sender_list{$b} <=> $sender_list{$a}} keys %sender_list)
   {
      print "$sender sent: $sender_list{$sender}\n";
   }
}

output:

Code: Select all

relay3:~:41> totalEmail.pl
user3@example.com sent: 189
user1@example.com sent: 124
user2@example.com sent: 63
user0@example.com sent: 37
admin@example.org sent: 16
user4@example.com sent: 11
user5@example.com sent: 1

Another method of watching unusual activity for user accounts. Possible enhancements - add a count threshold where you only print the totals or do some action after this condition is met.

HTH,

Jim

Re: How do I calculate the number of emails a user sends?

Posted: Thu Nov 14, 2019 6:56 am
by mr00t
hi JDunphy,
thank you for your interest.

output:
sender <user@domain.com> recipients: <abc@domain.com>,<abc1@domain.com>....<abc50@domanin.com>
sender <user@domain.com> recipients: <xxxx@domain.com>,<yyyy@domain.com>....<ttttt@domanin.com>
sender <userX@domain.com> recipients: <xxxx@domain.com>,<yyyy@domain.com>
sender <userX@domain.com> recipients: <1@domain.com>,<1@domain.com>,<3@domanin.com>
...
*sender = 1 recipient = 50 for the first line

but I need the total number of recipients of the sender.
I need to give you an example:

user@domain.com sent: 100
userX@domain.com sent: 5

it should look at all lines and give the total number of mails for each user, including the recipient list.
Is it possible!

Thank you.
JDunphy wrote:
mr00t wrote:hello everyone,

when I examined the zimbra.log file, I noticed that a user sent 75,000 emails. "cat /var/log/zimbra.log | grep -i" sasl_username "| awk '{print $ 9}' | sort | uniq -c | sort -n
"I don't see this user when I use it. I see it in the zimbra.log file


See if this gets you any closer.

Code: Select all

grep -i RelayedOutbound /var/log/zimbra.log | grep -v dkim_s | awk '{printf "sender %s recipients: %s\n",$14,$16}'

Also... zimbra's zmdailyreport calls pflogsumm.pl so you might look at that for ideas. I don't think zmdailyreport is what you want because it includes the top senders for external users also.

Code: Select all

%/opt/zimbra/common/bin/pflogsumm.pl -d today /var/log/maillog

I tried something a little different by looking at /var/log/maillog looking for bounces to detect compromised accounts or accounts sending lots of email. I only print out the account names if they have more than 20 bounces. I haven't seen any compromised accounts but I do get a heads up when one of our users does a mailing to their contacts list. :-)

Jim

Re: How do I calculate the number of emails a user sends?

Posted: Thu Nov 14, 2019 1:35 pm
by JDunphy
mr00t wrote:output:
sender <user@domain.com> recipients: <abc@domain.com>,<abc1@domain.com>....<abc50@domanin.com>
sender <user@domain.com> recipients: <xxxx@domain.com>,<yyyy@domain.com>....<ttttt@domanin.com>
sender <userX@domain.com> recipients: <xxxx@domain.com>,<yyyy@domain.com>
sender <userX@domain.com> recipients: <1@domain.com>,<1@domain.com>,<3@domanin.com>
...

Your grep output is different than what I see here... The perl script takes into account what that grep was outputing and counts ','s to determine number of recipients. Guess I should have counted '@'s. :-) My output looks like the following:

Code: Select all

% grep -i RelayedOutbound /var/log/zimbra.log | grep -v dkim_s | awk '{printf "sender %s recipients: %s\n",$14,$16}'
sender <user1@example.com> recipients: <user1@gmail.com>,<user2@example.com>,

You provided sample output above without any trailing ','s for the recipients when you ran the grep.

So I don't think my recommendations will work with whatever version of zimbra you are running given what you are explaining. We are also running clamav and DKIM. It isn't clear what you are doing as you haven't mentioned zimbra release either at this point. Maybe back to looking at the nrcpt as Sebastian recommended and count those given that method would also work.

It isn't clear to me why the perl script didn't work in your environment as it did here but most likely the log format is different in your environment than here. If you run this and provide a few lines, I could take a look. I would also appreciate if you told me the version of zimbra you are running.

Code: Select all

%  grep -i RelayedOutbound /var/log/zimbra.log | tail -5

You could also help yourself debug this by uncommenting this statement to see if they are populated correctly and the regular expression is working.

Code: Select all

 #print "sender $sender, recipients $recipients count: $sender_list{$sender}\n";
 

It's a pretty simple perl script with not much logic to it. parse the lines with RelayedOutbound, don't count twice by ignoring lines with dkim_s in them, count recipients for each match, add accumulating count to sender. Print senders by ordering highest to lowest after parsing the entire file[s]... so it is holding all the data prior to printing and not printing sequentially as it goes through the file. The debug line above will print sequentially however. That would tell you if its parsing and counting correctly.

Jim