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

お困りですか?

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

コミュニティに質問

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;

最終更新日 2020 年 7 月 14 日

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

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