How to find out what communities a user owns

Posted on by

Today there was a question in the Connections on Premises community where a company wanted to know which communities one of their Connections admins, who had left the company, owned. There’s not much special here about this person being a Connections admin, except that a Connections admin usually owns *a lot* of communities. The question therefore could be more generic to, how to find out what communities and files a user owns.

For normal users, to get a list of communities they are a member of, there’s a wsadmin command to answer this question:

execfile(“communitiesAdmin.py”)

CommunitiesService.fetchCommByMemberEmail(String email)
or
CommunitiesService.fetchCommByMemberUuid(String uuid)

This gives a list of all communities this user is a member of. It can be used, but due to the fact that the community description is also in there, it’s actually not that useful. If you have a user that’s a member of a really huge amount of communities you might get this message:

WASX7015E: Exception running command: "CommunitiesService.fetchCommByMember("user@company.com")"; exception information:
javax.management.RuntimeMBeanException java.lang.RuntimeException: java.lang.RuntimeException: com.ibatis.dao.client.DaoException: java.sql.SQLException: Error: executeQueryForObject returned too many results

The way around both problems is to query the SQL database directly:

In the SNCOMM Database:

SELECT  SNCOMM.MEMBER.COMMUNITY_UUID AS COMMUNITY_UUID, 
	SNCOMM.COMMUNITY.NAME AS COMMUNITY_NAME, 
	SNCOMM.MEMBERPROFILE.DISPLAY AS MEMBER_NAME, 
	 CASE SNCOMM.MEMBER.ROLE 
		WHEN 0 THEN 'Member' 
		WHEN 1 THEN 'Owner' 
	 END AS MEMBER_ROLE 
 FROM SNCOMM.MEMBER
 LEFT OUTER JOIN SNCOMM.MEMBERPROFILE ON SNCOMM.MEMBER.MEMBER_UUID = SNCOMM.MEMBERPROFILE.MEMBER_UUID
 LEFT OUTER JOIN SNCOMM.COMMUNITY ON  SNCOMM.COMMUNITY.COMMUNITY_UUID =  SNCOMM.MEMBER.COMMUNITY_UUID
 WHERE SNCOMM.MEMBERPROFILE.DISPLAY LIKE 'Martijn%Jong'

This outputs a table with the community id, Community name, Display name of the user and whether the user is a member or owner of this community. Thanks to Sharon James for the question and Martin Leyrer for the initial query.