How to Audit a User's Team Calendar Views and Watches Using the Database
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 allows users to add calendars to their personal "My Calendars" view (within "Calendars" in the top bar), and additionally to watch these calendars which will allow them to receive email notifications when events are added or updated.
For auditing or troubleshooting purposes, it may be useful for an administrator to see which calendars have been added to a user's personal view or determine which calendars the user is watching for notifications. The article will describe how to do this using SQL queries and the Confluence database.
Solution
ℹ️
Please note that each of the queries below contain placeholders, denoted in <angle brackets>.
Step 1 - Obtain the user_key value for the relevant user
1
SELECT * FROM user_mapping WHERE lower_username = '<username>';Step 2 - Obtain the user's calendar configurations
1
SELECT * FROM OS_PROPERTYENTRY WHERE entity_name = 'USERPROPS-<user_key_from_query1>' AND entity_key = 'calendar';Look for the following from this output:
subCalendarsInView - Each calendar ID in this segment denotes a calendar that has been added to the user's My Calendars view
watchedSubCalendars - Each calendar ID in this segment denotes a calendar that the user is watching, and will receive email notifications for event adds/updates for
Step 3 - Identify the calendar names based on the calendar IDs
1
SELECT NAME, SPACE_KEY FROM AO_950DC3_TC_SUBCALS WHERE ID = '<calendar_id_from_query2>';If you are using Postgres, for this query the column and table names will need to be wrapped in double quotes (due to case sensitivity).
1
SELECT "NAME", "SPACE_KEY" FROM "AO_950DC3_TC_SUBCALS" WHERE "ID" = '<calendar_id_from_query2>';Was this helpful?