How to list Confluence pages created by "Unknown User"
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products will end after 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
This knowledge base will provide instructions on how to get a list of all the content created by an "Unknown User".
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.
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