Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,293,568
Community Members
 
Community Events
165
Community Groups

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

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

 

1 answer

0 votes
Srinatha T Atlassian Team Apr 26, 2022

Hi @Shelly Gez ,

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 T ,

 

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

Space Name

Space Description

Space Categoreis

See screen shot:

image.png

 

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

751 views 5 21
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you