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

moethegreat · Post by **moethegreat** » Thu Jul 10, 2008 12:18 pm

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

mmorse · Post by **mmorse** » Thu Jul 10, 2008 11:59 pm

Welcome to the forums,
mkdir /opt/zimbra/info

chown zimbra.zimbra /opt/zimbra/info

su - zimbra

cd infoTo get a printout of all your accounts:zmprov gaa > accounts.txtor

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

or

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

or

/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">http://wiki.zimbra.com/index.php?title= ... >Exporting all addresses - Zimbra :: Wiki
To get a printout of all your distribution lists:

zmprov gadl > alllists.txt

To print out the members for a given distribution list:

zmprov gdl dist-list@domain.com > dist-list.txtTo show if that particular distribution list is a member of other distribution lists:

zmprov gdlm dist-list@domain.com > dist-list-membership.txtBug">http://bugzilla.zimbra.com/show_bug.cgi?id=19157>Bug 19157 - Ability to export GAL as a CSV file (admin console)

You can use awk & print to do so from CLI.

For instance:

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

moethegreat · Post by **moethegreat** » Fri Jul 11, 2008 12:45 pm

So no way to get aliases?
Thanks for the info.

mmorse · Post by **mmorse** » Fri Jul 11, 2008 6:12 pm

While you could:

#!/bin/bash
for i in `zmprov gaa`

do

echo $i

zmprov ga $i | grep zimbraMailAlias

doneCombining gaa -v and grep can be very useful.
This will give you a list like gaa except just of aliases.

zmprov gaa -v | grep -e zimbraMailAlias | awk '{print$2}' > aliases.txtHowever I gather you want to know who the aliases belong to:

zmprov gaa -v | grep -e name -e zimbraMailAlias > allemails.txtCombining 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: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

zmprov getAllAccounts -v |

grep -e name -e zimbraMailAlias |

awk '{

if (/^# name/) {

name=$3

} else if (/^zimbraMailAlias/) {

print $2 ": " name

}

}'Still another:

#!/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

doneBy 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 | grep zimbraId

zmprov sa zimbraCOSId=string
or zmprov sa zimbraCOSId=string > file.txt

(viewing-most likely in columns) Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=18779>Bug 18779 - Sorting User Accounts by COS.

(command line viewing) Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=16185>Bug 16185 - RFE: Get all COS users

(gui) Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=3373>Bug 3373 - bulk move of users from one COS to another

Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=14266>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">http://bugzilla.zimbra.com/show_bug.cgi?id=29763>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 | grep zimbraId

zmprov ca name@domain.com zimbraCOSId
SA is handy:

[quote user="mmorse"]zmprov searchAccounts [-v] {ldap-query} [limit {limit}] [offset {offset}] [sortBy {attr}] [attrs {a1,a2...}] [sortAscending 0|1*] [domain {domain}]
While:

zmprov sa -v zimbraCOSId=string | grep uidGives:

uid: username
And:

zmprov sa zimbraCOSId=stringGives:

username@domain.com
But for:

zmprov sa zimbraCOSId=string attrs uidIt still gives you:

username@domain.com
And should have given:

username
zmprov sa -v zimbraCOSId=string attrs uidWith -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">http://bugzilla.zimbra.com/show_bug.cgi?id=12759>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[/quote]

moethegreat · Post by **moethegreat** » Mon Jul 14, 2008 7:50 am

FANTASTIC!!!:)
Works like a charm.

3545jallen · Post by **3545jallen** » Mon Aug 11, 2008 2:15 pm

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

mlanner · Post by **mlanner** » Mon Feb 02, 2009 12:34 pm

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?

mmorse · Post by **mmorse** » Mon Feb 02, 2009 1:30 pm

[quote user="mlanner"]Is there a way to print a summary of each account and/or domain to find the total amount of storage used?[/quote]

Grand summary by domain:

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 > &
CLI:

[quote] zmprov GetQuotaUsage localhost[/quote]Spits it out in bytes. Second column is max quota (0=unlimited). Third column will be used amount.

[quote]user@domain.com 0 1234[/quote]To flip the output order could do something like:

zmprov gqu `zmhostname` | awk {'print " "$3" "$2" "$1'} > sizes.txt[quote]1234 0 user@domain.com[/quote]SOAP GetQuotaUsageRequest

zmsoap -z -v -e GetQuotaUsageRequest domain=domain.comGetQuotaUsageResponse is still per user, just limited search to domain.

[quote]

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

[quote] awk '{ sum += $1; } END { print sum; }'

zmprov gqu `zmhostname` | grep domain.com | awk {'print " "$3" " '} > sizes.txt

awk '{ sum += $1; } END { print sum; }'
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):

zmprov gqu `zmhostname` | awk '{ sum += $3; } END { print sum; }'[quote user="mlanner"]Any plans on bringing some kind of reporting functionality to the admin UI in the future?[/quote]

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">http://bugzilla.zimbra.com/show_bug.cgi?id=17343>Bug 17343 - domain statistics & Bug">http://bugzilla.zimbra.com/show_bug.cgi?id=13419>Bug 13419 - Admin>ServerStatistics>MailboxQuota: cant easily search/view quota of an account check them/vote/file away.

mlanner · Post by **mlanner** » Mon Feb 02, 2009 1:56 pm

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

4140jens · Post by **4140jens** » Sat Jul 04, 2009 1:43 am

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:
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