How to get the users Watching a Calendar
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 purpose of this document is to provide the steps to get a list of users watching a specific calendar.
If you are interested in getting a list of the users subscribed to a specific calendar or the creator of a calendar, please check How to get the Subscribers and Creator of a Calendar
Solution
Calendar Watchers
Run the following query to get a list of the IDs of the calendar which are used to identify watches. Copy the subscription and parent IDs that are returned.
⚠️ The following queries were tested on PostgreSQL.
1 2 3 4SELECT A."PARENT_ID", A."SUBSCRIPTION_ID" FROM "AO_950DC3_TC_SUBCALS" A WHERE "NAME" = '<REPLACE-WITH-CALENDAR-NAME>' AND ("PARENT_ID" IS NOT NULL OR "SUBSCRIPTION_ID" IS NOT NULL);Run the either of the queries below with the parent/subscription IDs returned from the first query to get list of users who are watching the calendar. For multiple IDs, additional OR statements will need to be added to the query.
Query for single parent ID
1 2 3 4 5 6SELECT U.USERNAME FROM USER_MAPPING U WHERE USER_KEY IN ( SELECT SUBSTRING(A.ENTITY_NAME,11, 32) FROM OS_PROPERTYENTRY A WHERE (TEXT_VAL LIKE '%"watchedSubCalendars":[%<REPLACE-WITH-ID>%'));Query for multiple parent IDs - add more OR statements for more IDs
1 2 3 4 5 6 7SELECT U.USERNAME FROM USER_MAPPING U WHERE USER_KEY IN ( SELECT SUBSTRING(A.ENTITY_NAME,11, 32) FROM OS_PROPERTYENTRY A WHERE (TEXT_VAL LIKE '%"watchedSubCalendars":[%<REPLACE-WITH-ID>%' OR TEXT_VAL LIKE '%"watchedSubCalendars":[%<REPLACE-WITH-ID>%'));
Space Watchers
Users also automatically watch a calendar if the calendar is related to a space and the user is a watcher of that space. The following query returns list of users watching a calendar that is related to a space.
1
2
3
4
SELECT USER_MAPPING.USERNAME
FROM NOTIFICATIONS,USER_MAPPING,SPACES
WHERE NOTIFICATIONS.SPACEID IS NOT NULL AND NOTIFICATIONS.SPACEID=SPACES.SPACEID AND USER_MAPPING.USER_KEY=NOTIFICATIONS.USERNAME
AND SPACEKEY IN (SELECT C."SPACE_KEY" FROM "AO_950DC3_TC_SUBCALS" C WHERE "NAME" = '<REPLACE-WITH-CALENDAR-NAME>' AND "SPACE_KEY" IS NOT NULL);Related Feature Requests
The following suggestion has been created related to this issue. New features are implemented following the Atlassian Implementation of New Features Policy.
Was this helpful?