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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.