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
)

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

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

Thanks,

Admin.