How to get a list of users mentioned in a space/page and
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
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.
ソリューション
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>'