Report with number of emails sent and received.

Discuss your pilot or production implementation with other Zimbra admins or our engineers.
diegogts94
Posts: 1
Joined: Mon Dec 18, 2017 5:08 pm

Report with number of emails sent and received.

Postby diegogts94 » Mon Dec 18, 2017 5:28 pm

English--
Friends,
I need to get a Zimbra report from some email users and it should contain the number of emails sent and the number of emails received from each user account.

Português--
Amigos,
preciso tirar um relatório do Zimbra, de alguns usuários de e-mail e nele deve conter o número de e-mails enviados e o número de e-mails recebidos de cada conta de usuário.


lytledd
Outstanding Member
Outstanding Member
Posts: 475
Joined: Sat Sep 13, 2014 12:54 am
ZCS/ZD Version: Release 8.8.9.GA.3019 P4

Re: Report with number of emails sent and received.

Postby lytledd » Wed Dec 20, 2017 9:44 am

Friends,
I need to get a Zimbra report from some email users and it should contain the number of emails sent and the number of emails received from each user account.


I've got a script that does just that. Give me a day or two to clean it up and then I'll post it. Output looks like:

to:support@fakedomain.com
from:summary@fakedomain.com
subject:Account Activity Summary

Account activity summary report 12/20/2017

Program name: mail_summary.sh
Server: myserver.com
Date created: 2012.05.25

Date for query: Dec 19, 2017
____________________________________________________________________


Mail Received: 050 -- Sent: 000 -- E-Mail: dr.white@fakedomain.com

Mail Received: 001 -- Sent: 000 -- E-Mail: deals@fakedomain.com

Mail Received: 034 -- Sent: 001 -- E-Mail: e.alytle@fakedomain.com

Mail Received: 015 -- Sent: 000 -- E-Mail: lytledd@fakedomain.com

Mail Received: 000 -- Sent: 000 -- E-Mail: me@fakedomain.com

Mail Received: 002 -- Sent: 001 -- E-Mail: support@fakedomain.com

Mail Received: 073 -- Sent: 000 -- E-Mail: support2@fakedomain.com

Mail Received: 000 -- Sent: 000 -- E-Mail: w.howard@fakedomain.com



Total accounts in this summary: 8


Doug
User avatar
jorgedlcruz
Zimbra Alumni
Zimbra Alumni
Posts: 2769
Joined: Thu May 22, 2014 4:47 pm

Re: Report with number of emails sent and received.

Postby jorgedlcruz » Wed Dec 20, 2017 12:15 pm

Hello,
If you are looking for a professional way to do it, with reports, etc, take a look at sendmailanalyzer, I wrote about it here:

Best regards
Jorge de la Cruz https://jorgedelacruz.es
Technical Marketing Manager at Zimbra/Synacor https://www.zimbra.com/
lytledd
Outstanding Member
Outstanding Member
Posts: 475
Joined: Sat Sep 13, 2014 12:54 am
ZCS/ZD Version: Release 8.8.9.GA.3019 P4

Re: Report with number of emails sent and received.

Postby lytledd » Fri Dec 22, 2017 11:37 am

I need to get a Zimbra report from some email users and it should contain the number of emails sent and the number of emails received from each user account.


The script will require two files in your work directory.

header

Which is used to send the report to the admin, mine contains:

Code: Select all

to:support@fakedomain.com
from:summary@fakedomain.com
subject:Account Activity Summary


And

addresses

Code: Select all

address1@somedomain.com
address2@somedomain.com
etc.....


Which will contain the addresses that you want a report against

I keep the script itself in my /usr/local/bin

The script needs to be made executable by chmod u+x mail_summary.sh
The script needs to be run as root.
I have mine scheduled as a CRON job.

Code: Select all

0 1 * * * /usr/local/bin/mail_summary.sh lytle


Script is listed below:

Code: Select all

#!/bin/bash

# ------------
# Set up paths
# ------------

