How to Audit a User's Team Calendar Views and Watches Using the Database
Purpose
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
SELECT * FROM user_mapping WHERE lower_username = '<username>';
Step 2 - Obtain the user's calendar configurations
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
SELECT NAME, SPACE_KEY FROM AO_950DC3_TC_SUBCALS WHERE ID = '<calendar_id_from_query2>';
Last modified on Aug 1, 2018
Powered by Confluence and Scroll Viewport.