Identify users in Jira Data Center who haven't logged in for the past 90 days

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.

Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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

To better manage your license count, at some point you may be interested in verifying which of your existing users are actively contributing on Jira, and which users have not logged in for a period of time.

Currently, Jira does not yet offer a report to check for user inactivity on the UI natively. However, a feature request for this functionality to be implemented in the future already exists on JRASERVER-2841 - Getting issue details... STATUS , so you may vote and keep a watch on this Feature Request page for further developments on this area.

In the meantime, while this functionality is not implemented and bundled to the Jira UI, we can extract this specific information directly from the Jira database using the following SQL queries, depending on your current DB and Jira version.
(info) The queries below are pre-configured with a 90-day interval, but you can adjust the '90' days value in the following section for each database query version:

  • Postgres:  current_date - 90 
  • MySQL: (current_date - interval 90 day)
  • MSSQL: DATEADD(d, -90, CONVERT(date, getdate()))
  • Oracle: to_date(SYSDATE - 90, 'DD-MON-YY')

(warning) If the "Last Login" column displays a null value, that means that this specific user has never logged in.
(warning) If the user has a "Remember Me" token set, then the Last Login date will not reflect the last time the user accessed JIRA, but will instead show the last time they had to go through the login process. See: JRA-60508 for details.

This SQL query may not return accurate results if you are using nested groups in LDAP or Crowd, or if you have users with duplicated usernames across multiple directories. Therefore, in case of discrepancy, consider the license count given in the Jira user interface instead. 

This SQL query will also return any user that was created within the last 90 days but never logged in.  If you would like to remove newly created users that haven't logged in you can modify the queries below to include another clause in the where clause against the cwd_user.created_date column.  The created_dated column will hold the date/time the user was created within Jira.  

For Jira 8.x and Later

PostgreSQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login" 
FROM cwd_user u
	JOIN cwd_directory d ON u.directory_id = d.id
	LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
	AND d.active = 1
	AND u.lower_user_name IN (
			SELECT DISTINCT lower_child_name
		    FROM cwd_membership m
		    JOIN licenserolesgroup gp ON m.lower_parent_name = lower(gp.GROUP_ID))
    AND (u.id IN (
			SELECT ca.user_id
			FROM cwd_user_attributes ca
			WHERE attribute_name = 'login.lastLoginMillis' AND to_timestamp(CAST(ca.attribute_value as bigint)/1000) <= current_date - 90)
		OR u.id NOT IN (
			SELECT ca.user_id
			FROM cwd_user_attributes ca
			WHERE attribute_name = 'login.lastLoginMillis')
		)
ORDER BY "Last Login" DESC;

This query will return users who are inactive where the user exists in multiple directories:

PostgreSQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(ca.attribute_value AS BIGINT)/1000) AS "Last Login" 
FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
    AND d.active = 1
    AND u.lower_user_name IN (
            SELECT DISTINCT lower_child_name
            FROM cwd_membership m
            JOIN licenserolesgroup gp ON m.lower_parent_name = lower(gp.GROUP_ID))
    AND (u.id, u.directory_id) IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE attribute_name = 'login.lastLoginMillis' AND to_timestamp(CAST(ca.attribute_value as bigint)/1000) <= current_date - 90
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 1))
    AND (u.id, u.directory_id) NOT IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE attribute_name = 'login.lastLoginMillis'
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 0)) 
ORDER BY "Last Login" DESC;

This query  will return the "never logged in" users:

select user_name from cwd_user where user_name not in 
(SELECT cwd_user.user_name
FROM cwd_user, cwd_user_attributes
WHERE cwd_user_attributes.user_id = cwd_user.id
AND cwd_user_attributes.attribute_name = 'lastAuthenticated');
MySQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    FROM_UNIXTIME((CAST(attribute_value AS UNSIGNED)/1000)) AS "Last Login"
FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
    AND d.active = 1
    AND u.lower_user_name IN (
            SELECT DISTINCT lower_child_name
            FROM cwd_membership m
            JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID)
    AND (u.id IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            WHERE attribute_name = 'login.lastLoginMillis' AND FROM_UNIXTIME(ROUND(ca.attribute_value/1000)) <= (current_date - interval 90 day))
        OR u.id NOT IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            WHERE attribute_name = 'login.lastLoginMillis')
        )
ORDER BY "Last Login" DESC;

This query will return users who are inactive where the user exists in multiple directories:

MySQL 
SELECT 
    d.directory_name AS 'Directory', 
    u.user_name AS 'Username',
    FROM_UNIXTIME(CAST(ca.attribute_value AS UNSIGNED)/1000) AS 'Last Login' 
FROM 
    cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE 
    u.active = 1
    AND d.active = 1
    AND u.lower_user_name IN (
            SELECT DISTINCT lower_child_name
            FROM cwd_membership m
            JOIN licenserolesgroup gp ON m.lower_parent_name = lower(gp.GROUP_ID))
    AND (
        (u.id, u.directory_id) IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE ca.attribute_name = 'login.lastLoginMillis' AND FROM_UNIXTIME(CAST(ca.attribute_value AS UNSIGNED)/1000) <= CURDATE() - INTERVAL 90 DAY
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 1)
            )
        AND 
        (u.id, u.directory_id) NOT IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE ca.attribute_name = 'login.lastLoginMillis'
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 0)
            ) 
        )
