How to find all events related to a custom event type in Confluence Team Calendars
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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:
- CONFSERVER-51114 - Getting issue details... STATUS
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:
SELECT * 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:
SELECT * 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: (Archived) How to find the Creator of a Calendar in Team Calendars 4.1 and earlier
Additionally, you can also list all Calendar events, including event type, calendar, and relevant dates with the following query:
SELECT 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"