How to List Users That Have Space Administrator Privileges with category also

Question

I am using this https://confluence.atlassian.com/confkb/how-do-i-view-a-list-of-all-space-administrators-for-all-spaces-346325879.html  in order to have the list of admins in our confluence.

SELECT DISTINCT s.spaceid,
s.spacekey,
s.spacename,
u.lower_username AS individual_lower_username,
cu.lower_user_name AS group_member_lower_username,
sp.permgroupname AS groupname,
cg.local AS local_group,
cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
AND sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY spacekey,groupname;

I want to use this query, but I am missing one thing, the category. can you assist on adding it ?

I wan also to get the list of categories. using postgres.

Thanks,

Shelly

unknown · Answer

Hi @Srinatha Tondihal ,

When I navigate inside confluence to Space Directory, I can see:

Space Name

Space Description

Space Categoreis

See screen shot:

I would like to get a report that provides:

Spaceid

spacekey

spacename

individual_lower_username

group_member_lower_username

groupname

local_group

group_directory_name

I am using this postgres query:

SELECT DISTINCT s.spaceid,
s.spacekey,
s.spacename,
u.lower_username AS individual_lower_username,
cu.lower_user_name AS group_member_lower_username,
sp.permgroupname AS groupname,
cg.local AS local_group,
cd.directory_name AS group_directory_name
FROM spaces AS s
JOIN spacepermissions AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_directory AS cd ON cg.directory_id = cd.id
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE s.spacestatus = 'CURRENT'
AND sp.permtype = 'SETSPACEPERMISSIONS'
ORDER BY spacekey,groupname;

What I am missing here is the space category.

Hope its clear.

Thanks,

Shelly

Srinatha Tondihal · Answer

Hi @[deleted],

Welcome to Atlassian community.

What exactly you mean by categories? I am not very clear about that, If you can explain what exactly the details you are trying to capture. I will try to customise the above query accordingly.

Have a good day!

Thanks,

Srinath T