Page 1 of 2

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

Posted: Thu Jul 10, 2008 12:18 pm
by moethegreat
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

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

Posted: Thu Jul 10, 2008 11:59 pm
by mmorse
Welcome to the forums,
mkdir /opt/zimbra/info

chown zimbra.zimbra /opt/zimbra/info

su - zimbra

cd info
To get a printout of all your accounts:
zmprov gaa > accounts.txt
or

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>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.txt
To show if that particular distribution list is a member of other distribution lists:

zmprov gdlm dist-list@domain.com > dist-list-membership.txt
Bug">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

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

Posted: Fri Jul 11, 2008 12:45 pm
by moethegreat
So no way to get aliases?
Thanks for the info.

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

Posted: Fri Jul 11, 2008 6:12 pm
by mmorse
While you could:

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

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

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

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

uid: username
And:

zmprov sa zimbraCOSId=string
Gives:

username@domain.com
But for:

zmprov sa zimbraCOSId=string attrs uid
It still gives you:

username@domain.com
And should have given:

username
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">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]

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

Posted: Mon Jul 14, 2008 7:50 am
by moethegreat
FANTASTIC!!!:)
Works like a charm.

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

Posted: Mon Aug 11, 2008 2:15 pm
by 3545jallen
PERFECT!
Thank you!! Took me a while to dig up this solution, but it was exactly what I needed :)

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

Posted: Mon Feb 02, 2009 12:34 pm
by mlanner
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?

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

Posted: Mon Feb 02, 2009 1:30 pm
by mmorse
[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.com
GetQuotaUsageResponse 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.

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

Posted: Mon Feb 02, 2009 1:56 pm
by mlanner
Awesome Mike! Thanks so much. I'll be testing this shortly. Will become very handy very fast. :)

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

Posted: Sat Jul 04, 2009 1:43 am
by 4140jens
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