How to find all events related to a custom event type in Confluence Team Calendars

Still need help?

The Atlassian Community is here for you.

Ask the community

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

  1. Identify the name of your custom event type in your desired calendar 
  2. 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>')); 


    1. 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')); 
  3. 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 4.1 and earlier
  4. 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

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.