I am trying to build out a query that counts the number of users who can access each Space in Confluence. So far I have the following:
SELECT DISTINCT s.spacename as Space,
COUNT(u.display_name is not null) as All_Users
FROM spacepermissions sp
JOIN spaces s ON s.spaceid = sp.spaceid
JOIN cwd_group g ON sp.permgroupname = g.group_name
JOIN cwd_membership m ON g.id = m.parent_id
JOIN cwd_user u ON u.id = m.child_user_id
WHERE sp.permgroupname IN
(
SELECT g.group_name
FROM cwd_group g
)
AND u.lower_user_name is not null AND u.active = 'T'
GROUP BY s.spacekey;
The numbers I'm getting back are too large and I believe that is because the query is pulling in duplicate names and including them in the count. Is there a way to fix this query to ignore duplicate names? Or is there a way to do this outside of a query?
