Practical Graph: Reporting Room Mailbox Weekly Schedules

Use PowerShell to Extract, Format, and Email Weekly Room Schedules to Designated Recipients

A reader asked about the possibility of emailing details of events scheduled for a room mailbox to a distribution list. It’s easy to envisage scenarios where this capability would be useful. For instance, a team might be responsible for looking after a set of conference rooms at a location and wish to know about events a week or even a day ahead. In any case, no such feature exists in Exchange Online, so let’s figure out what’s possible with PowerShell.

The initial steps in the script might be:

• Calculate the reporting period. This example reports events for the next week, starting on Monday.
• Find all room mailboxes (perhaps limited to a certain location).
• For each room, check if any events exist for the reporting period. If any are found, extract their details.

After finding the data, it then becomes a matter of making the information available in different ways. For instance, you might want a printed report of events for the week or, as originally requested, the script should send email to update people about events for specific rooms.

The script uses a registered Entra ID app to authenticate and secure an access token to allow it to query Graph data. The app must have consent to use the following Graph permissions:

• Calendar.Read: Read events from the calendar folder.
• Place.Read.All: Read details of places like room mailboxes.
• User.Read.All: Read details of user accounts.
• MailSend.All: Send emailed weekly schedules.

The Mail.Send.All permission allows email to be sent from any mailbox in the tenant. To limit access, consider using RBAC for Applications to restrict access to the mailbox used to distribute the weekly schedule updates.

You can download the script from GitHub.

Figuring Out Reporting Dates

Last year, I wrote about extracting statistics for room mailboxes from the event data held in their calendars. The script uses Graph API requests to find the room mailboxes and query events. Conceptually, the initial steps outlined above are like those performed to extract room statistics. The difference is that analyzing room usage is a lookback on data whereas this script is all about knowing what events a room will host in the future.

I decided that the reporting period should be from the next Monday to the Monday afterward. This code gets the current date and figures out if today is Monday. If it is, the start date for the reporting period is today. If not, the start date is the following Monday. After figuring out the dates, the code creates two variables in sortable format for use when querying the room mailboxes for calendar events.

[datetime]$Today = (Get-Date).date
# If today is Monday, we report on the current week. Otherwise, we report on the next week
If ($Today.DayOfWeek.value__ -eq 1) {
    [datetime]$NextMonday = Get-Date($Today) -format 'dd-MMM-yyyy'
} Else {
    [datetime]$NextMonday = Get-Date($Today.AddDays(2 + $Today.DayOfWeek.value__)) -format 'dd-MMM-yyyy'
}
# Calculate the following Monday after the start date
[datetime]$NextMondayAfter = Get-Date($NextMonday).AddDays(7) -format 'dd-MMM-yyyy'

# Create sortable dates that can be used with the Graph request to find calendar data
$StartDate = Get-Date($NextMonday) -format s
$EndDate = Get-Date($NextMondayAfter) -format s

Finding Room Mailboxes

Various techniques exist to find the set of room mailboxes. You could use the Get-ExoMailbox cmdlet from the Exchange Online management module to run a command like:

[array]$RoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited

There’s no need to load the Exchange Online management module just to find the set of room mailboxes. Instead, the script uses the Graph Places API to retrieve room mailboxes with code like:

$Uri = "https://graph.microsoft.com/V1.0/places/microsoft.graph.room"
[Array]$RoomMailboxes = Get-GraphData -Uri $Uri -AccessToken $Token

The reference to Get-GraphData is not for a cmdlet. Instead, it’s a function to make it easier to execute the Graph requests because the function takes care of error handling and pagination. The result is an array containing the set of room mailboxes. If you only want to report calendar events for certain rooms, apply a filter to select the target mailboxes.

Processing Calendars

To retrieve events from a room mailbox calendar, the script builds the URI with parameters such as the email address of the mailbox and the start and end date for the period to query.

$Uri = ("https://graph.microsoft.com/V1.0/users/{0}/calendar/calendarView?startDateTime={1}&endDateTime={2}" -f $Room.emailAddress, $StartDate, $EndDate)
[array]$CalendarData = Get-GraphData -Uri $Uri -AccessToken $Token

