Query to create report of all groups and their membership

[Adam Porad]

Is there a query we can run in the database to create a report (or list) all the groups and their membership?

Also is there away to determine which groups are Global and which are space specific (please excuse me if that is a novice question. Our primary Confluence admin has left the company, and I've taken over temporarily.)?

I tried using the User List Macro but it got error Error: Rendering this content exceeded the timeout of 120 seconds. on a few of the groups I tried testing with. I assume that is because those were large groups.

Also I'd like to be able to re-run this query so I can monitor group creation/usage.

Thanks

[Bhushan Nagaraj · Accepted solution]

This should give you a list of all the groups with related users.

select c.group_name, u.user_name 

from dbo.cwd_membership m

inner join dbo.cwd_group c 

on m.parent_id = c.id

inner join dbo.cwd_user u

on u.id = m.child_user_id

order by c.group_name