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

Discuss your pilot or production implementation with other Zimbra admins or our engineers.
mr00t
Posts: 5
Joined: Tue Nov 12, 2019 7:12 am

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

Postby mr00t » Wed Nov 13, 2019 8:57 am

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.
Last edited by mr00t on Wed Nov 13, 2019 11:23 am, edited 1 time in total.


phoenix
Ambassador
Ambassador
Posts: 26567
Joined: Fri Sep 12, 2014 9:56 pm
Location: Liverpool, England

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

Postby phoenix » Wed Nov 13, 2019 11:09 am

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)?
Regards

Bill

Rspamd: A high performance spamassassin replacement

Per ardua ad astra
mr00t
Posts: 5
Joined: Tue Nov 12, 2019 7:12 am

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

Postby mr00t » Wed Nov 13, 2019 11:21 am

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
User avatar
pup_seba
Outstanding Member
Outstanding Member
Posts: 687
Joined: Sat Sep 13, 2014 2:43 am
Location: Tarragona - Spain
Contact:

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

Postby pup_seba » Wed Nov 13, 2019 3:22 pm

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!
User avatar
axslingr
Advanced member
Advanced member
Posts: 195
Joined: Sat Sep 13, 2014 2:20 am
ZCS/ZD Version: 8.8.15_GA_3829.RHEL7_64_20190718141

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

Postby axslingr » Wed Nov 13, 2019 4:39 pm

The daily mail report to admin shows the top 50 senders by message count.

Lance
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 512
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 8.7.11_P14 RHEL6 Network Edition
Contact:

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

Postby JDunphy » Wed Nov 13, 2019 8:41 pm

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
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 512
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 8.7.11_P14 RHEL6 Network Edition
Contact:

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

Postby JDunphy » Thu Nov 14, 2019 2:08 am

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
mr00t
Posts: 5
Joined: Tue Nov 12, 2019 7:12 am

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

Postby mr00t » Thu Nov 14, 2019 6:32 am

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
mr00t
Posts: 5
Joined: Tue Nov 12, 2019 7:12 am

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

Postby mr00t » Thu Nov 14, 2019 6:56 am

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
User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 512
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 8.7.11_P14 RHEL6 Network Edition
Contact:

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

Postby JDunphy » Thu Nov 14, 2019 1:35 pm

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

Return to “Administrators”

Who is online

Users browsing this forum: No registered users and 12 guests