An example of a URI generated to query the calendar folder is:

https://graph.microsoft.com/V1.0/users/SFRoom101@Office365itpros.com/calendar/calendarView?startDateTime=2024-04-08T00:00:00&endDateTime=2024-04-15T00:00:00

After fetching calendar events that match the reporting period, the script extracts details such as the organizer’s name and email address, the meeting subject, start and end time, duration (in minutes), and number of attendees. By default, Exchange Online replaces the meeting subject with the organizer’s name. This is done to preserve the privacy of the meeting when details are viewed by people who only have access to the room calendar (individual attendees see the full information about a meeting).

An arguable case exists that meetings scheduled in a shared physical resource should reveal whatever subject the meeting organizer gives the event. If you agree, you can update the calendar processing settings for room mailboxes to preserve the meeting subject.

The output created by processing room mailboxes is an HTML report (Figure 1). It’s possible that an emailed reminder is not required for each room, so this report acts as an overall summary.

Emailing Weekly Schedules

The original request asked for a weekly schedule to be sent to a distribution list. The first item to resolve is how to store the email address of the distribution list to receive updates for a room mailbox. The easy solution is to store the email address in one of the 15 custom attributes available for mail-enabled recipients, including room mailboxes. I elected to use CustomAttribute13.

Previously the script fetched details of room mailboxes. This information includes the primary SMTP address of each mailbox but doesn’t include the user principal name or account identifier. While it’s best practice to have the same value for the primary SMTP address and user principal name, that guideline is more likely to be respected for user accounts than for room mailboxes. Accordingly, the script uses a filtered lookup against the Users API to retrieve the set of custom attributes (known as onPremisesExtensionAttributes to Entra ID) and extract the value of CustomAttribute13.

$Uri = ("https://graph.microsoft.com/v1.0/users?`$filter=mail eq '{0}'&`$select=displayName,id,onPremisesExtensionAttributes" -f $Room.emailaddress)
        [array]$Data = Get-GraphData -Uri $Uri -AccessToken $Token
        If ($Data) {
            [array]$Attributes = $Data | Select-Object -ExpandProperty OnPremisesExtensionAttributes
            # Extract email address from custom attribute 13
            $EmailAddress = $Attributes.extensionAttribute13
        }

If the script finds an email address, it constructs and sends a mail message. Normally, I would use the Send-MgUserMail cmdlet from the Microsoft Graph PowerShell SDK to send email, but this script uses Graph requests, so it posts a JSON body to the SendMail API using the shared mailbox as the sender. Here’s the code:

$MsgSubject = ("Weekly Schedule for the {0} room from {1} to {2}" -f $Room.DisplayName, $StartDate, $EndDate)
$MsgBody = @{
   Content = "$($HtmlMsgBody)"
   ContentType = 'html'  
}
[array]$Recipient = @{
   emailAddress = @{address = $EmailAddress}
}  
$Message =  @{subject           = $MsgSubject}
$Message += @{toRecipients      = $Recipient } 
$Message += @{body              = $MsgBody}
$Params   = @{'message'         = $Message}
$Params  += @{'saveToSentItems' = $True}
$Params  += @{'isDeliveryReceiptRequested' = $True}
$Uri = ("https://graph.microsoft.com/v1.0/users/{0}/sendMail" -f $msgfrom)
$BodyParams = $Params | ConvertTo-Json -Depth 10
Invoke-RestMethod -Uri $Uri -Method POST -Body $BodyParams -Headers $Headers -ContentType 'application/JSON'

Figure 2 is an example of an email sent with the weekly schedule for a room.

Goal Accomplished

The script was put together by assembling components from different scripts and tweaking the result. This is the kind of task that Copilot for GitHub is useful for because it can leverage a vast collection of PowerShell scripts stored in GitHub repositories. However, generative AI can only produce code based on what’s gone before. Knowing how Microsoft 365 works and the innovative use of data available within a tenant remains a human skill that I don’t think AI will master for quite some time to come.