How to get the Subscribers and Creator of 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 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.
Solution
Subscribers
For Team Calendars 4.2 and later
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
For Team Calendars 4.1 and earlier
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> |
For Team Calendars 4.2 and later
Run the following query to get the users who created every Calendar in your Confluence site:
⚠️ If you need to filter by Calendar name, just add a WHERE clause based on tc."NAME" column.
Postgres query
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,2;MySQL query
SELECT 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 AND tc.SUBSCRIPTION_ID IS NULL
ORDER BY creator;SQL Server query
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 AND tc.SUBSCRIPTION_ID IS NULL
ORDER BY creator;Oracle query
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 AND tc.SUBSCRIPTION_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:
Was this helpful?