[SOLVED] Looking for reporting output of names, accounts, lists, alias, etc.

Is there any fast/easy way to get a list of users, Distro lists and who's in them, alias' etc.

I just want something that I can output to excel/word etc. (even a text file)

I know I can get this data from LDAP but it's not pretty and not really worth my time to look up a whole bunch of ldap queries.

Big thanks!!!

Moe

Welcome to the forums,

Code:

---

mkdir /opt/zimbra/info
chown zimbra.zimbra /opt/zimbra/info
su - zimbra
cd info

---

To get a printout of all your accounts:

Code:

---

zmprov gaa > accounts.txt

---

or

Code:

---

zmaccts | grep "@" | awk '{print $1}' > accounts.txt

---

or

Code:

---

/opt/zimbra/bin/zmaccts | grep 'active' | egrep -v '^\W+' | awk '{print $1}'

---

or

Code:

---

/opt/zimbra/openldap/bin/ldapsearch -LLL -x -D"`/opt/zimbra/bin/zmlocalconfig -s zimbra_ldap_userdn | \
      awk '{print $3}'`" -w"`/opt/zimbra/bin/zmlocalconfig -s zimbra_ldap_password | \
      awk '{print $3}'`" -H `/opt/zimbra/bin/zmlocalconfig ldap_url | \
      awk '{print $3}'` $* | \
      grep ^mail | \
      awk '{print $2}' | \
      sort > zimbra_recipients.list

---

Exporting all addresses - Zimbra :: Wiki

To get a printout of all your distribution lists:

Code:

---

zmprov gadl > alllists.txt

---

To print out the members for a given distribution list:

Code:

---

zmprov gdl dist-list@domain.com > dist-list.txt

---

To show if that particular distribution list is a member of other distribution lists:

Code:

---

zmprov gdlm dist-list@domain.com > dist-list-membership.txt

---

Bug 19157 - Ability to export GAL as a CSV file (admin console)
You can use awk & print to do so from CLI.
For instance:

Code:

---

zmprov gdl dist-list@domain.com | awk 'ORS=","' > list.csv

---

So no way to get aliases?

Thanks for the info.

While you could:

Code:

---

#!/bin/bash

for i in `zmprov gaa`
do
  echo $i
  zmprov ga $i | grep zimbraMailAlias
done

---

Combining gaa -v and grep can be very useful.

This will give you a list like gaa except just of aliases.

Code:

---

zmprov gaa -v | grep -e zimbraMailAlias | awk '{print$2}' > aliases.txt

---

However I gather you want to know who the aliases belong to:

Code:

---

zmprov gaa -v | grep -e name -e zimbraMailAlias > allemails.txt

---

Combining grep and awk can arrange and spit out desired lines in the format of your choosing.

For instance this will put all your accounts in a csv format:

Code:

---

zmprov gaa | awk 'ORS=","' > accounts.csv

---

(Don't know what program you're exporting to but if it wants an extra space just do 'ORS=", "')

> location.file needs to be a place that the zimbra user can write to/or browse there first.


Another example

Code:

---


zmprov getAllAccounts -v |
    grep -e name -e zimbraMailAlias |
    awk '{
        if (/^# name/) {
            name=$3
        } else if (/^zimbraMailAlias/) {
            print $2 ": " name
        }
    }'

---

Still another:

Code:

---

#!/bin/bash

SEARCHSTRING="user@domain.tld"

for list in `zmprov gadl`
do
  results=`zmprov gdl $list | grep "Address" | cut -d " " -f 2 | grep "$SEARCHSTRING"`
  if [ ! -z $results ]
  then
    echo $list - $results
  fi
done

---

By COS, first get the cos id that you want:
zmprov gac -v | grep -e cn: -e zimbraId
or zmprov gc COSname

Then get all accounts with that COS:
zmprov gaa -v | grep -e uid: -e zimbraCOSId | grep -B1 putzimbraCOSidStringHERE | grep uid: | awk '{print $2}'

OR

zmprov gc <COSName> | grep zimbraId
zmprov sa zimbraCOSId=string

or zmprov sa zimbraCOSId=string > file.txt

(viewing-most likely in columns) Bug 18779 - Sorting User Accounts by COS.
(command line viewing) Bug 16185 - RFE: Get all COS users
(gui) Bug 3373 - bulk move of users from one COS to another
Bug 14266 - Extend search among users to COS

Admin console - first open the COS and copy where it says "ID: string"
Then type the following in the admin console search bar:
(zimbraCOSID=string)

There's also this bug: Bug 29763 - Missing zimbraCOSId when set to auto
-create domain.com assign it COS1
-create test@domain.com cos set to auto > doesn't show zimbraCOSId
-create test2@domain.com cos set to default > doesn't show zimbraCOSID
-create test3@domain.com cos set to COS1 > shows zimbraCOSId
-or someone said set the COS on the domain and it works

