How to find all events related to a custom event type in Confluence Team Calendars
要約
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:
環境
Confluence Server and Data Center with Team Calendars.
ソリューション
- 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: How to find the Creator of a Calendar in Team Calendars for Confluence
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"
Last modified on Mar 10, 2023
Powered by Confluence and Scroll Viewport.