How to identify inactive users in Confluence

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

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.

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 Confluence 3.5 or Confluence 3.4 and below 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. 

List users who are inactive

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:

    SELECT username,
           successdate
    FROM logininfo
    WHERE successdate < '2016-01-01'
    ORDER BY successdate;
  • For Confluence 5.2.x and above:

    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;

    To get the date from 180 days ago, use this one:

    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 - integer '180')
    ORDER BY successdate;


List users by last login date

  • For Confluence 3.5.x only:

    SELECT 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:

    SELECT username,
           successdate
    FROM logininfo
    ORDER BY successdate;
  • For Confluence 5.2.x and above:

    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
    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:

    SELECT username,
           prevsuccessdate
    FROM logininfo
    ORDER BY prevsuccessdate;
  • For Confluence 5.2.x and above:

    SELECT 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:

    SELECT 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:

    SELECT 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 cu.active = 'T';

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;
Last modified on Dec 26, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.