Summary

• While using Confluence with Jira remote directory for user management, if I want to search for information regarding user usage, such as last login data, we cannot find because nothing is registered in the cwd_user_attribute for users coming from Jira remote directory. Data is saved normally in the cwd_user.

Steps to reproduce

• PostgreSQL as database.
• Install Jira 6.4.x.
• Install confluence 5.8.x, 5.9.x, set Jira remote directory for User Management.
• Create a "testuser" and login
• Create any content.
• Throw the below queries, from the links, to find information regarding this user.
• How to check for users who never logged into Confluence
• How to list the last login date for all users in Confluence
• How to identify inactive users in Confluence

  select * from cwd_user
  select * from cwd_user_attribute
  select * from cwd_directory

Expected Results

• Data is retrieved from the cwd_user and cwd_user_attribute.

Actual Results

• Nothing is retrieved from cwd_user_attribute about the Jira remote directory user.

Investigation Done

• cwd_user receives the data normally
• when used with Jira, the cwd_user_attribute receives only data from the Confluence Internal Directory
• both cwd_user and cwd_attribute receives data normally when used with just Confluence internal and when used with LDAP/AD. Can retrieve desired data with the above queries.

Workaround

• Can retrieve the desired data from the Jira database by adapting the queries. Jira uses cwd_user_attributes. Note the s.
• Example:

  SELECT cwd_user.user_name, to_timestamp(CAST(cwd_user_attributes.attribute_value as double precision)/1000) AS LastLogin
  FROM cwd_user_attributes, cwd_user 
  WHERE cwd_user_attributes.user_id = cwd_user.id 
  AND cwd_user_attributes.attribute_name = 'lastAuthenticated' 
  AND cwd_user.active='1'
  ORDER BY LastLogin DESC;