目的

If you want to disable inactive users to prevent them from being counted towards a Confluence license, it is possible to find out by running SQL queries against your database. This is particularly useful if you have a large number of users.

ソリューション

注意: 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;

   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;