How do you return a list of space admins through the database?

[Michael S]

How do you return a list of space admins through the database?

[twong_atlassian · Accepted solution]

Possible untested improvement:

SELECT
    s.spacename AS "Space Name",
    s.spacekey  AS "Space Key",
    u.user_name AS "Admin User Name"
FROM
    cwd_user u
JOIN
    user_mapping um
ON
    u.user_name=um.username
JOIN
    cwd_membership cm
ON
    u.id=cm.child_user_id
JOIN
    cwd_group g
ON
    cm.parent_id = g.id
JOIN
    spacepermissions sp
ON
    um.user_key=sp.permusername
OR  sp.permgroupname=g.lower_group_name
JOIN
    spaces s
ON
    sp.spaceid=s.spaceid
WHERE
    sp.permtype='SETSPACEPERMISSIONS'
GROUP BY
    1,2,3
ORDER BY
    s.spacekey

I think this should result in pulling in the group memberships also. Only for 5.2.x +

[Mikhail T]

Thanks! This was a great start for me... The problem I encountered was that in our DB at least (MySQL) the SPACEPERMISSIONS and SPACES table-names are capitalized. Also, I used MySQL's GROUP_CONCAT function to improve the result, getting comma-separated lists of both admin-names and admin e-mail addresses:

SELECT
    s.spacename AS "Space Name",
    s.spacekey  AS "Space Key",
    GROUP_CONCAT(DISTINCT u.display_name SEPARATOR ', ') AS "Administrator(s)",
    GROUP_CONCAT(DISTINCT u.email_address SEPARATOR ', ') AS "Administrator email address(es)"
FROM
    cwd_user u
JOIN
    user_mapping um
ON
    u.user_name=um.username
JOIN
    cwd_membership cm
ON
    u.id=cm.child_user_id
JOIN
    cwd_group g
ON
    cm.parent_id = g.id
JOIN
    SPACEPERMISSIONS sp
ON
    um.user_key=sp.permusername
OR  sp.permgroupname=g.lower_group_name
JOIN
    SPACES s
ON
    sp.spaceid=s.spaceid
WHERE
    sp.permtype='SETSPACEPERMISSIONS'
GROUP BY
    1,2
ORDER BY
    s.spacekey

[Michael S]

NOTE: These are for individually assigned space permissions only.

In Confluence 5.2.x+:

select s.spacename as "Space Name", 
        s.spacekey as "Space Key",
        u.user_name as "Admin User Name"
from cwd_user u
join user_mapping um
        on u.user_name=um.username
join spacepermissions sp
        on um.user_key=sp.permusername
join spaces s
        on sp.spaceid=s.spaceid
where sp.permtype='SETSPACEPERMISSIONS'
order by s.spacekey;

In Confluence 3.5 - 5.1.x:

select s.spacename as "Space Name", 
        s.spacekey as "Space Key",
        u.user_name as "Admin User Name"
from cwd_user u
join spacepermissions sp
        on u.user_name=sp.permusername
join spaces s
        on sp.spaceid=s.spaceid
where sp.permtype='SETSPACEPERMISSIONS'
order by s.spacekey;