How to identify inactive users in Confluence

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

If you want to disable inactive users to prevent them from being counted towards a Confluence license, it is possible to find out by running SQL queries against your database. This is particularly useful if you have a large number of users.

This page contains outdated information relating to Confluence Server & Data Center. For a more updated KB, please visit How to get a list of users with their last logon times.

Solution

Note: If you are using Confluence 3.5 or later, the queries are the same regardless of which user management system you're using. See the How Do I Identify Inactive Users in Confluence or How Do I Identify Inactive Users in Confluence versions of this document for the various queries needed for legacy user management systems.

You may need to modify these queries for your particular database. Please check the casing of the database tables, as this may need to be adjusted in the SQL queries.

List users who are inactive

1
2
3

SELECT *
FROM cwd_user
WHERE active = 'F';

List active users who have not logged in since a specific date

For Confluence 4.0.x - 5.1.x:
1 2 3 4 5SELECT username, successdate FROM logininfo WHERE successdate < '2016-01-01' ORDER BY successdate;
For Confluence 5.2.x and above:
1 2 3 4 5 6 7 8 9SELECT 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 < '2016-01-01' ORDER BY successdate;
To get the date from 180 days ago, use this one (unless using MS SQL, see below):
1 2 3 4 5 6 7 8 9SELECT 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 - integer '180') ORDER BY successdate;
To get the date from 180 days ago, using MS SQL, use:
1 2 3 4 5 6 7 8 9SELECT 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 li.SUCCESSDATE < (getdate() - 180) ORDER BY li.SUCCESSDATE;
⚠️ Please note that MS SQL statements are case sensitive.

List users by last login date

For Confluence 3.5.x only:
1 2 3 4 5 6SELECT ENTITY_NAME, DATE_VAL FROM OS_PROPERTYENTRY WHERE ENTITY_KEY='confluence.user.last.login.date' AND ENTITY_NAME LIKE 'CWD%' ORDER BY DATE_VAL;
For Confluence 4.0.x - 5.1.x:
1 2 3 4SELECT username, successdate FROM logininfo ORDER BY successdate;
For Confluence 5.2.x and above:
1 2 3 4 5 6 7 8SELECT 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 ORDER BY successdate;

List users by previous login date

The "previous" login date is the one before the user's last login.

For Confluence 4.0.x - 5.1.x:
1 2 3 4SELECT username, prevsuccessdate FROM logininfo ORDER BY prevsuccessdate;
For Confluence 5.2.x and above:
1 2 3 4 5 6SELECT cu.user_name, li.PREVsuccessdate FROM logininfo li JOIN user_mapping um ON um.user_key = li.username JOIN cwd_user cu ON um.username = cu.user_name ORDER BY PREVsuccessdate;

Active users who have not created any content (page, blog, or comment) since a specific date

These accounts are still active, but the users themselves may no longer be using Confluence.

For Confluence 4.0.x - 5.1.x:
1 2 3 4 5 6 7 8SELECT user_name FROM cwd_user WHERE user_name NOT IN ( SELECT CREATOR FROM CONTENT WHERE CONTENTTYPE IN ('PAGE','BLOGPOST','COMMENT') AND CREATIONDATE > '2007-01-01') AND active = 'T';
For Confluence 5.2.X and above:
1 2 3 4 5 6 7 8 9SELECT cu.user_name FROM cwd_user cu JOIN user_mapping um ON um.username = cu.user_name WHERE um.user_key NOT IN ( SELECT CREATOR FROM CONTENT WHERE CONTENTTYPE IN ('PAGE','BLOGPOST','COMMENT') AND CREATIONDATE > '2007-01-01' AND CREATOR IS NOT NULL) AND cu.active = 'T';

Identify when your users logged into Confluence for the last time

For Confluence 5.2.X and above:

User base last login date

WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    AND to_timestamp(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name 
     , g.group_name
     , l.last_login
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 WHERE g.group_name = '<group-name>' -- for instance, 'confluence-users'
 ;

List users that count towards the License Count but have never logged in:

For Confluence 5.2.x and above:

Valid Users that never logged 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' AND u.lower_user_name NOT IN (
	SELECT cu.lower_user_name
	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
)
GROUP BY u.lower_user_name, d.directory_name
ORDER BY d.directory_name;

For Oracle

In Oracle, the queries above might fail as it is not able to process the dates correctly.

If the original query fails on Oracle, change any mention of dates from '2007-01-01', to to_date('01-JAN-2007','DD-MON-YYYY').

For example, the query below,

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 < '2016-01-01'
ORDER BY successdate;

should be as such.

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 < to_date('01-JAN-2016','DD-MON-YYYY')
ORDER BY successdate;

Updated on April 2, 2025

Was this helpful?

It wasn't accurateIt wasn't clearIt wasn't relevant

Atlassian Support

How to identify inactive users in Confluence

Summary

Solution

Still need help?