Discover the Usage of Meeting Rooms
With online meetings being the norm, an organization might wonder how much use conference and meeting rooms get. You could monitor usage by checking a room on an ongoing basis to see if it’s occupied, by whom, and how long the occupants spend there. Monitoring the ins and outs of a room is not an attractive or fun task, but the checking can be automated by using Graph API requests against the calendars of room mailboxes to find information about events scheduled with the room mailbox, like the one shown in Figure 1. The data won’t include informal meetings where people show up and use a room without a formal booking, but it should give a good oversight on how busy meeting rooms are.
In this article, I describe the script I wrote to extract meeting information from room calendars to report some usage statistics. The principal illustrated is how to access and use the information. Afterward, it’s up to you how you use the data.
Update: This article describes how the script was enhanced to add daily usage pattern charts.
I often use the Microsoft Graph PowerShell SDK cmdlets to interact with the Graph. However, in this case, I use a registered Azure AD app in the script to hold the Graph Calendar.Read.All and Place.Read.All application permissions needed to access the calendars.
Although it’s possible that a script like this will run as a scheduled task, I wanted to be able to run the script interactively, and interactive access with the Microsoft Graph PowerShell SDK is limited to delegate access. In other words, the code can report items in the calendar of the signed-in user but have no access to the calendars belonging to the meeting rooms. Application permission is necessary to gain access to the folders in other users’ mailboxes. The same issue drove the choice of Graph API requests in the mailbox report script.
Graph SDK cmdlets can use application permissions when run in a non-interactive script. For instance, when using the SDK cmdlets in an Azure Automation runbook, authentication happens using the automation account. The service principal of the automation account holds the permissions that the script needs to access the data. See this article for more information about Graph permissions.
Exchange Online application access policies can control apps that access mailbox contents by limiting access to specific mailboxes. In this case, an application access could define that the app can only access the room mailboxes.
Listing Room Mailboxes and Workspaces
After creating a registered app in the Azure AD admin center, assigning the two application permissions, and consenting to their use, it’s time to start by finding the list of room mailboxes to process. To do this, the script uses the Graph Places API and runs this query:
$Uri = "https://graph.microsoft.com/beta/places/microsoft.graph.room"
The query returns full-blown room mailboxes of the type used to host meetings but does not include workspaces. These are smaller locations (like hot desks) that people can book. In Exchange terms, these objects are room mailboxes marked as workspaces. The Places API treats workspaces differently because they are presented as separate objects in Outlook’s Room Finder. To retrieve workspaces, the API request is:
$Uri = "https://graph.microsoft.com/beta/places/microsoft.graph.workspace"
To create a list of both room mailboxes and workspaces, we combine the sets of data returned by the two API requests. Another way of fetching both room mailboxes and workspaces is to use the Get-EXOMailbox cmdlet:
[array]$RoomMailboxes = Get-EXOMailbox -RecipientTypeDetails RoomMailbox
The downside of using Get-EXOMailbox is that the script must connect to the Exchange Online management module. In addition, the query below to fetch calendar data changes so that instead of using the emailAddress property, it must use the primarySMTPaddress property.
Extracting Calendar Data
We now need to loop through the set of mailboxes to extract meeting information from each calendar. The URI for the query is:
$Uri = "https://graph.microsoft.com/V1.0/users/" + $Room.emailAddress + "/calendar/calendarView?startDateTime=" + $Start + "&endDateTime=" + $End
The identifier for the calendar is the primary SMTP address of the mailbox. The CalendarView parameter sets a start and end date for the information to retrieve. These variables are set earlier in the script to look for events over the last 60 days (easily changed). The resulting URI looks like this:
The results of the call populate an array of events. The script unpacks each event to fetch the information we want to analyze, such as the organizer, duration, online meeting status, the number of attendees, and so on.
After extraction, an event looks like this:
Room : Board Conference Room Mail : Board.Room@office365itpros.com Type : singleInstance Organizer : Ken Bowers OrganizerEmail : Ken.Bowers@office365itpros.com Created : 23/10/2022 17:39 Modified : 23/10/2022 18:00 TimeZone : Pacific Standard Time Subject : Ken Bowers AllDay : False Online : False OnlineProvider : unknown Start : 24/10/2022 15:00 End : 24/10/2022 15:25 Duration : 25 Location : Board Conference Room RequiredAttendees : Ken Bowers, James Ryan, Sean Landy OptionalAttendees : TotalAttendees : 3 Required : 3 Optional : 0 TotalAtEvent : 4 EventId : AAMkADE0M2EyZGI3LTMzMWEtNDkxNC04ZjczLWRiMDBhMWViYTJjYQBGAAAAAABic4Kwcjs0Qre76zx9826DBwCVwQhtkGArSqwD
After processing all the room mailboxes, the script summarizes what it finds. Some simple calculations tell us the count of events, how many are online, the most popular room and most active meeting organizers, and a summary snapshot for each room. Some simple formatting displays the statistics (Figure 2).
The data only includes meetings accepted by room calendars. Canceled meetings are excluded.
More to Do
Obviously, this data isn’t very exciting because it covers just 28 meetings. However, the principal is the important thing, and once you can access and retrieve data, you can process it any way you want, including exporting the data to Power BI to visualize it in different ways
The script I used is available from GitHub. Feel free to improve it in any way that you can.