How to list watchers in space, pages, blogposts in Confluence
Summary
The aim of this KB is to provide a list of watchers in space, pages, and blogposts.
Environment
Confluence Server and Data Center
Solution
The SQL queries below will return a list of watchers.
To view Space watchers
- This query will return the Username and Space Name:
MySQL
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;- This query will return the list of users watch the specific space:
MySQL
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 view Page watchers
This query will return Username, Page Name, Space Name:
MySQL
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 view Page watchers who are active users and with a link for each page
This query will return Space Name, Page Name, a link for each page, Username:
PostgreSQL
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 view Blogposts watchers
This query will return Username, Blog Name, Space Name :
MySQL
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 May 6, 2021
Powered by Confluence and Scroll Viewport.