最終ログイン日時を含むユーザー一覧の取得方法

プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。

この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバーバージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、アトラシアン Server サポート終了のお知らせにアクセスして、移行オプションを確認してください。

*Fisheye および Crucible は除く

要約

特定の時間枠に Confluence にログインしたユーザー基盤のアクティブ度やそこに含まれるユーザー、ユーザー数を確認したい場合があります。

次のクエリを使用すると、グループ内のユーザーの最終ログオン時間、ログイン成功時間、ログイン失敗時間を取得できます。

これにより、スーパーユーザーの「管理者」を除くそのグループのすべてのユーザーの情報が表示されることにご注意ください。
Confluence 内のすべてのグループの情報を表示するには、「g.group_name =<グループ名>」を「g.group_name is not null」に変更します。

ソリューション

最終ログイン日時

次のクエリは、定義した時間内に Confluence への最終ログインまたはログイン失敗を行っているユーザーのリストを返します。これらの値を抽出するには、Confluence データベースでクエリを実行するためのアクセス権が必要です。

「<グループ名>」を特定のグループ名 (例: 「confluence-users」に置き換えます。last_login_date に参加すると、「last_login=NULL」としてログインしたことのないユーザーが返されます。

PostgreSQL

WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    )
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name
     , g.group_name
     , l.last_login
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 WHERE g.group_name = '<group-name>'
 ORDER BY last_login DESC;

Oracle

select cu.user_name
    , cu.lower_user_name
    , cu.email_address
    , cu.display_name
    , cu.last_name
    , (timestamp '1970-01-01 00:00:00 GMT' +
  numtodsinterval(cua.attribute_value/1000, 'SECOND'))
  at time zone 'Asia/Calcutta' as lastAuthenticated
    FROM CONF.cwd_user cu left join CONF.cwd_user_attribute cua on cu.id = cua.user_id and cua.attribute_name = 'lastAuthenticated'
    order by lastAuthenticated DESC

MySQL

select cu.user_name
     , cu.lower_user_name
     , cu.email_address
     , cu.display_name
     , cu.last_name
     , cua.attribute_value
     , FROM_UNIXTIME(cua.attribute_value/1000) as lastAuthenticated
     FROM cwd_user cu left join cwd_user_attribute cua on cu.id = cua.user_id and cua.attribute_name = 'lastAuthenticated'
     order by lastAuthenticated desc

SQL Server:

select cu.user_name
     , cu.lower_user_name
     , cu.email_address
     , cu.display_name
     , cu.last_name
     , cua.attribute_value
     ,DATEADD(SS,CAST(cua.attribute_value as bigint)/1000,'19700101') as lastAuthenticated
     FROM cwd_user cu left join cwd_user_attribute cua on cu.id = cua.user_id and cua.attribute_name = 'lastAuthenticated';

最終ログイン成功日時:

次のクエリは、定義した時間内に Confluence への最終ログイン成功を行ったユーザーのリストを返します。

<グループ名> を特定のグループ名で置き換えます (例: 「confluence-users」)

PostgreSQL

WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    )
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name
     , g.group_name
     , li.successdate
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 INNER JOIN user_mapping um   ON (c.user_name = um.username)
 INNER JOIN logininfo li      ON (um.user_key = li.username)
 WHERE g.group_name LIKE '<group-name>'
 ORDER BY successdate DESC;

MySQL