When creating accounts you should use:
zmprov gc <COSName> | grep zimbraId
zmprov ca name@domain.com <password> zimbraCOSId <zimbraIdNumberStringCOS>

SA is handy:

Quote:

---

Originally Posted by mmorse

zmprov searchAccounts [-v] {ldap-query} [limit {limit}] [offset {offset}] [sortBy {attr}] [attrs {a1,a2...}] [sortAscending 0|1*] [domain {domain}]

While:

Code:

---

zmprov sa -v zimbraCOSId=string | grep uid

---

Gives:
uid: username

And:

Code:

---

zmprov sa zimbraCOSId=string

---

Gives:
username@domain.com

But for:

Code:

---

zmprov sa zimbraCOSId=string attrs uid

---

It still gives you:
username@domain.com

And should have given:
username

Code:

---

zmprov sa -v zimbraCOSId=string attrs uid

---

With -v it's a huge list outputted to the console, however -v is supposed to be used there anyways so that it can dump attributes.

The command was never corrected/closed out as wontfix: Bug 12759 - zmprov searchAccounts does not return requested attrs
-I suppose if it doesn't work (though it would be useful) we should remove the [attrs {a1,a2...}] from the help :p

---

FANTASTIC!!!:)

Works like a charm.

PERFECT!

Thank you!! Took me a while to dig up this solution, but it was exactly what I needed :)

This works beautifully!

Is there a way to print a summary of each account and/or domain to find the total amount of storage used?

Any plans on bringing some kind of reporting functionality to the admin UI in the future?

Quote:

---

Originally Posted by mlanner

Is there a way to print a summary of each account and/or domain to find the total amount of storage used?

---

Grand summary by domain:

Code:

---

zmprov gqu `zmhostname` | grep domain.com | awk '{ sum += $3; } END { print sum; }'

---

Walkthrough:

Navigate to a directory writable by zimbra, or create one (so that we can use > & < out/in to a file later).

CLI:

Quote:

---

zmprov GetQuotaUsage localhost

---

Spits it out in bytes. Second column is max quota (0=unlimited). Third column will be used amount.

Quote:

---

user@domain.com 0 1234

---

To flip the output order could do something like:

Code:

---

zmprov gqu `zmhostname` | awk {'print " "$3" "$2" "$1'} > sizes.txt

---

Quote:

---

1234 0 user@domain.com

---

SOAP GetQuotaUsageRequest

Code:

---

zmsoap -z -v -e GetQuotaUsageRequest domain=domain.com

---

GetQuotaUsageResponse is still per user, just limited search to domain.

Quote:

---

<GetQuotaUsageResponse xmlns="urn:zimbraAdmin" searchTotal="#" more="0">
<account name="user@domain.com" id="string" limit="0" used="265941" ></account>

---

But combining grep for domain with awk, plus another awk to sum the numbers using something like:

Quote:

---

awk '{ sum += $1; } END { print sum; }' < sizes.txt

---

And all together you get:

Code:

---

zmprov gqu `zmhostname` | grep domain.com | awk {'print " "$3" " '} > sizes.txt
awk '{ sum += $1; } END { print sum; }' < sizes.txt

---

Simply remove | grep domain.com if you want everyone.

Combined onto one line you can find out how much space everyone's taking up (well keep in mind there's single-instance storage for those on the same mailstore):

Code:

---

zmprov gqu `zmhostname` | awk '{ sum += $3; } END { print sum; }'

---

Quote:

---

Originally Posted by mlanner

Any plans on bringing some kind of reporting functionality to the admin UI in the future?

---

Admin console: Server Statistics > Server > Mailbox Quota tab ?

Looking for better search on quota/by domain in the admin console?
There's some RFE's open like Bug 17343 - domain statistics & Bug 13419 - Admin>ServerStatistics>MailboxQuota: cant easily search/view quota of an account check them/vote/file away.

Awesome Mike! Thanks so much. I'll be testing this shortly. Will become very handy very fast. :)

Mike,

do you know of an efficient way to get:

* for all accounts

* from all address books

* all email addresses (up to 3 per contact)

My current script is not just damn slow, but causes hiccups on the server possibly due to the sheer amount of java processes fired up:

Code:

---

ACCOUNTS=$($ZMPROV -l gaa)

# FIXME: getting contacts through zmmailbox is damn slow.........
(for acct in $ACCOUNTS; do ( sleep 2 && $ZMMBOX -z -m "$acct" gact email{,1,2} ) ; done) \
        | grep -P 'email[123]?:' \
        | awk '{ print $2 " custom_sender_whitelisted"}' \
        | tr "[:upper:]" "[:lower:]" | sort -u \
        >> $CONF/custom_sender_zimbra_whitelist.tmp

---