How to get a list of users mentioned in a space/page and
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
As a Confluence administrator, you may need to know what users were mentioned in a specific page or space, and how many times.
There currently isn't a way to export this information directly from the Confluence via the UI.
Solution
The queries below use wildcard searching in order to locate page content that matches a specific pattern for macros. Due to the nature of wildcard searching, these queries may take a long time to execute, and it is recommended to carry these out in a clone of the production database rather than on the production environment itself to avoid any impact to the production environment.
Number of Mentioned Users in a Space per Page
This SQL statement should provide you a list of all the users mentioned in a specific page, with a counter of how many times that user was mentioned:
create or replace function regexp_count(text, text)
returns integer language sql as $$
select count(m)::int
from regexp_matches($1, $2, 'g') m
$$;
SELECT s.spacekey, c.title, um.user_key, um.lower_username, regexp_count(bc.body, CONCAT('<ac:link><ri:user ri:userkey="',um.user_key))
FROM user_mapping um
LEFT JOIN bodycontent bc ON bc.body LIKE CONCAT('%<ac:link><ri:user ri:userkey="',um.user_key,'%')
JOIN content c ON c.contentid=bc.contentid
JOIN spaces s ON c.spaceid=s.spaceid
WHERE c.contenttype ='PAGE'
AND c.prevver IS null
AND s.spacekey='<SPACEKEY>'SELECT s.spacekey, c.title, um.user_key, um.lower_username, REGEXP_COUNT(bc.body, CONCAT('<ac:link><ri:user ri:userkey="',um.user_key))
FROM user_mapping um
LEFT JOIN bodycontent bc ON bc.body LIKE CONCAT('%<ac:link><ri:user ri:userkey="',CONCAT(um.user_key,'%'))
JOIN content c ON c.contentid=bc.contentid
JOIN spaces s ON c.spaceid=s.spaceid
WHERE c.contenttype ='PAGE'
AND c.prevver IS nullList of Mentioned Users in a Space
A simpler way of list the user without knowing how many times they were mentioned, it will be the following:
SELECT DISTINCT um.lower_username
FROM user_mapping um
LEFT JOIN bodycontent bc ON bc.body LIKE CONCAT('%<ac:link><ri:user ri:userkey="',um.user_key,'%')
JOIN content c ON c.contentid=bc.contentid
JOIN spaces s ON c.spaceid=s.spaceid
WHERE c.contenttype ='PAGE'
AND c.prevver IS null
AND s.spacekey='<SPACEKEY>'SELECT DISTINCT um.lower_username
FROM user_mapping um
LEFT JOIN bodycontent bc ON bc.body LIKE CONCAT('%<ac:link><ri:user ri:userkey="',CONCAT(um.user_key,'%'))
JOIN content c ON c.contentid=bc.contentid
JOIN spaces s ON c.spaceid=s.spaceid
WHERE c.contenttype ='PAGE'
AND c.prevver IS null
AND s.spacekey='<SPACEKEY>'