nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Raja Shekher

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

All comments

Raja Shekher

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.

<span>WHERE</span> successdate <span><</span> <span>'2016-01-01'</span>

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;

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server