Purpose

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 a 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": 

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 anonymised ones), you can run this SQL statement: 

SELECT * FROM user_mapping WHERE lower_username NOT IN (SELECT lower_user_name FROM cwd_user);

Related Content:

• How to list Confluence pages created by "Unknown User"