I want to find all calendars that haven't been used in the last year or so and remove them.
My end users have created probably 150 or more calendars over the last decade, and unless the space was depreciated, they are still all there. I've a staging instance locally working on a SQL backend that I don't mind querying (Note: for identification purposes only, I will use the GUI tools for actually exporting/deleting calendars), and I'm not sure I've figured out the schema - an ERD would be great. Of particular confusion is what the Parent_ID field is supposed to be pointing at, and why is the Space_key mostly NULL? I have a lot of subcalendars with the imaginative name of "Team Calendar" so I'm not sure which ones roll up together.
My code thus far - or is there an in app way to get this information?
With Mostrecent as (
Select e.SUB_CALENDAR_ID, Max(dateadd(S, convert(int,left(e.START,10)), '1970-01-01 00:00:00' )) as LastStart
from [dbo].[AO_950DC3_TC_EVENTS] e
group by e.SUB_CALENDAR_ID
)
Select cal.[NAME], cal.COLOUR, cal.[DESCRIPTION], cal.SPACE_KEY, e.SUB_CALENDAR_ID, e.LastStart , u.lower_username, cal.LAST_MODIFIED
, s.SPACENAME, cal.ID, cal.PARENT_ID
from [AO_950DC3_TC_SUBCALS] cal
left join Mostrecent e on e.SUB_CALENDAR_ID=cal.ID
left join [dbo].[user_mapping] u on cal.CREATOR = u.user_key
left join SPACES s on cal.SPACE_KEY=s.SPACEKEY
You might just download this app and get the information you need that way. https://marketplace.atlassian.com/apps/1214913/calendar-insights?hosting=server&tab=overview
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.