Find Unknown Users and pages created by them in Confluence Data Center
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 SQL queries on how to get a list of all unknown users, as well as all the content created by an Unknown User in your Confluence Data Center instance.
Solution
Unknown User in Confluence
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 anonymize 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 the 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 the CONTENT table with CONTENT.creator mapping to user_mapping.user_key.
Find all Unknown Users in Confluence
Use any of these SQL statements to list the users marked as "Unknown User":
PostgreSQL
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
SELECT * FROM user_mapping WHERE lower_username NOT IN (SELECT lower_user_name FROM cwd_user);Find all pages created by an Unknown User
PostgreSQL
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.titleFind pages created by an Unknown User in a specific space
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 and s.spacekey = '<space key of the desired space>'
ORDER BY s.spacekey, c.titleℹ️ As a space admin, you can go to Space tools > Overview > Space details to find the Space key.
Was this helpful?