How to get a list of users with their last logon times
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 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. To show the info for all groups within your Confluence change the 'g.group_name = <group_name>' to 'g.group_name is not null'.
Please note that this will show info for all users on that group except the super-user 'admin'
Environment
Confluence 6.x, 7.x
Solution
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 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
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 descLast 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 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
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 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