How to get a list of all "Unknown User" 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
This knowledge base will provide instructions on how to get a list of all the users that are displayed as "Unknown User" in Confluence.
Technical Background
Deleting a user stored in an external directory, is a two-step process. You need to remove them from all external directories and perform a directory resync before they can be deleted from Confluence. Once a user account has been deleted their identity will be anonymised throughout Confluence in places like the page byline, mentions, comments, and page history.
A user is displayed as "Unknown User" as a result of user deletion only on the External Directory side. You can follow the process described on KB How To Rename the User: "Unknown User" as the Content Creator Caused by User Deletion to anonymised the deleted user.
Solution
Use any of these SQL statements to list the users marked as "Unknown User":
PostgreSQL
1
2
3
4
5
6
7
SELECT u.user_key, u.username,*
FROM user_mapping u
WHERE u.user_key NOT IN (
SELECT u.user_key
FROM user_mapping u
WHERE lower_username IN (SELECT lower_user_name FROM cwd_user))
AND u.user_key!=u.username;In case you want to identify all the users deleted (including the anonymized ones), you can run this SQL statement:
PostgreSQL
1
SELECT * FROM user_mapping WHERE lower_username NOT IN (SELECT lower_user_name FROM cwd_user);Related Content
Was this helpful?