Info report about users who have not logged in for x days (configurable)

Hello Sourabh - We have incorporated a new feature which might come handy to get the user access report. Please refer to this document for further info -https://www.ibm.com/docs/en/cloud-paks/cp-data/4.8.x?topic=environment-monitoring-user-activity.

Hi Sourabh -

Please review the below Queries. Long terms, we would look at option of adding few OOTB reports which can be used as a template -

We can utilize the field last_session_start_timestamp in misc column of platform_users table.Query 1: Users created and already logged-in/logged-out BUT not active for "X" days (In this case we can utilised last_session_start_timestamp )

SELECT uid, username, misc
FROM platform_users
WHERE
  misc->>'last_session_start_timestamp' IS NOT NULL
  AND misc->>'last_session_start_timestamp' <> ''
  AND (CURRENT_TIMESTAMP - TO_TIMESTAMP((misc->>'last_session_start_timestamp')::bigint / 1000)::timestamp) > INTERVAL '3 days';

Query 2: Users created but not logged-in yet for "X" days (In this case we don't have last_session_start_timestamp so we need to depend on created_timestamp column

SELECT uid, username, misc
FROM platform_users
WHERE
  (misc->>'last_session_start_timestamp' IS NULL OR misc->>'last_session_start_timestamp' = '')
  AND created_timestamp IS NOT NULL
  AND (CURRENT_TIMESTAMP - TO_TIMESTAMP((created_timestamp)::bigint / 1000)::timestamp) > INTERVAL '3 days';

We can combined these Query like:

SELECT uid, username, misc
FROM platform_users
WHERE
  (
    (misc->>'last_session_start_timestamp' IS NOT NULL
     AND misc->>'last_session_start_timestamp' <> ''
     AND (CURRENT_TIMESTAMP - TO_TIMESTAMP((misc->>'last_session_start_timestamp')::bigint / 1000)::timestamp) > INTERVAL '3 days')
  )
  OR
  (
    (misc->>'last_session_start_timestamp' IS NULL OR misc->>'last_session_start_timestamp' = '')
    AND created_timestamp IS NOT NULL
    AND (CURRENT_TIMESTAMP - TO_TIMESTAMP((created_timestamp)::bigint / 1000)::timestamp) > INTERVAL '3 days'
  );

We can use INTERVAL with different units such as seconds, minutes, hours, days, months