Query for licensed users

[ukhan]

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 T]

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]

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 T]

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

Thanks,

Srinath T