How do you return a list of space admins through the database?
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 +
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.