How to get a numeric count of users who can access each Space in Confluence

Mike August 23, 2023

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 = m.parent_id
JOIN cwd_user u ON = 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 = '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?

0 answers

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events