user query also returns groups

[deajan]

While developping around grommunio-admin, I noticed that

grommunio-admin user query returns users, but also groups, with no way to differentiate users from groups. Pretty sure that should be a job for grommunio-admin mlist list, getting user list should not include mailing lists IMO.

Using

grommunio-admin-web-3.1.0.36.b3e1844-lp155.109.1.noarch
grommunio-admin-api-1.16.8.ab650a1-lp155.120.1.noarch
grommunio-admin-common-38.f4553bd-lp155.24.1.noarch

[crpb]

getting user list should not include mailing lists IMO.

i know exactly how you feel :rofl: . that mlist was historicaly seen a bit different but i can only concur that could be divided to somethine else.

And the --filter= feature isn't that good at filtering or i just couldn't make out how to do it. I ended up just using bonkers jq with the json-output..
e.g.

grom_doms is a function
grom_doms ()
{
    grommunio-admin domain query domainname # not needed anymore. ->. | sed '/domainname/d'
}
grom_users is a function
grom_users ()
{
    local doms;
    if [[ $# -ge 1 ]]; then
        doms=$(grep "^${1}" <<<"$(grom_doms)");
    else
        doms=$(grom_doms);
    fi;
    for dom in $doms;
    do
        grommunio-admin user query username status maildir --format json-structured | jq -r '.[]|select((.username|endswith("'"${dom}"'")) and (.maildir|.!="") and ((.status==0) or (.status==4)))|.username';
    done
}

grommunio-admin user query returns users, but also groups, with no way to differentiate users from groups.

it will also give you contacts which at least only show up as 'contact-NNN' (when not of your domain) and status 5/contact

You can differentiate when you query ID status maildir username(not really but could help..)

• status: 0/active -> users + groups
  • maildir: users
  • maildir: groups
• status: 4/shared -> shared mailboxes
• status: 5/contact -> contacts

i wrote this thingy once but never extended it to orgs and contacts but that might help ^_^

#!/bin/bash
set -e
set -x

# retrieve mailbox folders, usually /var/lib/gromox/{user,domain}
domainPrefix=$(grommunio-admin config get options.domainPrefix)
userPrefix=$(grommunio-admin config get options.userPrefix)

# retrieve all configured domains
domaindata="$(grommunio-admin domain query --format=csv --separator=';' homedir ID domainname | grep "^${domainPrefix}")"
userdata="$(grommunio-admin user query --format=csv --separator=';' maildir ID username domainID | grep "^${userPrefix}")"
#
# grommunio-admin domain query --format json-structured ID orgID domainname activeUsers address adminName chat chatID displayname domainStatus endDay homedir homeserverID inactiveUsers maxUser tel title virtualUsers
# grommunio-admin org query --format json-structured ID name domainCount description
# grommunio-admin user query --format json-structured ID aliases changePassword chat chatAdmin domainID forward homeserverID lang ldapID maildir pop3_imap privArchive privChat privFiles privVideo publicAddress smtp status username
#
for domain in ${domaindata[@]}; do
  domainID="$(awk -F';' '{print $2}' <<< "$domain")"
  domainname="$(awk -F';' '{print $3}' <<< "$domain")"
  homedir="$(awk -F';' '{print $1}' <<< "$domain")"
  users="$(awk -F';' '$4=='$domainID'' <<< "$userdata")"
  for user in $users; do
    read maildir username <<< $(awk -F';' '{print $1, $3}' <<< "$user")
  done
done

[deajan]

@crpb Thank you for your reply. I too did tinker around with the CLI output. Also noticed that commands like grommunio-admin exmdb <user> store get don't have json output, so it's not that easy to work with, and are painfully slow.

I'm currently making a prometheus exporter for Grommunio. I'll post about it on the forum shortly. Perhaps you want to have a quick look: https://github.com/netinvent/grommunio_exporter/tree/v0.2.1 I'm open to any suggestions.

[crpb]

@deajan depending on if the data is only accessible by people who would be allowed to see sch data i would suggest adding folderstatistics. the data is all in the mail stores exmdb/exchange.sqlite3. i just tinkered with that these days. Still working on it but here is a little teaser

folderstatistics

``` ~gdev/grommunio/scripts/sql (git)-[main] % sqlite3 -json ~/tmp/ex-i.sqlite3 "select id,foldersize,count,name='foo' from folderstatistics;" [{"id":10,"foldersize":31765276,"count":627,"name='foo'":0}, {"id":11,"foldersize":19789555,"count":68,"name='foo'":0}, {"id":12,"foldersize":null,"count":0,"name='foo'":0}, {"id":13,"foldersize":19034672,"count":55,"name='foo'":0}, {"id":14,"foldersize":null,"count":0,"name='foo'":0}, {"id":15,"foldersize":3771270,"count":1183,"name='foo'":0}, {"id":16,"foldersize":null,"count":0,"name='foo'":0}, {"id":17,"foldersize":12153,"count":3,"name='foo'":0}, {"id":18,"foldersize":40680,"count":18,"name='foo'":0}, {"id":19,"foldersize":1185817,"count":417,"name='foo'":0}, {"id":20,"foldersize":null,"count":0,"name='foo'":0}, {"id":21,"foldersize":null,"count":0,"name='foo'":0}, {"id":22,"foldersize":null,"count":0,"name='foo'":0}, {"id":23,"foldersize":99938,"count":2,"name='foo'":0}, {"id":25,"foldersize":null,"count":0,"name='foo'":0}, {"id":26,"foldersize":null,"count":0,"name='foo'":0}, {"id":27,"foldersize":null,"count":0,"name='foo'":0}, {"id":28,"foldersize":null,"count":0,"name='foo'":0}, {"id":29,"foldersize":null,"count":0,"name='foo'":0}, {"id":429,"foldersize":null,"count":0,"name='foo'":0}, {"id":795,"foldersize":null,"count":0,"name='foo'":0}, {"id":819,"foldersize":null,"count":0,"name='foo'":0}, {"id":907,"foldersize":null,"count":0,"name='foo'":0}, {"id":1153,"foldersize":null,"count":0,"name='foo'":0}, {"id":1154,"foldersize":null,"count":0,"name='foo'":0}, {"id":1155,"foldersize":null,"count":0,"name='foo'":0}, {"id":1156,"foldersize":null,"count":0,"name='foo'":0}, {"id":1157,"foldersize":null,"count":0,"name='foo'":0}, {"id":1471,"foldersize":null,"count":0,"name='foo'":0}, {"id":1475,"foldersize":null,"count":0,"name='foo'":0}, {"id":1498,"foldersize":null,"count":0,"name='foo'":0}, {"id":1900545,"foldersize":353759753,"count":7967,"name='foo'":0}, {"id":1910545,"foldersize":null,"count":0,"name='foo'":0}, {"id":1920545,"foldersize":186018868,"count":860,"name='foo'":0}, {"id":1930545,"foldersize":147794,"count":2,"name='foo'":0}, {"id":1940545,"foldersize":null,"count":0,"name='foo'":0}, {"id":1960545,"foldersize":8566752,"count":75,"name='foo'":0}, {"id":1970545,"foldersize":35389366,"count":271,"name='foo'":0}, {"id":1980545,"foldersize":42558612,"count":513,"name='foo'":0}, {"id":1990545,"foldersize":28919666,"count":190,"name='foo'":0}, {"id":2000545,"foldersize":null,"count":0,"name='foo'":0}, {"id":2010545,"foldersize":null,"count":0,"name='foo'":0}, {"id":2020545,"foldersize":554714,"count":103,"name='foo'":0}, {"id":2030545,"foldersize":63280529,"count":330,"name='foo'":0}, {"id":2050545,"foldersize":47277444,"count":145,"name='foo'":0}, {"id":2070545,"foldersize":1415520,"count":28,"name='foo'":0}, {"id":2100545,"foldersize":2609958,"count":30,"name='foo'":0}, {"id":2110545,"foldersize":39909776,"count":168,"name='foo'":0}, {"id":2120545,"foldersize":24512219,"count":87,"name='foo'":0}, {"id":2130545,"foldersize":1508068,"count":19,"name='foo'":0}, {"id":2140545,"foldersize":9903306,"count":49,"name='foo'":0}, {"id":2150545,"foldersize":32792697,"count":43,"name='foo'":0}, {"id":2180545,"foldersize":49494056,"count":55,"name='foo'":0}, {"id":2200545,"foldersize":2195273,"count":16,"name='foo'":0}, {"id":2210545,"foldersize":3616109,"count":78,"name='foo'":0}, {"id":2230545,"foldersize":3478083,"count":35,"name='foo'":0}, {"id":2290545,"foldersize":8623543,"count":5,"name='foo'":0}, {"id":2300545,"foldersize":61508,"count":3,"name='foo'":0}, {"id":2320545,"foldersize":537131789,"count":288,"name='foo'":0}, {"id":2340545,"foldersize":41544,"count":1,"name='foo'":0}, {"id":2390545,"foldersize":15561434,"count":83,"name='foo'":0}, {"id":2400545,"foldersize":4843666,"count":105,"name='foo'":0}, {"id":2410545,"foldersize":null,"count":0,"name='foo'":0}, {"id":2420545,"foldersize":7230795,"count":37,"name='foo'":0}, {"id":2430545,"foldersize":27686,"count":1,"name='foo'":0}, {"id":2440545,"foldersize":13412463,"count":256,"name='foo'":0}, {"id":2490545,"foldersize":938371,"count":10,"name='foo'":0}, {"id":2510545,"foldersize":53777,"count":1,"name='foo'":0}, {"id":2520545,"foldersize":55659506,"count":655,"name='foo'":0}, {"id":2540545,"foldersize":null,"count":0,"name='foo'":0}, {"id":2550545,"foldersize":733195,"count":8,"name='foo'":0}, {"id":2770545,"foldersize":15326,"count":1,"name='foo'":0}, {"id":2800545,"foldersize":14767,"count":1,"name='foo'":0}, {"id":2820545,"foldersize":null,"count":0,"name='foo'":0}, {"id":2820546,"foldersize":null,"count":0,"name='foo'":0}, {"id":3057154,"foldersize":5584178,"count":1,"name='foo'":0}, {"id":3162690,"foldersize":231012,"count":165,"name='foo'":0}, {"id":3212690,"foldersize":533344,"count":11,"name='foo'":0}, {"id":3288226,"foldersize":21397721,"count":4163,"name='foo'":0}, {"id":3368226,"foldersize":38630,"count":9,"name='foo'":0}, {"id":3388226,"foldersize":54417,"count":24,"name='foo'":0}, {"id":3398226,"foldersize":null,"count":0,"name='foo'":0}, {"id":3408226,"foldersize":3144445,"count":7,"name='foo'":0}, {"id":3448226,"foldersize":364215,"count":283,"name='foo'":0}, {"id":3458226,"foldersize":34694,"count":8,"name='foo'":0}, {"id":3468226,"foldersize":80538,"count":2,"name='foo'":0}, {"id":3478226,"foldersize":434663,"count":224,"name='foo'":0}, {"id":3488226,"foldersize":36431,"count":9,"name='foo'":0}, {"id":3498226,"foldersize":1874213,"count":285,"name='foo'":0}, {"id":3659298,"foldersize":20354562,"count":334,"name='foo'":0}, {"id":3669298,"foldersize":23205612,"count":120,"name='foo'":0}, {"id":3689298,"foldersize":120768,"count":4,"name='foo'":0}, {"id":3764834,"foldersize":21425,"count":18,"name='foo'":0}, {"id":3774834,"foldersize":null,"count":0,"name='foo'":0}, {"id":3850371,"foldersize":null,"count":0,"name='foo'":0}, {"id":3860371,"foldersize":null,"count":0,"name='foo'":0}, {"id":3860372,"foldersize":null,"count":0,"name='foo'":0}] ```

[juliaschroeder]

Groups can be excluded from the results by using the --filter mlist= option (yes, with an empty value).
Sorry for the missing documentation, I will add that to the man page.

[deajan]

@juliaschroeder Works for me, thank you. As side question, currently developping a prometheus exporter for grommunio, and I noticed that getting the mailbox sizes and quotas via grommunio-admin exmdb <user> store get is painfully ressource hungry, and doesn't output anything easily parsable. Is there any alternative I could use to gather these information ? (sorry to piggyback this thread)

[crpb]

and doesn't output anything easily parsable.

this reminds me of this :P

trick 17 for silly ["settings":{"zarafa".... explosions

COLUMNS=80 grommunio-admin exmdb user@dom.tld store get

Originally posted by @crpb in 9281282

and you can just do an grommunio-admin exmdb user store get PROPVAL for the single return.

[juliaschroeder]

Initializing the Python environment, especially the SQLAlchemy runtime, takes a lot of time. If possible, you can pre-generate commands and pipe them into grommunio-admin shell, e.g.

grommunio-admin shell -x <<EOF
exmdb user1@example.com store get
exmdb user2@example.com store get
EOF

However, that will make the output even more difficult to work with…

Another factor is that the mailbox access which occurs for every user, which can take some time if the mailbox is not already loaded and has to be opened (by the gromox-http process). Unfortunately, there is nothing that can be done about that overhead.

I will add the options to produce output in JSON and CSV format in the next days to at least make the parsing easier.

[crpb]

grommunio-admin shell -x <<EOF
exmdb user1@example.com store get
exmdb user2@example.com store get
EOF

However, that will make the output even more difficult to work with…

ask the people who only do those shenanigangs 📦 this was just the first try.

echo "exmdb cb@dadada.de store get normalmessagesizeextended" | grommunio-admin shell 2>/dev/null

[deajan]

@crpb @juliaschroeder Thank you.

Currently, I'm parsing exmdb output via awk to produce json output via grommunio-admin exmdb user1@example.com store get | awk ' BEGIN { printf"[" } {if ($1~/^0x/) {next} ; printf"\n%s{\"%s\": \"%s\"}", sep,$1,$2; sep=","} END { printf"]\n"}'. This produces json output, but of course is really not a foolproof solution.

Indeed, having json output would be much easier ^^

@crpb I'm not feeling really comfortable with directly tinkering with the sqllite databases, since everything could break on internal data schema changes. The solution of using one grommunio-admin shell to gather all the info might be the best one.

I've made some tests, getting a single value via store get normalmessagesizeextended or getting all values both take about 0,8s on my test system. Getting 4 users in a single session takes 0,9s, so that's definitly the good way to go. I'll just have to change the awk process to something using regular expressions.

[crpb]

i just got this one

{  grommunio-admin shell -x <<< "exmdb cb@moode store get normalmessagesizeextended" 2>/dev/null; } | sed '1d;/^$/d'

times:

real    0m0.869s
user    0m0.783s
sys     0m0.080s

EDIT: as said before, just query what you want and not the thing w/o a propval

gromi:~ # time {  grommunio-admin shell -x <<< "exmdb cb@as.de store get prohibitreceivequota prohibitsendquota assocmessagesizeextended normalmessagesizeextended" 2>/dev/null; } | sed '1d;/^$/d'
prohibitreceivequota          921600  900 MiB
prohibitsendquota             768000  750 MiB
assocmessagesizeextended      758236  740 kiB
normalmessagesizeextended  456801370  436 MiB

real    0m0.863s
user    0m0.776s
sys     0m0.087s

@crpb I'm not feeling really comfortable with directly tinkering with the sqllite databases, since everything could break on internal data schema changes. The solution of using one grommunio-admin shell to gather all the info might be the best one.

you have seen that it's all temporary tables and -readonly calls?

[deajan]

you have seen that it's all temporary tables and -readonly calls?

Perhaps. But having an API that does the job is enough for me, and doesn't require getting the sqlite file paths ^^

depending on if the data is only accessible by people who would be allowed to see sch data i would suggest adding folderstatistics. the data is all in the mail stores exmdb/exchange.sqlite3. i just tinkered with that these days. Still working on it but here is a little teaser

As a sysadmin, I cannot imagine what the folderstatistics are for an a monitoring tool. What's the goal ? Mine is to know whether a mailbox reaches the quotas, and act before it's too late.

[crpb]

As a sysadmin, I cannot imagine what the folderstatistics are for an a monitoring tool. What's the goal ? Mine is to know whether a mailbox reaches the quotas, and act before it's too late.

it was a thought. i also know people who keep mailboxes very clean and the next maybe wants to know if there is anything Stuck in a process which would usually clear out a folder but didn't and that thing just doesn't warn about it or it just didn't know..

[deajan]

I get the idea... But folderstatistics would add alot of data (one entry per folder name, with every user having it's own idea of folder names I guess).

[deajan]

Initializing the Python environment, especially the SQLAlchemy runtime, takes a lot of time. If possible, you can pre-generate commands and pipe them into grommunio-admin shell, e.g. grommunio-admin shell -x <<EOF exmdb user1@example.com store get exmdb user2@example.com store get EOF

I've just coded something around the shell, and it's pretty fast, but the output is not coherent. I don't get to parse the output via regex because sometimes I get newlines in the middle of data. I've managed to parse the output with some little awk magic. Looking out forward for json support.

As for now, my requests take a couple of seconds regardless of the number of users, although I didn't test with "big" grommunio setups, it should scale better. Again, thanks.

[juliaschroeder]

Hi, I added the format argument to exmdb … store get (23c560ab91cc9afc337a7b3cc4fc921a0554a831). I also added two new formats, json-kv and json-object (9d08ffec03c3abcf7d535908f7d967d9dc9c5f05), because I felt like especially the former would make a lot of sense for the described use case.

[crpb]

@deajan fwiw, that might be faster :p.

gromi:/var/lib/gromox/user/5/2 # grommunio-admin taginfo prohibitreceivequota prohibitsendquota assocmessagesizeextended normalmessagesizeextended
0x666a0003 (1718222851): PROHIBITRECEIVEQUOTA, type LONG
0x666e0003 (1718484995): PROHIBITSENDQUOTA, type LONG
0x66b40014 (1723072532): ASSOCMESSAGESIZEEXTENDED, type LONGLONG
0x66b30014 (1723006996): NORMALMESSAGESIZEEXTENDED, type LONGLONG
gromi:/var/lib/gromox/user/5/2 # time sqlite3 -readonly /var/lib/gromox/user/5/2/exmdb/exchange.sqlite3 'SELECT proptag,propval FROM store_properties WHERE proptag IN (1718222851,1718484995,1723072532,1723006996);'
1718222851|921600
1718484995|768000
1723006996|459164156
1723072532|758312

real    0m0.005s
user    0m0.000s
sys     0m0.005s

[deajan]

@crpb Thanks, but I think using your solution will need to query the maildir location on a per user basis using grommunio-admin, which will make it again slow, as suggested by @juliaschroeder since it will again initialize python + sqlalchemy env per user.

I guess the current implementation is already quite fast (I don't have a "big" grommunio server to test though), but using 0.9 seconds for 4 users instead of 0,8s is pretty decent already, since it would require 0,025s per user in a synthetic benchmark.

I think that once I get to parse the new json output, it will be even faster since I won't rely on terrible awk hack.

Still, I really appreciate your feedback, thank you.

[crpb]

but I think using your solution will need to query the maildir location on a per user basis using grommunio-admin,

just fyi, if it comes to be slow somewhere you would have to do an mysql grommunio --execute'select username,maildir from users' (w/o thinking about any extras here like username/password of course) iirc.

[deajan]

@juliaschroeder LGTM, the new format json-kv format seems pretty nice to parse. I still have to do some awk magic to transform the per user json blocks into a list when using shell EOF method. Would it be complicated to add a grommunio-admin exmdb --all-users store get command that would directly return a list of json objects perhaps ? If not, the awk trick will do ;)