A script to review a Zimbra account and it’s folder sizes

I came across stsimb@Github‘s repository zimbra-scripts which had this nice script called zimbra-size.sh. What it does, is somewhat similar to the way Zimbra list an accounts folders in zmmailbox.

Zimbra provides the following using zmmailbox:

zimbra@pikachu:~$ zmmailbox -z -m philip@digno.no gaf
        Id  View      Unread   Msg Count  Path
----------  ----  ----------  ----------  ----------
         1  unkn           0           0  /
        16  docu           0           0  /Briefcase
        10  appo           0         222  /Calendar
        14  mess           0           0  /Chats
       259  unkn           0           0  /Comments
         7  cont           0           0  /Contacts
....

This is actually a really nice overview, however, it does not list the size of the folders. At work, as a large ISP, often customers have a quota and does not understand what is consumption all the storage. To be able to generate an overview that is fast to produce, and does not require the admin to log into their account and view their mail, this script does the job. The only thing that is exposed from the account is the folder names.

An example of output is shown here, for the same account as above:

zimbra@pikachu:/tmp$ ./zimbra-size.sh philip@digno.no
philip@digno.no's max mailbox size = 0 MB, current mailbox size = 1.33 GB.

size (MB)  msgcount     unread folder
--------- --------- ---------- ----------------------------
        0         0          0 /Chats
      730      7544        878 /Inbox
....

The script can be found at my Github page, and is displayed below:

#!/bin/bash
# script to report zimbra mailbox size per folder for a specific user
# based on info at http://www.zimbra.com/forums/administrators/23655-per-folder-size-command-line.html#post121758
# stsimb feb 2014

PATH=/opt/zimbra/bin:/bin:/usr/bin

if [ "$(id -un)x" != "zimbrax" ]; then
  echo "Fatal error: This script needs to run as user zimbra."
  exit 1
fi

if [ $# == 0 ] ; then
  echo "Report zimbra mailbox size per folder for a specific user"
  echo
  echo "Usage = $0 username"
  echo 
  exit 1
fi

USER=$1

backend="$(zmprov ga ${USER} zimbraMailHost | tail -2 | awk '{print $2}')"
if [ "${backend}x" != "$(zmhostname)x" ]; then
  echo "Fatal error: need to run on ${backend} for ${USER}."
  exit 1
fi

quota="$(expr `zmprov ga ${USER} zimbraMailQuota | tail -2 | awk '{print $2}'` / 1024 / 1024)"
size="$(zmmailbox -z -m ${USER} gms)"
echo "${USER}'s max mailbox size = ${quota} MB, current mailbox size = ${size}."
echo

TF=$(mktemp)
zimbraID="$(zmprov ga ${USER} zimbraID | tail -2 | awk '{print $2}')"
rm -f ${TF}
zmmailbox -z -m ${USER} gaf | grep mess | egrep -v "\(.*@.*:2\)" > ${TF}
echo "size (MB)  msgcount     unread folder"
echo "--------- --------- ---------- ----------------------------"
while read line ; do
  folder="$(echo ${line} | awk '{print $5,$6,$7,$8,$9}')"
  msgcount="$(echo ${line} | awk '{print $4}')"
  unread="$(echo ${line} | awk '{print $3}')"
  fid=$(echo ${line} | awk '{print $1}')
  if [ ! -z "${fid##*[!0-9]*}" ]; then
    if [ "x${msgcount}" != "x0" ]; then
      mboxinfo=$(mysql -N -e "select id, group_id from zimbra.mailbox where account_id=\"${zimbraID}\"")
      mboxid=$(echo ${mboxinfo} | awk '{print $1}')
      gid=$(echo ${mboxinfo} | awk '{print $2}')
      info=$(mysql -N -e "select size, metadata from mboxgroup${gid}.mail_item where mailbox_id=${mboxid} and id=${fid}")   
      size=$(echo ${info} | egrep -o ":szi.*:" | cut -d: -f2 | cut -c 4- | sed -e 's/e4$//')
      sizeMB="$(expr ${size} / 1024 / 1024 2>/dev/null)" # || echo 0)"
    else
      sizeMB="0"
    fi
    printf "%9s %9s %10s " ${sizeMB} ${msgcount} ${unread}
    echo ${folder}
#  else
#    echo "${folder} is shared, skipping"
  fi
done < ${TF}
rm -f ${TF}