WITH last_login_date AS
(SELECT user_id
      , FROM_UNIXTIME(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    AND FROM_UNIXTIME(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name
     , g.group_name
     , li.successdate
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 INNER JOIN user_mapping um   ON (c.user_name = um.username)
 INNER JOIN logininfo li      ON (um.user_key = li.username)
 WHERE g.group_name LIKE '<group-name>'
 ORDER BY successdate DESC;

最終ログイン失敗日時

次のクエリは、定義した時間内に Confluence への最終ログイン失敗を行ったユーザーのリストを返します。

<グループ名> を特定のグループ名で置き換えます (例: 「confluence-users」)

PostgreSQL

WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    )
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name
     , g.group_name
     , li.faileddate
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 INNER JOIN user_mapping um   ON (c.user_name = um.username)
 INNER JOIN logininfo li      ON (um.user_key = li.username)
 WHERE g.group_name LIKE '<group-name>' AND
 li.faileddate IS NOT NULL
 ORDER BY faileddate DESC;

MySQL


WITH last_login_date AS
(SELECT user_id
      , FROM_UNIXTIME(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    AND FROM_UNIXTIME(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
     , c.lower_user_name
     , c.email_address
     , c.display_name
     , c.last_name
     , g.group_name
     , li.faileddate
  FROM cwd_user c
 INNER JOIN last_login_date l ON (c.id = l.user_id)
 INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
 INNER JOIN cwd_group g       ON (m.parent_id = g.id)
 INNER JOIN user_mapping um   ON (c.user_name = um.username)
 INNER JOIN logininfo li      ON (um.user_key = li.username)
 WHERE g.group_name LIKE '<group-name>' AND
 li.faileddate IS NOT NULL
 ORDER BY faileddate DESC;

ライセンスにカウントされるユーザー (ログインしたことがないユーザーを含む)

次のクエリは、ライセンスと前回のログインにカウントされるユーザーのリストを返します。ログインしたことがないユーザーの「last_login」は「NULL」になります

注意

これは PostgreSQL DB で記載および検証されたため、ご利用のデータベースによっては微調整が必要になる場合があります。


WITH last_login_date AS
(SELECT user_id
      , to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
   FROM cwd_user_attribute cua
  WHERE cua.attribute_name = 'lastAuthenticated'
    )
 
SELECT DISTINCT u.display_name, u.lower_user_name, u.email_address, d.directory_name, l.last_login
FROM cwd_user u
LEFT JOIN last_login_date l ON (u.id = l.user_id)
JOIN cwd_membership m ON u.id = child_user_id
JOIN cwd_group g ON m.parent_id = g.id
JOIN SPACEPERMISSIONS sp ON g.group_name = sp.PERMGROUPNAME
JOIN cwd_directory d on u.directory_id = d.id
WHERE sp.PERMTYPE='USECONFLUENCE' AND u.active = 'T' AND d.active = 'T'
ORDER BY d.directory_name;

ライセンスにカウントされるユーザーと最終ログイン日。

次のクエリは、ライセンスにカウントされるライセンスユーザーとその最終ログイン日のリストを返します。

注意

これは PostgreSQL DB で記載および検証されたため、ご利用のデータベースによっては微調整が必要になる場合があります。

SELECT DISTINCT
        cu.lower_user_name AS "User name"
        ,cu.display_name AS "Display name"
        ,li.successdate::timestamp(0) AS "Last login date"
        ,cu.lower_email_address AS "e-mail address"
        ,cu.created_date::timestamp(0) AS "Account created"
        ,cd.directory_name AS "Directory"
    FROM cwd_user cu
    JOIN user_mapping um ON um.username = cu.user_name
    JOIN cwd_membership cm ON cu.id = child_user_id
    JOIN cwd_group cg ON cm.parent_id = cg.id
    JOIN SPACEPERMISSIONS sp ON cg.group_name = sp.PERMGROUPNAME
    JOIN cwd_directory cd on cu.directory_id = cd.id
    FULL JOIN logininfo li on li.username = um.user_key
    WHERE
        sp.PERMTYPE='USECONFLUENCE'
        AND cu.active = 'T'
        AND cd.active = 'T'
    ORDER BY "Last login date" DESC NULLS LAST;

アトラシアンサポート

最終ログイン日時を含むユーザー一覧の取得方法

要約

ソリューション

最終ログイン日時

PostgreSQL

Oracle

MySQL

SQL Server:

最終ログイン成功日時:

PostgreSQL

MySQL

最終ログイン失敗日時

PostgreSQL

MySQL

ライセンスにカウントされるユーザー (ログインしたことがないユーザーを含む)

ライセンスにカウントされるユーザーと最終ログイン日。

関連コンテンツ

さらにヘルプが必要ですか?