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

How to get a list of users with their last logon times