How to find out how many team areas each user is a member of?
Accepted answer
1. Try this REST API call:
https://SERVER/ccm/rpt/repository/foundation?fields=foundation/teamArea/(itemId|qualifiedName|teamMembers/(itemId|name))&size=100000
The output will be:
- For each team area (by itemId and qualified name)
-- list all users (by itemId and name)
2. Save the output XML to a file "UserData.xml"
3. The top of the XML output will be a link to the "next" page which I expect will be empty. If not, follow that link and concatenate the result to "UserData.xml". Repeat if needed.
4. Then run this on a Linux or Cygwin command line:
cat UserData.xml | sed 's/teamMembers><itemId>/@/g' | tr '@' '\n' | cut -d '<' -f 1 | sort | uniq -c | sort
The output will be number of team areas for each user itemId. Example:
1 _CqtAAGaLEeSzX8ALDnR3YA
3 _NCdyQHYnEeSnLYxGo5NAEw
5 _19zx0HCqEeSSpfO5JdNebQ
6 _2ArfoXCqEeSSpfO5JdNebQ
5. You can search the file UserData.xml for ItemIds to match them to user, OR open any user in jts/admin and then replace the itemId at the end of the URL with one found this way, to map the itemId to a user identity.
Comments
To make it a bit more "user friendly", probably we can just do a simple search in Chrome? I know some users are just scared of Linux and/or command lines.
So basically, just use the said URL in Chrome, then in the response find the user name of interest, say "John Smith", and search the term "<name>John Smith</name>" after pressing Crtl+F. Chrome will tell you how many matches found.
1 vote
Thank you Donald, good ideas. In fact a user could quite easily import this into Excel and create Pivot charts in three simple steps:
1. Replace all occurrences of this string with a newline: teamMembers><itemid>
2. Replace all <> brackets with comma
3. Import as CSV in Excel
But my customer's goal was to find out if there are any users who are members of an unusually high number of team areas, so they needed to summarize somehow.