Hi @ukhan ,
Welcome to Atlassian community.
So you want to list all users counting for the license ? if that is your requirement below is the query.
SELECT COUNT(distinct cwd_user.user_name),
cwd_group.group_name
FROM cwd_group
INNER JOIN cwd_membership ON cwd_membership.parent_id= cwd_group.id
INNER JOIN cwd_user ON cwd_user.id = cwd_membership.child_user_id
WHERE cwd_user.active = 'T'
AND cwd_user.lower_user_name IN
(SELECT u.lower_user_name
FROM cwd_user u
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d ON u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE'
AND u.active = 'T'
AND d.active = 'T'
GROUP BY u.lower_user_name,
d.directory_name)
GROUP BY cwd_group.group_name
ORDER BY COUNT(cwd_user.user_name) DESC;
Thanks,
Srinath T
@ukhan , Its tricky . I will let you know if i had any success in accomplishing the above requirement.
Thanks,
Srinath T
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.