Purpose

To better manage your license count, at some point you may be interested in verifying which of your existing users are actively contributing on Confluence, and which users have not logged in for a period of time.

Currently, Confluence does not yet offer a report to check for user inactivity on the UI natively. In the meantime, while this functionality is not implemented and bundled to the Confluence UI, we can extract this specific information directly from the Confluence database using the following SQL queries, depending on your current DB and Confluence version. 

• To find the user details of who all logged in 6 months or before (success date) 
• To find the user details whose account is created 6 months before and who never logged in at all

We can get these details after executing the below SQL in the Confluence connected Database. This information helps us to clean up the  Inactive users from the confluence and that will help to manage the user license count in Confluence. The below SQL's are built for getting data > 6 months. SQL can be modified based on the time durations.

Solution

• To find the user details of who all logged in 6 months or before (success date) 

  • Postgres
  • Oracle

   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 - INTERVAL '6 months' )
  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)
   ORDER BY last_login DESC;

  WITH last_login_date AS
  (SELECT user_id
        , TO_DATE('1970-01-01','YYYY-MM-DD') + cua.attribute_value / 86400000 AS last_login
     FROM cwd_user_attribute cua
    WHERE cua.attribute_name = 'lastAuthenticated'
      AND TO_DATE('1970-01-01','YYYY-MM-DD') + cua.attribute_value / 86400000 < add_months(sysdate, -6) )
  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)
   ORDER BY last_login DESC; 

• To find the user details whose account is created 6 months before and who never logged in at all

  • Postgres
  • Oracle

  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 - INTERVAL '6 months' )
  SELECT c.user_name
       , c.lower_user_name
       , c.email_address
       , c.display_name
       , c.last_name 
       , g.group_name
       , c.created_date as "account_created_date"																		 
       , 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 c.created_date < CURRENT_DATE - INTERVAL '6 months'
   ORDER BY last_login DESC;	

  WITH last_login_date AS
  (SELECT user_id
        , TO_DATE('1970-01-01','YYYY-MM-DD') + cua.attribute_value / 86400000 AS last_login
     FROM cwd_user_attribute cua
    WHERE cua.attribute_name = 'lastAuthenticated'
      AND TO_DATE('1970-01-01','YYYY-MM-DD') + cua.attribute_value / 86400000 < add_months(sysdate, -6) )
  SELECT c.user_name
       , c.lower_user_name
       , c.email_address
       , c.display_name
       , c.last_name 
       , g.group_name
       , c.created_date as "account_created_date"																		 
       , 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 c.created_date < add_months(sysdate, -6)
   ORDER BY last_login DESC;