List of Confluence users with last login date

[kdickason]

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 = '<group-name>'
 ORDER BY last_login DESC;

I 

[Ojas Potdar]

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

[kdickason]

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

[kdickason]

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?