購読者とカレンダーの作成者を取得する方法
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
The purpose of this document is to provide the steps to get a list of users subscribed to a specific calendar, or the creators of a calendar.
If you are interested in getting a list of the users watching a specific calendar, please check How to get the users Watching a Calendar
ソリューション
登録者
Run the following query to get a list of the subscribers of every Calendar in your Confluence site:
SELECT tc."NAME" AS "Calendar Name", um."username" AS "Subscriber"
FROM "AO_950DC3_TC_SUBCALS" tc
LEFT JOIN "user_mapping" um ON um."user_key" = tc."CREATOR"
WHERE "PARENT_ID" IS NULL AND "SUBSCRIPTION_ID" IS NOT NULL
ORDER BY 1,2
If you need to filter by Calendar name, just add a WHERE clause based on tc."NAME" column.
Creators
Replace the <calendar_name> with the name of the calendar that you wish to find its creator.
select BANDANACONTEXT from BANDANA where BANDANAVALUE like '%X-WR-CALNAME:<calendar_name>%';Now Run the next query. Replace the <BANDANACONTEXT> with the previous query result
select BANDANAVALUE from BANDANA where BANDANACONTEXT = '<BANDANACONTEXT>';The output of this query should be similar to the one below:
| <string>{"id":"1c0ecd7a-0cf8-4db2-a3ec-11706f28ddfe","color":"subcalendar-turquoise","description":"ert","name":"trte","spaceKey":"","creator":"admin","timeZoneId":"Asia/Calcutta"}</string> | | <string>{"id":"6dc2caf6-5261-4de2-a3e7-eff19ffbebba","color":"subcalendar-blue2","description":"sdfsd","name":"dfsg","spaceKey":"","creator":"admin","timeZoneId":"Asia/Calcutta"}</string> |
From the result, you can see the creator name, in this case it is 'admin'
Run the following query to get the users who created every Calendar in your Confluence site:
SELECT tc."NAME" AS "Calendar Name", um."username" AS "Creator", to_timestamp(CAST(tc."CREATED" AS bigint) / 1000) AS "Creation Date"
FROM "AO_950DC3_TC_SUBCALS" tc
LEFT JOIN "user_mapping" um ON um."user_key" = tc."CREATOR"
WHERE "PARENT_ID" IS NULL AND "SUBSCRIPTION_ID" IS NULL
ORDER BY 1,2SELECT um.lower_username as creator, tc.NAME as calendar_name, tc."SPACE_KEY" as Space, from_unixtime(round(tc.created/1000)) as creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator;SELECT um.lower_username as creator, tc.NAME as calendar_name, tc."SPACE_KEY" as Space, dateadd(second,cast(cast(tc.CREATED AS nvarchar(255)) AS bigint)/1000,'19700101 00:00:00:000') as creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator;SELECT um.lower_username as creator, tc.NAME as calendar_name, tc."SPACE_KEY" as Space, to_date('01.01.1970','dd.mm.yyyy') + to_number(tc.created)/1000/60/60/24 AS creation_date
FROM AO_950DC3_TC_SUBCALS tc
JOIN user_mapping um ON um.user_key = tc.CREATOR
WHERE tc.PARENT_ID IS NULL
ORDER BY creator; If you need to filter by Calendar name, just add a WHERE clause based on tc."NAME" column.
There is an open suggestion to implement a functionality to know who is the creator of a calendar in the UI: