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

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

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 days 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.

次の SQL クエリは、LDAP または Crowd でネストされたグループを使っていたり、複数のディレクトリを横断して重複したユーザー名を持つユーザーがいたりする場合は正確な結果が返されない可能性があります。このため、内容に相違がある場合は Jira ユーザー インターフェイスで提供されるライセンス数をご確認ください。 

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;
MS SQL
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:

MS SQL
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;

最終更新日: 2024 年 1 月 19 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.