Confluence で非アクティブなユーザーを特定する方法

お困りですか?

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

コミュニティに質問

目的

非アクティブなユーザーを無効化して Confluence ライセンスに計上されるのを防ぎたい場合、データベースに対して SQL クエリを実行してそのようなユーザーを見つけることができます。これは、大量のユーザーがいるときに特に便利です。

This page contains outdated information relating to Confluence Server & Data Center. For a more updated KB, please visit How to get a list of users with their last logon times.

ソリューション

注意: Confluence 3.5 以降を利用している場合、利用しているユーザー管理システムにかかわらず同じクエリを使います。従来のユーザー管理システムで必要となるさまざまなクエリについては、このドキュメントの Confluence 3.5 または Confluence 3.4 以前 のバージョンのセクションをご確認ください。  

ご利用のデータベースに合わせてこれらのクエリを変更する必要がある可能性があります。SQL クエリで調整が必要な可能性があるため、データベース テーブルの大文字と小文字をご確認ください。

非アクティブなユーザーの一覧

SELECT *
FROM cwd_user
WHERE active = 'F';

特定の日付以降にログインしていないアクティブなユーザーの一覧

  • Confluence 4.0.x - 5.1.x

    SELECT username,
           successdate
    FROM logininfo
    WHERE successdate < '2016-01-01'
    ORDER BY successdate;
  • Confluence 5.2.x 以降

    SELECT cu.user_name,
           cd.directory_name,
           li.successdate
    FROM logininfo li
    JOIN user_mapping um ON um.user_key = li.username
    JOIN cwd_user cu ON um.username = cu.user_name
    JOIN cwd_directory cd ON cu.directory_id = cd.id
    WHERE successdate < '2016-01-01'
    ORDER BY successdate;

    180 日前からの日付を取得する場合は次のものを利用します (MS SQL を利用している場合を除く、以降を参照)。

    SELECT cu.user_name,
           cd.directory_name,
           li.successdate
    FROM logininfo li
    JOIN user_mapping um ON um.user_key = li.username
    JOIN cwd_user cu ON um.username = cu.user_name
    JOIN cwd_directory cd ON cu.directory_id = cd.ID
    WHERE successdate < (CURRENT_DATE - integer '180')
    ORDER BY successdate;

    MS SQL を利用して 180 日前からの日付を取得するには次のものを利用します。

    SELECT cu.user_name,
    cd.directory_name,
    li.SUCCESSDATE
    FROM logininfo li
    JOIN user_mapping um ON um.user_key = li.USERNAME
    JOIN cwd_user cu ON um.username = cu.user_name
    JOIN cwd_directory cd ON cu.directory_id = cd.id
    WHERE li.SUCCESSDATE < (getdate() - 180)
    ORDER BY li.SUCCESSDATE;

    (warning) MS SQL のステートメントでは大文字と小文字が区別される点にご注意ください。


最終ログイン日ごとのユーザー一覧

  • Confluence 3.5.x のみ

    SELECT ENTITY_NAME,
           DATE_VAL
    FROM OS_PROPERTYENTRY
    WHERE ENTITY_KEY='confluence.user.last.login.date'
      AND ENTITY_NAME LIKE 'CWD%'
    ORDER BY DATE_VAL;
  • Confluence 4.0.x - 5.1.x

    SELECT username,
           successdate
    FROM logininfo
    ORDER BY successdate;
  • Confluence 5.2.x 以降

    SELECT cu.user_name,
           cd.directory_name,
           li.successdate
    FROM logininfo li
    JOIN user_mapping um ON um.user_key = li.username
    JOIN cwd_user cu ON um.username = cu.user_name
    JOIN cwd_directory cd ON cu.directory_id = cd.id
    ORDER BY successdate;

前のログイン日ごとのユーザー一覧

"前の" ログイン日とは、ユーザーの最終ログインの前のログインです。

  • Confluence 4.0.x - 5.1.x

    SELECT username,
           prevsuccessdate
    FROM logininfo
    ORDER BY prevsuccessdate;
  • Confluence 5.2.x 以降

    SELECT cu.user_name,
           li.PREVsuccessdate
    FROM logininfo li
    JOIN user_mapping um ON um.user_key = li.username
    JOIN cwd_user cu ON um.username = cu.user_name
    ORDER BY PREVsuccessdate;

特定の日付以降にコンテンツ (ページ、ブログ、またはコメント) を作成していないアクティブ ユーザー

これらのアカウントは引き続きアクティブですが、ユーザーが Confluence を利用しなくなっている可能性があります。

  • Confluence 4.0.x - 5.1.x

    SELECT user_name
    FROM cwd_user
    WHERE user_name NOT IN
        ( SELECT CREATOR
         FROM CONTENT
         WHERE CONTENTTYPE IN ('PAGE','BLOGPOST','COMMENT')
           AND CREATIONDATE > '2007-01-01')
      AND active = 'T';
  • Confluence 5.2.X 以降

    SELECT cu.user_name
    FROM cwd_user cu
    JOIN user_mapping um ON um.username = cu.user_name
    WHERE um.user_key NOT IN
        ( SELECT CREATOR
         FROM CONTENT
         WHERE CONTENTTYPE IN ('PAGE','BLOGPOST','COMMENT')
           AND CREATIONDATE > '2007-01-01' AND CREATOR IS NOT NULL)
      AND cu.active = 'T';

ユーザーが Confluence に最後にログインしたタイミングを特定

  • Confluence 5.2.X 以降

ユーザー基盤の最終ログイン日
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'
    AND to_timestamp(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
     , 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>' -- for instance, 'confluence-users'
 ;

ライセンスに計上されるがログインしたことのないユーザーの一覧

  • Confluence 5.2.x 以降
ログインしたことのない有効なユーザー
SELECT u.lower_user_name
FROM cwd_user u
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' AND u.lower_user_name NOT IN (
	SELECT cu.lower_user_name
	FROM logininfo li
	JOIN user_mapping um ON um.user_key = li.username
	JOIN cwd_user cu ON um.username = cu.user_name
	JOIN cwd_directory cd ON cu.directory_id = cd.id
)
GROUP BY u.lower_user_name, d.directory_name
ORDER BY d.directory_name;


Oracle の場合

Oracle の場合、日付を適切に処理できないために上記のクエリが失敗する可能性があります。

元のクエリが Oracle で失敗する場合、日付への言及を '2007-01-01' から to_date('01-JAN-2007','DD-MON-YYYY') に変えます。

  • たとえば、次のクエリは
SELECT cu.user_name,
       cd.directory_name,
       li.successdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE successdate < '2016-01-01'
ORDER BY successdate;
  • 次のようになります。
SELECT cu.user_name,
       cd.directory_name,
       li.successdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
JOIN cwd_directory cd ON cu.directory_id = cd.id
WHERE successdate < to_date('01-JAN-2016','DD-MON-YYYY')
ORDER BY successdate;
Last modified on Mar 21, 2024

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

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