Jira サーバーでユーザーの最終ログイン日を見つける

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

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

コミュニティに質問

This article requires fixes

This article has been Flagged for fixing. Use caution when using it and fix it if you have Publisher rights.

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

Please be mindful that the information below is provided on a best effort as-is basis as it pertains to a service that is outside of Atlassian Support Scope. Any effort provided to support issues related to out of scope issues will be on a best-effort by the support engineer.

目的

アプリケーションの利用状況の監査のため、ユーザーの最終ログインのタイムスタンプの一覧を取得する。

ユーザーがログイン状況を記憶するトークンを設定している場合、最終ログイン日にはユーザーが最後にアクセスした日付ではなく最後にログイン プロセスを完了した日付になる点にご注意ください。詳細については JRA-60508 をご確認ください。

プロセス

Jira 7.0.x 以降

Postgresql 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login" 
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
    ) AS m ON m.child_name = u.user_name
JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
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 (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
    ) AS m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM cwd_user_attributes
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY attribute_value DESC;
MS SQL 
SELECT d.directory_name AS "Directory",
    u.user_name AS "Username",
  DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'})  AS "Last Login"
FROM dbo.cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM dbo.cwd_membership m
    JOIN dbo.licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
    ) AS m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM dbo.cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;

(info) ご利用の MS-SQL データベースで利用しているスキーマ (jiraschema) でスキーマ名 (.dbo) を置き換える必要がある可能性があります。

Oracle 
SELECT d.directory_name,
    u.user_name,
        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
    ) m ON m.child_name = u.user_name
JOIN (
    SELECT *
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc;


Jira 6.2 から 6.4

以降を参考に、ご利用のデータベースに応じたクエリを実行します。

PostgreSQL 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login" 
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name 
    FROM cwd_membership m 
    JOIN globalpermissionentry gp ON m.lower_parent_name = gp.group_id 
    WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
    ) AS m ON m.child_name = u.user_name
LEFT JOIN (
    SELECT * 
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
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 (
	SELECT DISTINCT child_name 
	FROM cwd_membership m 
	JOIN globalpermissionentry gp ON m.lower_parent_name = gp.group_id 
	WHERE gp.permission IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
	) AS m ON m.child_name = u.user_name
LEFT JOIN (
	SELECT * 
	FROM cwd_user_attributes 
	WHERE attribute_name = 'login.lastLoginMillis'
	) AS a ON a.user_id = u.id
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;
MS SQL 
SELECT d.directory_name AS "Directory",
    u.user_name AS "Username",
  DATEADD(second, cast(attribute_value as bigint)/1000,{d '1970-01-01'})  AS "Last Login"
FROM dbo.cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM dbo.cwd_membership m
    JOIN dbo.globalpermissionentry gp ON m.lower_parent_name = gp.GROUP_ID
    WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
    ) AS m ON m.child_name = u.user_name
LEFT JOIN (
    SELECT *
    FROM dbo.cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.ID
JOIN dbo.cwd_directory d ON u.directory_id = d.ID
ORDER BY "Last Login" DESC;
Oracle 
SELECT d.directory_name,
    u.user_name,
        TO_DATE('19700101','yyyymmdd') + ((attribute_value/1000)/24/60/60) as last_login_date
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN globalpermissionentry gp ON m.lower_parent_name = gp.GROUP_ID
    WHERE gp.PERMISSION IN ('ADMINISTER', 'USE', 'SYSTEM_ADMIN')
    ) m ON m.child_name = u.user_name
LEFT JOIN (
    SELECT *
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.ID
order by last_login_date desc

Jira 4.3 から 6.1.9

この情報を取得するうえで関連するテーブルは cwd_user および cwd_user_attributes 属性です。以降を参考に、ご利用のデータベースに応じたクエリを実行します。

Atlassian Crowd や他の LDAP サービスなどの外部のユーザー管理システムを利用している場合、このセクションのクエリは動作しません。

MySQL 
SELECT cwd_user.user_name, from_unixtime(round(cwd_user_attributes.attribute_value/1000))
FROM cwd_user, cwd_user_attributes 
WHERE cwd_user_attributes.user_id = cwd_user.id 
AND cwd_user_attributes.attribute_name = 'lastAuthenticated';
PostgreSQL 
SELECT cwd_user.user_name, to_timestamp(CAST(cwd_user_attributes.attribute_value AS bigint)/1000) 
FROM cwd_user, cwd_user_attributes 
WHERE cwd_user_attributes.user_id = cwd_user.id 
AND cwd_user_attributes.attribute_name = 'lastAuthenticated';
Microsoft SQL Server 
SELECT cwd_user.user_name, dateadd(second,cast(cast(cwd_user_attributes.attribute_value AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000')
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id AND cwd_user_attributes.attribute_name = 'login.lastLoginMillis'
Oracle SQL 
SELECT cwd_user.*,
to_date('01.01.1970','dd.mm.yyyy') + to_number(cwd_user_attributes.attribute_value)/1000/60/60/24 AS last_login
FROM cwd_user, cwd_user_attributes
WHERE cwd_user.id = cwd_user_attributes.user_id
AND cwd_user_attributes.attribute_name ='login.lastLoginMillis' AND cwd_user.ACTIVE ='1'

必要に応じてクエリにスキーマ名を追加します。


オプション 2 - Jira 7.0.x 以降

If you want to add the "active" and "never logged in" users to this list, consider the following query:

Postgresql 
SELECT d.directory_name AS "Directory", 
    u.user_name AS "Username",
    u.active AS "Active",
    to_timestamp(CAST(attribute_value AS BIGINT)/1000) AS "Last Login"
FROM cwd_user u
JOIN (
    SELECT DISTINCT child_name
    FROM cwd_membership m
    JOIN licenserolesgroup gp ON m.lower_parent_name = gp.GROUP_ID
    ) AS m ON m.child_name = u.user_name
LEFT JOIN (
    SELECT *
    FROM cwd_user_attributes ca
    WHERE attribute_name = 'login.lastLoginMillis'
    ) AS a ON a.user_id = u.ID
JOIN cwd_directory d ON u.directory_id = d.id
ORDER BY "Last Login" DESC;





説明 アプリケーションの利用状況の監査のため、ユーザーの最終ログインのタイムスタンプの一覧を取得する。
製品Jira
プラットフォームServer
Last modified on Mar 1, 2024

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

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.