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

Still need help?

The Atlassian Community is here for you.

Ask the community

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;

For Jira 7.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;
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;
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;
Oracle
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_date('01-JAN-1970', 'DD-MON-YY') + ( 1 / 24 / 60 / 60) * ca.attribute_value 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') + ( 1 / 24 / 60 / 60) * ca.attribute_value 
            <= to_date(SYSDATE - 90, 'DD-MON-YY')
        OR u.id NOT IN (
            SELECT ca.user_id
            FROM cwd_user_attributes ca
            WHERE attribute_name = 'login.lastLoginMillis'))
        )
ORDER BY "Last Login" DESC;

Last modified on Jul 14, 2020

Was this helpful?

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