How to get list of admins for each space

[Jiří Wetter]

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

[Jiří Wetter · Accepted solution]

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

[Jiří Wetter]

Without users spaces (NOT LIKE '~%')

[Andrei [errno]]

you should have mentioned that this code is for Oracle DB

[Nic Brough -Adaptavist-]

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)

[Jiří Wetter]

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

Application	| |	johnos
Application	| |	weveros
Application	| |	enrico

[Joe Clark]

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