Table: microsoft365_calendar - Query Microsoft 365 Calendars using SQL

Microsoft 365 Calendar is a resource within Microsoft 365 that provides users with a personal calendar for scheduling, meeting organization, and event tracking. It is a part of the Microsoft 365 suite which offers a range of productivity tools and services. The Microsoft 365 Calendar allows users to create, update, and manage events, invite attendees, and share their calendar with others.

Table Usage Guide

The microsoft365_calendar table provides insights into calendars within Microsoft 365. As an IT administrator, explore calendar-specific details through this table, including event schedules, attendee information, and recurring event patterns. Utilize it to uncover information about user scheduling habits, such as frequent meeting attendees, peak scheduling times, and the distribution of events throughout the week.

Important Notes

You must specify the user_id in the where or join clause (where user_id=, join microsoft365_calendar c on c.user_id=) to query this table.

Examples

Basic info

Analyze the settings of your Microsoft 365 calendar to understand its sharing and editing capabilities, as well as its default online meeting provider. This is particularly useful for assessing user permissions and managing meeting logistics.

select
  name,
  is_default_calendar,
  can_edit,
  can_share,
  default_online_meeting_provider
from
  microsoft365_calendar
where
  user_id = 'test@org.onmicrosoft.com';

select
  name,
  is_default_calendar,
  can_edit,
  can_share,
  default_online_meeting_provider
from
  microsoft365_calendar
where
  user_id = 'test@org.onmicrosoft.com';

List calendars the user can edit

Explore which calendars a user has editing privileges for, to manage and organize meetings effectively across different platforms within a Microsoft 365 environment. This is useful for understanding user permissions and streamlining online meeting scheduling.

select
  name,
  can_edit,
  default_online_meeting_provider
from
  microsoft365_calendar
where
  user_id = 'test@org.onmicrosoft.com'
  and can_edit;

select
  name,
  can_edit,
  default_online_meeting_provider
from
  microsoft365_calendar
where
  user_id = 'test@org.onmicrosoft.com'
  and can_edit = 1;

List permissions for each calendar

Determine the areas in which specific permissions are granted for each calendar in a Microsoft 365 organization. This query is useful for administrators who want to monitor and manage access rights, especially in larger organizations where multiple calendars are in use.

select
  c.name,
  p -> 'allowedRoles' as permission_allowed_roles,
  p -> 'emailAddress' as permission_email_address,
  p ->> 'id' as permission_id,
  p -> 'isInsideOrganization' as permission_inside_organization,
  p -> 'isRemovable' as permission_is_removable,
  p ->> 'role' as permission_role
from
  microsoft365_calendar as c,
  jsonb_array_elements(permissions) as p
where
  c.user_id = 'test@org.onmicrosoft.com';

select
  c.name,
  json_extract(p.value, '$.allowedRoles') as permission_allowed_roles,
  json_extract(p.value, '$.emailAddress') as permission_email_address,
  json_extract(p.value, '$.id') as permission_id,
  json_extract(p.value, '$.isInsideOrganization') as permission_inside_organization,
  json_extract(p.value, '$.isRemovable') as permission_is_removable,
  json_extract(p.value, '$.role') as permission_role
from
  microsoft365_calendar as c,
  json_each(c.permissions) as p
where
  c.user_id = 'test@org.onmicrosoft.com';

Schema for microsoft365_calendar

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
allowed_online_meeting_providers	jsonb		Represent the online meeting service providers that can be used to create online meetings in this calendar. Possible values are: unknown, skypeForBusiness, skypeForConsumer, teamsForBusiness.
calendar_group_id	text	=	The ID of the group.
can_edit	boolean		True if the user can write to the calendar, false otherwise.
can_share	boolean		True if the user has the permission to share the calendar, false otherwise. Only the user who created the calendar can share it.
can_view_private_items	boolean		True if the user can read calendar items that have been marked private, false otherwise.
change_key	text		Identifies the version of the calendar object. Every time the calendar is changed, changeKey changes as well.
color	text		Specifies the color theme to distinguish the calendar from other calendars in a UI. The property values are: auto, lightBlue, lightGreen, lightOrange, lightGray, lightYellow, lightTeal, lightPink, lightBrown, lightRed, maxColor.
default_online_meeting_provider	text		The default online meeting provider for meetings sent from this calendar. Possible values are: unknown, skypeForBusiness, skypeForConsumer, teamsForBusiness.
hex_color	text		The calendar color, expressed in a hex color code of three hexadecimal values, each ranging from 00 to FF and representing the red, green, or blue components of the color in the RGB color space.
id	text	=	The calendar's unique identifier.
is_default_calendar	boolean		True if this is the default calendar where new events are created by default, false otherwise.
is_removable	boolean		Indicates whether this user calendar can be deleted from the user mailbox.
is_tallying_responses	boolean		Indicates whether this user calendar supports tracking of meeting responses. Only meeting invites sent from users' primary calendars support tracking of meeting responses.
multi_value_extended_properties	jsonb		The collection of multi-value extended properties defined for the calendar.
name	text		The calendar name.
owner	jsonb		Represents the user who created or added the calendar.
permissions	jsonb		Represents the user who created or added the calendar.
tenant_id	text		The Azure Tenant ID where the resource is located.
title	text		Title of the resource.
user_id	text	=	ID or email of the user.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- microsoft365

You can pass the configuration to the command with the --config argument:

steampipe_export_microsoft365 --config '<your_config>' microsoft365_calendar