Query for licensed users

Question

Hi,

I am looking for a query that shows all the users who accessed one space and no other, ever since they were created or within a span of time (6 months).

Srinatha Tondihal · Answer

@ukhan  , Its tricky . I will let you know if i had any success in accomplishing the above requirement.

Thanks,

Srinath T

ukhan_c4i63n · Answer

That is correct, but you are missing the main requriement....which users accessed a specifc space such as "HR Department" and no other space.

Srinatha Tondihal · Answer

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