PATH=/opt/zimbra/bin:/opt/zimbra/postfix/sbin:/opt/zimbra/openldap/bin:/opt/zimbra/snmp/bin:/opt/zimbra/bdb/bin:/opt/zimbra/openssl/bin:/opt/zimbra/java/bin:/usr/sbin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

# --------------------
# Initialize variables
# --------------------

# -------------------------------------
# Directory for the script's work files
# -------------------------------------
workdir=/home/doug/work

# ---------------------------
# Month to display for report
# ---------------------------
display_month=`date +%b`

# -------------
# Current Month
# -------------
month=`date +%m`

# -----------
# Current Day
# -----------
today=`date +%e`

# -------------
# Yesterday Day
# -------------
day=`date --date=yesterday +%d`

# ------------
# Current year
# ------------
year=`date +%Y`

# -----------------
# Report identifier
# -----------------
report_identifier=$1

# ---------------------------
# Name of server, for reports
# ---------------------------
server=my.server.name

cd /var/log

# --------------------------------------
# If 1st day of the month, set month to
# the previous month.  If it's the first
# of the year and first day of the month
# set year to the previous year
# -----------------------------

if [ "$display_month" == "Jan" ]
then
    echo "Month was hit"
    if [ "$today" == " 1" ]
    then

        display_month=`date --date=yesterday +%b`
        month=`date --date=yesterday +%m`
        year=`date --date=yesterday +%Y`

    fi

        else

    if [ "$today" == " 1" ]
    then
        display_month=`date --date=yesterday +%b`
        month=`date --date=yesterday +%m`
    fi
fi

# --------------------
# Create email headers
# --------------------

cp $workdir/header $workdir/report.$report_identifier

# ------------------
# Creates the report
# ------------------

echo " " >>$workdir/report.$report_identifier
echo "Account activity summary report                             `date +%m/%d/%Y`" >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier
echo "Program name: mail_summary.sh" >>$workdir/report.$report_identifier
echo "Server: $server"  >>$workdir/report.$report_identifier
echo "Date created: 2012.05.25"  >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier
echo "Date for query: $display_month $day, $year"  >>$workdir/report.$report_identifier
echo "____________________________________________________________________" >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier

# -----------------------------------------
# Scan the current and last two Zimbra logs
# for matches, count and total
# ----------------------------

while read addresses
do

   let accounts_listed++

# ------------------------------------------------------------------------------------------------------
# -r (Email received for an address) Counts the number of "Message ID" and assigns the total to received
# -s (Email sent for an address) Counts the number of assp entries and assigns the total to sent
# ----------------------------------------------------------------------------------------------

    received=`/opt/zimbra/libexec/zmmsgtrace zimbra.log.2.gz zimbra.log.1.gz zimbra.log -r "$addresses" --time "$year$month$day,$year$month$day"|grep "Message ID "|wc -l`
    sent=`/opt/zimbra/libexec/zmmsgtrace zimbra.log.2.gz zimbra.log.1.gz zimbra.log -s "$addresses" --time "$year$month$day,$year$month$day"|grep "sig"|uniq|wc -l`

# ----------------------------
# Keep the length of the count
# at 3 digits in length for
# report layout
# -------------

    received_length=`expr length $received`
    sent_length=`expr length $sent`

    if [ $received_length ==  1 ]
    then
        received="00"$received
    fi

    if [ $received_length ==  2 ]
    then
        received="0"$received
    fi

    if [ $sent_length ==  1 ]
    then
        sent="00"$sent
    fi

    if [ $sent_length ==  2 ]
    then
        sent="0"$sent
    fi

echo "Mail Received:  "$received -- "Sent: "$sent -- "E-Mail: "$addresses >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier

# -------------------------
# File containing addresses
# -------------------------

done <$workdir/addresses

echo " " >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier
echo "Total accounts in this summary: "$accounts_listed  >>$workdir/report.$report_identifier
echo " " >>$workdir/report.$report_identifier


# ------------------
# Send out the email
# ------------------

/usr/sbin/sendmail -t </$workdir/report.$report_identifier


Doug

Return to “Administrators”

Who is online

Users browsing this forum: No registered users and 26 guests