How to get list of users watching a calendar

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

To get a list of users watching a calendar.

Solution


  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.

    (warning) 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);

Last modified on Jun 20, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.