nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Thank you

Accepted answers

Jiří Wetter

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

All comments

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.

Jiří Wetter

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

cloud

confluence-cloud

report

confluence-administration