ORDER BY 'Last Login' DESC;
MSSQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    DATEADD(S, CONVERT(int,left(attribute_value, 10)),'1970-01-01') AS "Last Login"
FROM cwd_user u
	JOIN cwd_directory d ON u.directory_id = d.id
	LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
	AND d.active = 1
	AND u.lower_user_name IN (
			SELECT DISTINCT lower_child_name
		    FROM cwd_membership m
		    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID)
    AND (u.id IN (
			SELECT ca.user_id
			FROM cwd_user_attributes ca
			WHERE attribute_name = 'login.lastLoginMillis' AND DATEADD(S, CONVERT(int,left(ca.attribute_value, 10)), '1970-01-01') <= DATEADD(d, -90, CONVERT(date, getdate()))
		OR u.id NOT IN (
			SELECT ca.user_id
			FROM cwd_user_attributes ca
			WHERE attribute_name = 'login.lastLoginMillis'))
		)
ORDER BY "Last Login" DESC;

This query will return users who are inactive where the user exists in multiple directories:

MSSQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    DATEADD(S, CONVERT(BIGINT, LEFT(ca.attribute_value, 10)) / 1000, '1970-01-01') AS "Last Login"
FROM cwd_user u
	JOIN cwd_directory d ON u.directory_id = d.id
	LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
	AND d.active = 1
	AND u.lower_user_name IN (
			SELECT DISTINCT lower_child_name
		    FROM cwd_membership m
		    JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID)
    AND (u.id IN (
			SELECT ca.user_id
			FROM cwd_user_attributes ca
			JOIN cwd_user u ON ca.user_id = u.id
			WHERE ca.attribute_name = 'login.lastLoginMillis' 
            AND DATEADD(S, CONVERT(BIGINT, LEFT(ca.attribute_value, 10)) / 1000, '1970-01-01') <= DATEADD(day, -90, GETDATE())
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 1))
        AND (u.id NOT IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE ca.attribute_name = 'login.lastLoginMillis'
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 0)))
        )
ORDER BY "Last Login" DESC;
Oracle 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    TO_DATE('01-JAN-1970', 'DD-MON-YY') + (ca.attribute_value / 1000 / 60 / 60 / 24) AS "Last Login"
FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
    AND d.active = 1
    AND u.lower_user_name IN (
            SELECT DISTINCT lower_child_name
            FROM cwd_membership m
            JOIN licenserolesgroup gp ON m.parent_name = gp.GROUP_ID)
    AND (u.id IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            WHERE attribute_name = 'login.lastLoginMillis' 
            AND TO_DATE('01-JAN-1970', 'DD-MON-YY') + (ca.attribute_value / 1000 / 60 / 60 / 24)
            <= SYSDATE - 90
        OR u.id NOT IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            WHERE attribute_name = 'login.lastLoginMillis'))
        )
ORDER BY "Last Login" DESC;

This query will return users who are inactive where the user exists in multiple directories:

Oracle 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    TO_DATE('01-JAN-1970', 'DD-MON-YY') + (ca.attribute_value / 1000 / 60 / 60 / 24) AS "Last Login"
FROM cwd_user u
    JOIN cwd_directory d ON u.directory_id = d.id
    LEFT JOIN cwd_user_attributes ca ON u.id = ca.user_id AND ca.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1
    AND d.active = 1
    AND u.lower_user_name IN (
            SELECT DISTINCT lower_child_name
            FROM cwd_membership m
            JOIN licenserolesgroup gp ON m.lower_parent_name = lower(gp.GROUP_ID))
    AND (u.id, u.directory_id) IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE attribute_name = 'login.lastLoginMillis' 
            AND TO_DATE('01-JAN-1970', 'DD-MON-YY') + (ca.attribute_value / 1000 / 60 / 60 / 24)
            <= SYSDATE - 90
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 1))
    AND (u.id, u.directory_id) NOT IN (
            SELECT ca.user_id, u.directory_id
            FROM cwd_user_attributes ca
            JOIN cwd_user u ON ca.user_id = u.id
            WHERE attribute_name = 'login.lastLoginMillis'
            AND u.directory_id IN (
                SELECT id FROM cwd_directory WHERE active = 0))
ORDER BY "Last Login" DESC;
Jira 6.x and older

Postgres

SELECT  u.user_name as "Username", 
	u.display_name as "Full Name", 
	to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login" 
FROM cwd_user u
	LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 90 OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

MySQL

SELECT  u.user_name as Username,
    u.display_name as Fullname,
    from_unixtime(round(a.attribute_value/1000)) as LastLogin
FROM cwd_user u
    LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE from_unixtime(round(a.attribute_value/1000)) <= current_date - interval 90 day OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

Last modified on Dec 10, 2024

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.