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.

Scheduling a meeting in a room mailbox
Figure 1: Scheduling a meeting in a room mailbox

Script Prerequisites

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.

Cybersecurity Risk Management for Active Directory

Discover how to prevent and recover from AD attacks through these Cybersecurity Risk Management Solutions.

Learn More!

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:

https://graph.microsoft.com/V1.0/users/Board.Room@office365itpros.com/calendar/calendarView?startDateTime=2022-09-28T18:48:02&endDateTime=2022-11-28T18:48:02

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).

Statistics for meetings scheduled in room mailboxes
Figure 2: Statistics for meetings scheduled in room mailboxes

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.

Cybersecurity Risk Management for Active Directory

Discover how to prevent and recover from AD attacks through these Cybersecurity Risk Management Solutions.

Learn More!

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Bill Wheeler

    Hi Tony,

    Again, wanted to thank you for posting this code. It was extremely helpful. I did run into a but in the graph when I ran this in our production environment. I found that I wasn’t getting all of the conference rooms. Pulling the data in the Exchange Module, I had 194 rooms but when I pulled running using Graph, i was only able to pull down 100 rooms. the issue is being escalated to engineering. I was able to target room lists in the Uri as a workaround, which worked very nicely.

    Thanks again!

    1. Avatar photo
      Tony Redmond

      Hi Bill,

      The script uses a default Graph query, which means that 100 items are returned, which is why I use a function to return additional data if more is available (Get-GraphData). I’m surprised that it didn’t return all available room mailboxes. Although you’ve got a workaround, it would be good to see if the problem is with the API. You could try adding a Top clause to the query to return more than the default 100 items.

      $Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.room?`$top=200”

      Then run the command to fetch the data (or use [array]$Data = Invoke-MgGraphRequest -Uri $Uri) to see how many objects are returned.

  2. Bill Wheeler

    Never mind :). User error!

    I’ve found that if you ‘accidentally’ click conent on behalf of the org, it fails. I ended up revoking org permissions and it worked like a champ.

    Thanks again!

  3. Bill Wheeler

    Tony – thank so much for the script. I seem to be running into the same challenge as others of getting (Response status code does not indicate success: 403 (Forbidden)). I am running with Application permissions: Calendars.Read and Place.Read.All. I see the rooms successfully loaded into the array.

  4. Rob Melnick

    Tony, thanks, this script is pretty much exactly what we have been trying to find for awhile now. I have ran it several times and we get interesting results. I ran it on 180day timeframe , and it seems to be mixed as to what rooms it attaches to. We also get an error after the room listing and before the calculations. The calculations return fine for the rooms that are gathered.

    Found 10 calendar events for the Lynchburg Conference Room room
    Found 35 calendar events for the Lynchburg Training Room room
    Found 15 calendar events for the Tyler HR Conference Room room
    Get-GraphData : System.Net.WebException: The remote server returned an error: (404) Not Found.
    at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
    at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()
    At C:\Users\..\…, Inc\Documents\Exchange PowerShell Scripts\ROOMS_REPORT.ps1:129 char:28
    + … [array]$CalendarData = Get-GraphData -Uri $Uri -AccessToken $Token
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-GraphData

    Meeting Room Statistics from 7/15/2022 9:35:34 AM to 1/12/2023 9:35:34 AM
    ———————————————————————–

    1. Avatar photo
      Tony Redmond

      What email address is in $URI when the call fails? It might be that the lookup against the Places data returns bad information.

      You could look at the $RoomMailboxes array after the failure to find which mailbox is being processed after the last successful room (looks like that’s the Tyler HR Conference room).

      When you find the room, check that it exists… It might not! That’s what the error says…

        1. Avatar photo
          Tony Redmond

          First – for some reason, the URI that you show has no user information in it. There should be something like Room1@mydomain.com between users and calendar. Like /users/room@mydomain.com/calendar

          One quick fix would be to include a line like $RoomMailboxes = $RoomMailboxes | Where-Object {$_.EmailAddress -ne $Null} to eliminate any rooms without an email address from the array. However, I am curious as to why a room mailbox would exist without an email address… or why a lookup against the Places API returns such data. Places is a beta API so maybe that’s one of the reasons why it’s not in the production Graph APIs.

          You could also look at the array to see what’s there before making any changes:

          $RoomMailboxes | Format-List

          The key to processing a selected list of rooms would be to build the input array with the rooms that you want to process. For instance, you could build the array with Get-ExoMailbox -RecipientTypeDetails RoomMailbox and a filter against a custom attribute.

  5. Alex

    Hello Guys, Thanks for sharing. I having the same problem as VibHu, check all the steps in the discussion but still I’m getting this error:
    Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.

    My only two permissions are : “Place.Read.All” and “Calendars.Read”

    1. Avatar photo
      Tony Redmond

      Did you check that you’re using application rather than delegate permissions?

  6. Vibhu Gupta

    Thank you Tony,

    Removed all permissions and left only “Place.Read.All” and “Calendars.Read”. The script now works.
    Surprising that extra permissions would have such unexpected behaviour.
    Again, thank you very much for the help solving this.

    1. Avatar photo
      Tony Redmond

      As I say, Graph permissions can be a real bear to deal with…

  7. Vibhu Gupta

    This is bizarre, I don’t see “Calendars.Read.All” it does not appear in the list.

    Only the below permissions are available when I search for “Calendars”

    Calendars.Read
    Read calendars in all mailboxes

    Calendars.ReadBasic.All
    Read basic details of calendars in all mailboxes

    Calendars.ReadWrite
    Read and write calendars in all mailboxes

    1. Avatar photo
      Tony Redmond

      Sorry. My mistake – it is Calendars.Read (Graph permissions can be a bit of a nightmare to navigate at times). I think that the Calendars.ReadBasic.All permission is getting in the way. I would start over and remove all permissions from the app except Calendars.Read and Places.Read.

      Then check your token to see if those permissions are present and then see if the code works.

  8. Vibhu Gupta

    Here are all the roles extracted from the token:
    “rh”: “0.ASAA3glhSrDTFkCO3RY0kzd99gMAAAAAAAAAwAAAAAAAAAAgAAA.”,
    “roles”: [
    “Place.Read.All”,
    “User.ReadBasic.All”,
    “Calendars.Read”,
    “Directory.Read.All”,
    “User.Read.All”,
    “Calendars.ReadBasic.All”
    ],

    I have added few extra while testing.

    Any ideas what could be the issue here?

    1. Avatar photo
      Tony Redmond

      I use Calendars.Read.All. You have Calendars.Read and Calendars.ReadBasic.All (a different permission that’s used for free/busy information). Try assigning Calendars.Read.All

  9. Vibhu Gupta

    I have added Directory.Read.All as per your recommendation, no difference unfortunately.

  10. Vibhu Gupta

    This is using your powershell script:

    .\ReportRoomMailboxUsage.ps1
    Scanning room mailboxes for calendar events from 28/12/2022 15:04:31 to 05/01/2023 15:04:31
    Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.
    The URL is the below I believe. (with substituted account)
    Uri: https://graph.microsoft.com/V1.0/users/user@company.com/calendar/calendarView?startDateTime=2022-12-28T18:48:02&endDateTime=2022-12-31T18:48:02

    Thanks for trying to help

    Vibhu

    1. Avatar photo
      Tony Redmond

      Well, I just tested and looked at the token (you can paste it into jwt.io to test your token) and I see that the app has just two permissions:

      “oid”: “f7165c54-9331-497a-8da5-5ae53e8b263d”,
      “rh”: “0.AVwAPzFitvwUokOaetLif080eAMAAAAAAAAAwAAAAAAAAABcAAA.”,
      “roles”: [
      “Place.Read.All”,
      “Calendars.Read”
      ],

    2. Vishal

      fix the emailaddress to primarysmtpaddress in $uri. It will work

      1. Avatar photo
        Tony Redmond

        The Graph doesn’t return a property called PrimarySmtpAddress for the Places API. You get emailAddress, which is what the script uses.

        id : ebe9be7a-0e33-481f-bfea-1e10735aaaf6
        emailAddress : Board.Room@office365itpros.com
        displayName : Board Conference Room
        phone : +353 1 2070000
        nickname : Board Conference Room

        However, if you find room mailboxes with Get-ExoMailbox -RecipientTypeDetails RoomMailbox, you can use PrimarySmtpAddress because that’s what the cmdlet returns.

  11. Vibhu Gupta

    Yes, added also User.Read.All, still 403…

    1. Avatar photo
      Tony Redmond

      What call is returning the 403? (what URL are you trying to access)?

  12. Vibhu Gupta

    Hi Tony,

    I get 403 error even though Calendar.Read.All and Place.Read.All application permissions are set.
    Are any other permissions required as well?

    Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.

  13. Tonino Bruno

    Nice article Tony very usefully, just take care of the App Secret in your script (remove and reset it) as its visible!

    1. Avatar photo
      Tony Redmond

      The app secret isn’t much use without the app identifier and the tenant identifier…

Leave a Reply