====== Confluence ======

Список пользователей и групп, имеющих права на пространства
<code>
SELECT s.SPACENAME,
       s.SPACEKEY,
       sg.groups,
       GROUP_CONCAT(distinct cu.display_name ORDER BY cu.display_name SEPARATOR ', ') as users
FROM confluence.SPACEPERMISSIONS sp 
JOIN confluence.SPACES s ON s.SPACEID = sp.SPACEID
LEFT OUTER JOIN confluence.user_mapping um ON um.user_key = sp.PERMUSERNAME
LEFT OUTER JOIN confluence.cwd_user cu ON um.username = cu.user_name
LEFT OUTER JOIN (select s.SPACEID, GROUP_CONCAT(distinct sp.PERMGROUPNAME ORDER BY sp.PERMGROUPNAME SEPARATOR ', ') as groups
	FROM confluence.SPACEPERMISSIONS sp
	JOIN confluence.SPACES s ON s.SPACEID = sp.SPACEID
	GROUP BY s.SPACEKEY
	ORDER BY s.SPACEKEY
) sg ON sp.SPACEID = sg.SPACEID
GROUP BY s.SPACEKEY
ORDER BY s.SPACEKEY;
</code>

Список активных пользователей c группами

<code>
SELECT cu.display_name,
       cu.email_address,
       cu.active,
       li.successdate,
       GROUP_CONCAT(cg.group_name ORDER BY cg.group_name SEPARATOR ', ') as groups
FROM confluence.logininfo li
right JOIN confluence.user_mapping um ON um.user_key = li.username
JOIN confluence.cwd_user cu ON um.username = cu.user_name
JOIN confluence.cwd_membership cm ON cu.id = cm.child_user_id
JOIN confluence.cwd_group cg on cm.parent_id = cg.id 
where cu.active = 'T'
group by cu.display_name,
       cu.email_address,
	   cu.active,
       li.successdate
ORDER BY successdate DESC;
</code>

{{tag> confluence }}