How to get a list of Confluence users' last password change date
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
To get a list of all Confluence users' last password change date.
Solution
Run the following SQL query
1
2
3
4
5
SELECT cu.user_name, cua.attribute_name, cua.attribute_value
FROM cwd_user_attribute cua
INNER JOIN cwd_user cu
ON cua.user_id = cu.id
WHERE cua.attribute_name = 'passwordLastChanged';Confluence stores the password last changed time based on epoch time. Hence, to convert it to human readable format:
Truncate the attribute_value (timestamps) for each user to only 10 digits, instead of the 13 digits stored in Confluence DB.
ℹ️ The extra three digits represent milliseconds and should be omitted before the conversion.
Navigate to the following link and convert the truncated values accordingly.
Was this helpful?