Page 1 of 2 12 LastLast
Results 1 to 10 of 18

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

  1. #1
    Join Date
    Jul 2008
    Posts
    3
    Rep Power
    7

    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

  2. #2
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    21

    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
    Last edited by mmorse; 03-08-2009 at 06:16 PM. Reason: single quotes disappeared on paste

  3. #3
    Join Date
    Jul 2008
    Posts
    3
    Rep Power
    7

    Default

    So no way to get aliases?

    Thanks for the info.

  4. #4
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    21

    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
    Last edited by mmorse; 03-24-2009 at 06:25 PM.

  5. #5
    Join Date
    Jul 2008
    Posts
    3
    Rep Power
    7

    Thumbs up

    FANTASTIC!!!

    Works like a charm.

  6. #6
    Join Date
    Aug 2007
    Posts
    19
    Rep Power
    8

    Default

    PERFECT!

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

  7. #7
    Join Date
    Nov 2007
    Location
    Berkeley, CA
    Posts
    115
    Rep Power
    7

    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?

  8. #8
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    21

    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:
    zmprov GetQuotaUsage localhost
    Spits it out in bytes. Second column is max quota (0=unlimited). Third column will be used amount.
    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
    SOAP GetQuotaUsageRequest
    Code:
    zmsoap -z -v -e GetQuotaUsageRequest domain=domain.com
    GetQuotaUsageResponse is still per user, just limited search to domain.
    <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:
    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.
    Last edited by mmorse; 02-06-2009 at 01:24 PM.

  9. #9
    Join Date
    Nov 2007
    Location
    Berkeley, CA
    Posts
    115
    Rep Power
    7

    Default

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

  10. #10
    Join Date
    May 2009
    Posts
    13
    Rep Power
    6

    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

Similar Threads

  1. [SOLVED] error on creating short email alias
    By stock99 in forum Administrators
    Replies: 1
    Last Post: 12-27-2007, 08:47 PM
  2. Replies: 13
    Last Post: 10-18-2007, 09:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •