How to find all events related to a custom event type in Confluence Team Calendars
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
Team Calendars does not have options to generate a report of all events in a custom event type. However, there is currently an enhancement request to bring this functionality to Confluence:
Environment
Confluence Server and Data Center with Team Calendars.
Solution
Identify the name of your custom event type in your desired calendar
Run the following query, replacing the "Title" with the correct title:
1SELECT * FROM "AO_950DC3_TC_EVENTS" WHERE "SUB_CALENDAR_ID"=(SELECT "ID" FROM "AO_950DC3_TC_SUBCALS" WHERE "USING_CUSTOM_EVENT_TYPE_ID"=(SELECT "ID" FROM "AO_950DC3_TC_CUSTOM_EV_TYPES" WHERE "TITLE"='<EVENT TYPE TITLE>'));For example, if your custom calendar was titled "Vacation" you would run the following:
1SELECT * FROM "AO_950DC3_TC_EVENTS" WHERE "SUB_CALENDAR_ID"=(SELECT "ID" FROM "AO_950DC3_TC_SUBCALS" WHERE "USING_CUSTOM_EVENT_TYPE_ID"=(SELECT "ID" FROM "AO_950DC3_TC_CUSTOM_EV_TYPES" WHERE "TITLE"='Vacation'));
If you'd like to identify the creator of the calendar event you can review our How to get the Subscribers and Creator of a Calendar
Additionally, you can also list all Calendar events, including event type, calendar, and relevant dates with the following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18SELECT E."SUMMARY" AS Event_Name, E."DESCRIPTION" as Event_Description, case when S."STORE_KEY" = 'com.atlassian.confluence.extra.calendar3.calendarstore.generic.GenericLocalSubCalendarDataStore' then 'Events' when S."STORE_KEY" = 'com.atlassian.confluence.extra.calendar3.calendarstore.generic.BirthdaySubCalendarDataStore' then 'Birthday' when S."STORE_KEY" = 'com.atlassian.confluence.extra.calendar3.calendarstore.generic.LeaveSubCalendarDataStore' then 'Leave' when S."STORE_KEY" = 'com.atlassian.confluence.extra.calendar3.calendarstore.generic.TravelSubCalendarDataStore' then 'Travel' when S."STORE_KEY" = 'com.atlassian.confluence.extra.calendar3.calendarstore.generic.CustomSubCalendarDataStore' then C."TITLE" end as Event_type, S."NAME" AS Calendar_Name, U.username AS Organiser, to_timestamp(E."CREATED"/1000) AS Created, to_timestamp(E."START"/1000) AS Start_Date, to_timestamp(E."END"/1000) AS End_Date FROM "AO_950DC3_TC_EVENTS" AS E INNER JOIN user_mapping AS U ON U.user_key = E."ORGANISER" INNER JOIN "AO_950DC3_TC_SUBCALS" AS S ON S."ID" = E."SUB_CALENDAR_ID" LEFT JOIN "AO_950DC3_TC_CUSTOM_EV_TYPES" AS C ON S."USING_CUSTOM_EVENT_TYPE_ID" = C."ID"
Was this helpful?