Purpose

The purpose of this document is to provide the steps to get a list of users watching a specific calendar.

Solution

Calendar Watchers

1. 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.

   SELECT 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);

2. 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

   SELECT 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

   SELECT 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.

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.

CONFSERVER-51028 - Getting issue details... STATUS