How to get a list of users with their last logon times including users from external directories
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
You may need to know the list of active users, including the time of their last login within the last X number of days in Confluence. We already have a knowledge base article that provides this information for users who are part of the internal directory. By using the queries below, you can access details such as last login, last modified, email address, directory membership, and failed login date for users that are part of external directories as well.
Environment
Confluence 7.6 and above
Solution
The below query will return the list of users from all the directories whose last logged in date is with in the X days, replace X with the required number of days
SELECT cu.user_name,
cd.directory_name,
li.successdate,
cu.active,
cu.email_address,
cu.created_date,
cu.updated_date,
li.faileddate
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 >= NOW() - INTERVAL 'X days';The below query will return the list of users from specific directory whose last logged in date is with in the X days, replace X with the required number of days and DIRECTORY NAME with actual directory name
SELECT cu.user_name,
cd.directory_name,
li.successdate,
cu.active,
cu.email_address,
cu.created_date,
cu.updated_date,
li.faileddate
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 directory_name LIKE 'DIRECTORY NAME' AND successdate >= NOW() - INTERVAL 'X days';Related Content
Was this helpful?