Sql Query to find Confluence Space Admins.

Hello Friends,

This is one of the tutorials regarding the Confluence usage. Following is the query to find the admins of the confluence spaces on Confluence.

firstly we find the space admins from admin groups:

CREATE TABLE audit AS (SELECT GROUP_CONCAT(os_user.username) USER, SPACES.SPACEID,SPACENAME, PERMGROUPNAME
FROM SPACEPERMISSIONS, SPACES, os_group , os_user_group , os_user
WHERE PERMTYPE LIKE 'SETSPACEPERMISSIONS'
AND SPACEPERMISSIONS.SPACEID = SPACES.SPACEID
AND SPACETYPE LIKE 'global'
AND PERMGROUPNAME IS NOT NULL
AND os_group.groupname = SPACEPERMISSIONS.PERMGROUPNAME
AND os_user_group.group_id = os_group.id
AND os_user_group.user_id = os_user.id

GROUP BY SPACENAME
)

CREATE TABLE audit AS (SELECT GROUP_CONCAT(os_user.username) USER, SPACES.SPACEID,SPACENAME, PERMGROUPNAME

FROM SPACEPERMISSIONS, SPACES, os_group , os_user_group , os_user

WHERE PERMTYPE LIKE 'SETSPACEPERMISSIONS'

AND SPACEPERMISSIONS.SPACEID = SPACES.SPACEID

AND SPACETYPE LIKE 'global'

AND PERMGROUPNAME IS NOT NULL

AND os_group.groupname = SPACEPERMISSIONS.PERMGROUPNAME

AND os_user_group.group_id = os_group.id

AND os_user_group.user_id = os_user.id

GROUP BY SPACENAME

)

now we merge the individual space admin users with the above list

SELECT SPACEPERMISSIONS.SPACEID , audit.SPACENAME , CONCAT(audit.USER, ',' , IFNULL(PERMUSERNAME, '')) USERS FROM SPACEPERMISSIONS , audit
WHERE
PERMTYPE LIKE 'SETSPACEPERMISSIONS'
AND audit.SPACEID = SPACEPERMISSIONS.SPACEID

GROUP BY SPACENAME

SELECT SPACEPERMISSIONS.SPACEID , audit.SPACENAME , CONCAT(audit.USER, ',' , IFNULL(PERMUSERNAME, '')) USERS FROM SPACEPERMISSIONS , audit

WHERE

PERMTYPE LIKE 'SETSPACEPERMISSIONS'

AND audit.SPACEID = SPACEPERMISSIONS.SPACEID

GROUP BY SPACENAME

Hope this will help you in searching the admins from all the spaces on confluence.

Thanks,

Admin.

One thought on “Sql Query to find Confluence Space Admins.”

Hey Nice article nicks.
Why you have stopped backlinking ? i have not seen your links in Buzz and FB.
Keep it up…

Best Regards,
Jitendra Zaa

One thought on “Sql Query to find Confluence Space Admins.”

Leave a Reply Cancel reply