How to get a list of users with their last logon times

プラットフォームについて: Data Center のみ。 - この記事は、 Data Center プラットフォーム。

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

*Fisheye および Crucible は除く

要約

Sometimes you may need to know how active your user base is, whom, and how many users logged in to Confluence during a specific time frame.

Using the queries below you can get the last logon times, successful login times and failed login times of users within a group.

Please note that this will show info for all users on that group except the super-user 'admin'
To show the info for all groups within your Confluence change the g.group_name = <group_name> to g.group_name is not null

ソリューション

Last logon times

The below query will return a list of users who last logged in or unsuccessfully tried to log in to Confluence on the timeframe interval you define. You will require access to run queries on Confluence database to extract these values.

Replace <group-name> with a specific group name, i.e. 'confluence-users' Left Joining last_login_date will return users who have never logged in as having 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';

Last successful login times:

Below query will return a list of users who last successfully logged in to Confluence on the timeframe interval you define.

Replace <group-name> with a specific group name, i.e. '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;

Last failed login times

Below query will return a list of users who last failed to log in to Confluence on the timeframe interval you define.

Replace <group-name> with a specific group name, i.e. '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;

Users Counting Towards License (including those who have never logged in)

The below query will return a list of users who count towards the license and their last login. Users who have never logged in will have a last_login of NULL

注意

This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.


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;

Users Counting Towards License and their last login date.

The below query will return a list of Licensed users who count towards the license and their last login date.

注意

This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.

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;

アトラシアンサポート

How to get a list of users with their last logon times

要約

ソリューション

Last logon times

PostgreSQL:

Oracle:

MySQL:

SQL Server:

Last successful login times:

PostgreSQL:

MySQL:

Last failed login times

PostgreSQL:

MySQL:

Users Counting Towards License (including those who have never logged in)

Users Counting Towards License and their last login date.

Related Content

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