How to Get the List of User Details for a Specific User Directory in Confluence
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
If you're unsure of how to proceed with executing these commands, please contact your DBA.
Also, these commands may need adjustment for other DBMSs as they were built and tested with Postgres.
Sometimes you may need to list all users' details from a particular User Directory. The below SQL queries can help to get these details from Confluence database.
Environment
Confluence Data Center and PostgreSQL
Solution
First, identify the name of the User Directory that you want to fetch the user details. Run the below query and check the column DIRECTORY_NAME:
1
SELECT * FROM CWD_DIRECTORY;
2. Use the below query to get the user details for the specific directory name. Replace the DIRECTORY_NAME with the one that you want to check:
1
2
3
SELECT ID, USER_NAME, FIRST_NAME, LAST_NAME, EMAIL_ADDRESS, ACTIVE, CREATED_DATE from CWD_USER
WHERE DIRECTORY_ID=(SELECT ID FROM CWD_DIRECTORY WHERE DIRECTORY_NAME='DIRECTORY_NAME' )
ORDER BY USER_NAME ASC; Was this helpful?