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

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.

    

Summary

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

Environment

Confluence 6.x, 7.x

Solution

Last logon times

Bellow 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.

(info) Replace '<group-name>' with a group name, i.e. 'confluence-users'

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>'
 ORDER BY last_login DESC;
Make sure to edit the timezone in the query as per your timezone. You can find the value of your timezones in https://docs.oracle.com/cd/B13866_04/webconf.904/b10877/timezone.htm
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 
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

Last successful login times

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

(info) Replace '<group-name>' with a group name, i.e. 'confluence-users'

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
     , 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

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

(info) Replace '<group-name>' with a group name, i.e. 'confluence-users'

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
     , 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;

Related Content

How to identify inactive users in Confluence


Last modified on Aug 25, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.