Zimbra offers Open Source email server software and shared calendar for Linux and the Mac
 
Go Back   Zimbra - Forums > Zimbra Collaboration Suite > Administrators

Welcome to the Zimbra - Forums!
Welcome, if you would like to post a comment please register. We also encourage you to explore all things Zimbra with our team and members of the community.

Reply
 
LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 07-10-2008, 11:18 AM
New Member
 
Posts: 3
Default [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
Reply With Quote
  #2 (permalink)  
Old 07-10-2008, 10:59 PM
Zimbra Consultant
 
Posts: 5,814
Default

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
__________________
-Mike Morse (MCode151)

ZCS-to-ZCS Migrations & Moves | Admin Tools & Tidbits » ZimbraBlog.com | ZimbraCommunity.com

Last edited by mmorse : 03-08-2009 at 07:16 PM. Reason: single quotes disappeared on paste
Reply With Quote
  #3 (permalink)  
Old 07-11-2008, 11:45 AM
New Member
 
Posts: 3
Default

So no way to get aliases?

Thanks for the info.
Reply With Quote
  #4 (permalink)  
Old 07-11-2008, 05:12 PM
Zimbra Consultant
 
Posts: 5,814
Default

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 View Post
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
__________________
-Mike Morse (MCode151)

ZCS-to-ZCS Migrations & Moves | Admin Tools & Tidbits » ZimbraBlog.com | ZimbraCommunity.com

Last edited by mmorse : 03-24-2009 at 07:25 PM.
Reply With Quote
  #5 (permalink)  
Old 07-14-2008, 06:50 AM
New Member
 
Posts: 3
Thumbs up

FANTASTIC!!!

Works like a charm.
Reply With Quote
  #6 (permalink)  
Old 08-11-2008, 01:15 PM
Intermediate Member
 
Posts: 19
Default

PERFECT!

Thank you!! Took me a while to dig up this solution, but it was exactly what I needed
Reply With Quote
  #7 (permalink)  
Old 02-02-2009, 11:34 AM
Loyal Member
 
Posts: 99
Default Getting storage used per account or domain?

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?
Reply With Quote
  #8 (permalink)  
Old 02-02-2009, 12:30 PM
Zimbra Consultant
 
Posts: 5,814
Default

Quote:
Originally Posted by mlanner View Post
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 View Post
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.
__________________
-Mike Morse (MCode151)

ZCS-to-ZCS Migrations & Moves | Admin Tools & Tidbits » ZimbraBlog.com | ZimbraCommunity.com

Last edited by mmorse : 02-06-2009 at 02:24 PM.
Reply With Quote
  #9 (permalink)  
Old 02-02-2009, 12:56 PM
Loyal Member
 
Posts: 99
Default

Awesome Mike! Thanks so much. I'll be testing this shortly. Will become very handy very fast.
Reply With Quote
  #10 (permalink)  
Old 07-04-2009, 12:43 AM
Member
 
Posts: 12
Default Get email adresses from all contacts

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
Reply With Quote
Reply


Thread Tools
Display Modes


Similar Threads

Why Join?

Registering let's you ask questions, makes it easier to search, displays any files attached to posts, and notifies you about replies.

Zimbrablog.com




 

Search Engine Optimization by vBSEO 3.1.0