How to list watchers in space, pages, blogposts in Confluence DC
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
MySQL Query to list Space watchers:
1
2
3
4
5
6
7
8
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;MySQL Query to list Space watchers of a specific Space:
1
2
3
4
5
6
7
8
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>';MySQL Query to list Page watchers:
1
2
3
4
5
6
7
8
9
10
11
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;PostgreSQL Query to list Page watchers who are active users and with a link for each page:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.titleMySQL Query to list Blogposts watchers:
1
2
3
4
5
6
7
8
9
10
11
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;Was this helpful?