How to get list of users watching a calendar

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

目的

To get a list of users watching a calendar.

ソリューション


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

関連する機能リクエスト

The following suggestion has been created related to this issue. New features are implemented following the Atlassian Implementation of New Features Policy.

CONFSERVER-51028 - 課題詳細を取得中... ステータス


最終更新日 2022 年 9 月 27 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.