How to list watchers in space, pages, blogposts in Confluence DC
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
The aim of this KB is to provide a list of watchers in space, pages, and blogposts.
Solution
To list Space watchers
SELECT um.username,
s.spacename
FROM NOTIFICATIONS n,
user_mapping um,
SPACES s
WHERE n.spaceID IS NOT NULL
AND n.spaceID = s.spaceID
AND um.user_key = n.username;To list Space watchers of a specific Space
SELECT um.username
FROM NOTIFICATIONS n,
user_mapping um,
SPACES s
WHERE n.spaceID IS NOT NULL
AND n.spaceID = s.spaceID
AND um.user_key = n.username
AND s.spacekey = '<SPACEKEY>';To list Page watchers
SELECT um.username,
c.title,
s.spacename
FROM NOTIFICATIONS n,
user_mapping um,
SPACES s,
CONTENT c
WHERE n.contenttype = 'page'
AND n.contentID = c.contentID
AND um.user_key = n.username
AND c.spaceID = s.spaceID;To list Page watchers who are active users and with a link for each page
SELECT s.spacename,
c.title as page_title,
CONCAT('<your-confluence-URL>/pages/viewpage.action?pageId=', n.contentid) AS link,
u.username
FROM notifications n
JOIN user_mapping u ON u.user_key=n.username
JOIN content c on n.contentID=c.contentID
JOIN spaces s ON c.spaceID=s.spaceID
JOIN cwd_user cu ON u.username=cu.user_name
WHERE cu.user_name NOT IN (SELECT user_name
FROM cwd_user
WHERE active = 'F')
GROUP BY u.username,s.spacename,c.title,n.contentid
ORDER BY s.spacename,c.titleTo list Blogposts watchers
SELECT um.username,
c.title,
s.spacename
FROM NOTIFICATIONS n,
user_mapping um,
SPACES s,
CONTENT c
WHERE n.contenttype = 'blogpost'
AND n.contentID = c.contentID
AND um.user_key = n.username
AND c.spaceID = s.spaceID;
Last modified on Dec 6, 2024
Powered by Confluence and Scroll Viewport.