List of Confluence users with last login date

Question

I am looking at posts to get a list of Confluence users by last login date. This (https://confluence.atlassian.com/confkb/how-to-get-a-list-of-users-with-their-last-logon-times-985499701.html) indicates "on the timeframe interval you define." Can someone show me an example of entering the timeframe interval? We are using a Postgres database. 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 = '' ORDER BY last_login DESC; I

kdickason · Answer

I am typing these in the Console in Jira.  This line is giving me an error:

(SELECT user_id

The error is "unexpected token: SELECT @ line 2, column 2. @ line 2 column 2."

What  am I missing?

kdickason · Answer

Thank you!  I'll work with these today and let you know.

Ojas Potdar · Answer

Hello @kdickason ,

Thank you for posting your question in Atlassian community.

Please check below examples and see if they help you.

* select users with last login between 11 Nov 2022 and 13-11-2022.

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) between '2022-11-11' AND '2022-11-13' )
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 = 'confluence-users'
ORDER BY last_login DESC;

* select users with last login after 11 Nov 2022.

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) > '2022-11-11' )
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 = 'confluence-users'
ORDER BY last_login DESC;

You can also run below query to check what the format is used for timeframe.

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

Regards,
Ojas Potdar