How to gets list of users which never login to confluence and consume a licence

[serge calderara]

Hello,

I am looking for a way to list all users in confluence which never login and consume a licence

I have tried the following query :

SELECT cu.display_name, cu.email_address, cu.active,
cd.directory_name,
li.SUCCESSDATE
FROM logininfo li
right JOIN user_mapping um ON um.user_key = li.USERNAME
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.id
ORDER BY SUCCESSDATE;

But it returns all active users with last login date and never loggin

My issue is that I can have some user which are active in confluence but not allowed to use confluence because not belonging to proper group

How to get from querry only users which never login and consume a licence count ?

Thnaks

[Raja Shekher · Accepted solution]

Hi @serge calderara - You can find the information documented here: https://confluence.atlassian.com/confkb/how-to-check-for-users-who-never-logged-into-confluence-777015610.html

Additional info: https://confluence.atlassian.com/confkb/how-to-identify-inactive-users-in-confluence-214335880.html

Regards,

Raja

[serge calderara]

@Raja Shekher , I have try that already but it dows not work as expected beceause of the follwoing .

It returns as well active users which do not consume a licence

What I need is only active users which never logs and which are counted as a licence user

any idea?

[Raja Shekher]

Hi @serge calderara - Please try this https://confluence.atlassian.com/confkb/how-to-get-a-list-of-active-users-counting-towards-the-confluence-license-298978076.html

In the Where Condition - Try to pass something like this based on the Date Range that you are looking for.

WHERE successdate < '2016-01-01'

Regards,

Raja

[Raja Shekher]

Hi @serge calderara - Here the customized Query for your use case. Let me know how it goes.

SELECT cu.user_name,
cd.directory_name,
li.successdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.ID
WHERE successdate < (CURRENT_DATE - 180)
ORDER BY successdate;

SELECT cu.user_name,
cd.directory_name,
li.successdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.ID
JOIN cwd_membership m ON cu.id = m.child_user_id
JOIN cwd_group g ON m.parent_id = g.id
WHERE g.group_name = 'confluence-users' AND successdate < (CURRENT_DATE - integer '180') AND (cu.active = 'T' and cu.directory_id=557057 and cu.user_name !~ '@|srv_|ADMIN|SVC|CQIMPORT')
ORDER BY successdate;