How to find all "@unknown user" in Confluence documents?

Question

Hi all,

I am working currently for a big company with a lot of stuff in Confluence.

Currently I go through all the old stuff to archive, delete, update etc.

Often I have documentation where links point to users who aren't working in the company anymore, they then appear as e.g. @[deleted] (user.name).

If I use the search I got also all docs where just unknown user, not @[deleted] is included.

Questions:

Is there/what is the way to find all docs where "@unknown user" is mentioned?

Is there a way to lookup "case sensitive"? Tried with "unknown user", but didn't work.

Thx 2 all n a happy x-mas!!! :)

Marc

Kyle Manel · Answer

Marc,
The link that Victor provided above, is for your Confluence database, not from an addon for Confluence;
The Confluence database is an essential part of the back-end of Confluence, required to store and provide all information in Confluence.

It is stored in a separate application/server.

The line provided in the link from Victor is an 'SQL query' which can be run to procure information from the database;

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

In this case it will select everything from user_mapping table in your confluence database; where the lower_username field is not also found in the cwd table;
Essentially it lists all mapped users which are no longer registered with the server.
This can also be procured from the Confluence administration page, in Users under the USERS & SECURITY heading on the left-hand side of the page.  Select Show all users and if a selected user is no longer registered information will be provided here; This is often the case with an ldap server providing users; When the user is removed from Active Directory (or the ldap server), the Confluence server no longer has a user to associate with the content from the ldap, but still has its own internal reference to the content, it associates to an 'unknown user'

I've not heard best practices with this in mind previously, but if this is a concern for you, it will likely be wise to handle the removal of the user from the Confluence server before their removal on the associated ldap server.

marc_nicolaus · Answer

Hi Viktor,

and thanks for your quick reply!

Looks very interesting, but I am currently not sure if & how I can do an sql query In confluence...

If I understand it right, its a paid feature!??

https://marketplace.atlassian.com/apps/225/sql-for-confluence-pro-edition?hosting=server&tab=overview

Dont think that we got this...nevertheless I will go through the page and the other links, maybe I find something similar or related...

Greez

Marc

Edit: No, thats not a way 4 me; mainly because I dont have admin rights.... :(

Any other suggestions are welcome...

Victor Mutambuki · Answer

Marc,

Welcome to the community. Please see the link below.
https://confluence.atlassian.com/confkb/how-to-get-a-list-of-all-unknown-user-in-confluence-867362827.html

Victor