Purpose

This knowledge base will provide instructions on how to get a list of all the content created by an "Unknown User".

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. 

The contents of these users will be marked as "Created by Unknown User(username)".

An "Unknown User" is technically a row in the database where a row exists in user_mapping table but the user_mapping.username does not map to a row in cwd_user.user_name. All content pages are stored in CONTENT table with CONTENT.creator mapping to user_mapping.user_key.

Solution

Run the following SQL query:

SELECT s.spacekey, s.spacename, c.title AS "Page Title", um.username AS "Unknown User"
FROM content c, spaces s, user_mapping um 
LEFT JOIN cwd_user cu ON um.username = cu.user_name
WHERE c.spaceid = s.spaceid AND c.contenttype = 'PAGE' AND c.prevver IS NULL AND c.content_status = 'current' AND c.creator = um.user_key AND cu.id IS NULL
ORDER BY s.spacekey, c.title