How to get list of admins for each space

Hello,

im trying to get list of admin for each space. But without success.

This is my inspiration: http://come2niks.com/?p=821

I using sql plugin with

SELECT DISTINCT T2.SPACENAME, T1.PERMUSERNAME, T1.CREATOR FROM confluence.spacepermissions T1 JOIN confluence.Spaces T2 ON T1.SPACEID=T2.SPACEID

Result is
| Application | user2 | user1|
| Application | user3 | user2|
| Application | | user3|
| Application | | admin|

user1 created user2
user2 created user3
In Space admin > Advanced i get information "Created By: admin (22. 08. 2008)".

My question is what does mean 3rd line?

If you solved or solving similar task i will appreciated for some tips or advices.

Thank you

2 answers

1 accepted

1 vote
Accepted answer
SELECT spacename, wm_concat(DISTINCT(ADMIN)) ADMIN FROM
(SELECT T2.spacename, T1.PERMUSERNAME ADMIN FROM confluence.spacepermissions T1
JOIN
confluence.Spaces T2 ON T1.SPACEID=T2.SPACEID
WHERE T2.spacekey NOT LIKE '~%'
UNION ALL
SELECT T2.spacename, T1.CREATOR ADMIN FROM confluence.spacepermissions T1
JOIN
confluence.Spaces T2 ON T1.SPACEID=T2.SPACEID
WHERE T2.spacekey NOT LIKE '~%')
GROUP BY SPACENAME
ORDER BY SPACENAME

Without users spaces (NOT LIKE '~%')

you should have mentioned that this code is for Oracle DB

1 vote

The third line in your results is telling you when the space was created, and by whom.

I think your SQL isn't quite right though, I think it'll be fetching information about individual users who have admin rights and have created a space. That won't capture the current space admins, especially if you use any groups in the permissions.

I'm not that familiar with the database though, I have to admit to writing a few lines of code in a small plugin to list users and groups with admin rights (provided a macro to display them)

Ok, and how do you explained me this (is impossible that 3 peoples created same space)

Application | | johnos
Application | | weveros
Application | | enrico
Joe Clark Atlassian Team Dec 19, 2011

It looks to me like it is referring to the user who created the permission, not the user who created the space.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 24, 2018 in Confluence

Atlassian Research opportunity with Confluence templates

Do you use templates with Confluence? Take part in a remote 1-hr workshop. You'll receive USD $100 for your time!   We're looking for people to participate in a   remote 1-hr workshop...

1,103 views 17 14
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you