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.
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.
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).
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.
The Real Person!
The Real Person!
Hi Tony!
Thank you for the script. I use the latest V1.0 19-Sep-2024
1: When I run the script it will list 397 of 587 rooms. Any clue why some room doesn`t appear in the csv file?
2: I get also error: ObjectNotFound: (New-DayString:String)
Se below
Looking for room mailboxes and workspaces…
Scanning room mailboxes for calendar events from 27-jul-2024 to 27-sep-2024
Found 2 calendar events for the A…. room
Found 5 calendar events for the B… room
Found 1 calendar events for the ROM Video room
New-DayString : The term ‘New-DayString’ is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At C:\Temp\Report-RoomMailboxUsage.PS1:154 char:20
+ $MondayOutput = New-DayString -InputDay “Monday” -DayEvents $Monda …
+ ~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (New-DayString:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
.2 is easy. You found a script that I hadn’t really released (yes, it’s in my repro, but I hadn’t published the article describing the changes to make the script run with the SDK instead of Graph requests). In any case, the article will appear next week. What you hit is what’s normally called a bug. The function name was incorrect. In any case, that bug is fixed in the version now online in GitHub.
.1 The script uses the Get-MgPlaceAsRoom cmdlet to find rooms. What’s reported as the count with:
[Array]$RoomMailboxes = Get-MgPlaceAsRoom -All -PageSize 500
$RoomMailboxes.count
Compared to:
[array]$ExoRoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -ResultSize unlimited
$ExoRoomMailboxes.count
The first command returns the set of room mailboxes known to the Places API. These don’t include workspaces.
The second includes workspaces and will be higher if you have workspaces.
You can compare the two sets to find room mailboxes that don’t show up with Get-MgPlaceAsRoom. An examination of their properties might reveal more.
The Real Person!
The Real Person!
Thank you for the quick response.
Here is what it look like:
PS C:\Temp> Get-MgPlaceAsRoom -All -PageSize 999 | measure
Count : 586
Average :
Sum :
Maximum :
Minimum :
Property :
PS C:\Temp> Get-Mailbox -ResultSize Unlimited -Filter {RecipientTypeDetails -eq “RoomMailbox”} | measure
Count : 587
Average :
Sum :
Maximum :
Minimum :
Property :
OK, so all the rooms are being found.
As to the output, the set of rooms reported is calculated by selecting the set of individual rooms that calendar data is foun for:
[array]$Rooms = $CalendarInfo | Sort-Object Room -Unique | Select-Object -ExpandProperty Room
It could be that the missing rooms simply don’t have any events in the reporting period.
BTW, download a new version of the script to pick up a couple of other fixes I made.
The Real Person!
The Real Person!
FYI, I downloaded and ran the latest version of your script last night. This fixed bug #2 that I reported to you. Thank you very much.
I am 100% sure that there are many calendar events in the rooms that the script does not include as this is the head office.
The Real Person!
The Real Person!
New info regarding missing rooms with events:
We have some rooms with Name = ObjectId instead of DisplayName. It seems that the script drop rooms with Name = Object
See example below. Both room has event, but the scripts drop ROOM Headquarter 1
Name: 411cc9f5-2da0-418a-9b1a-c36b68b2acc5
DisplayName: ROOM Headquarter 1
Name: ROM Headquarter 2
DisplayName: ROOM Headquarter 2
A room with the name property set to the object identifier (or rather, the value of ExternalDirectoryObjectId, see https://office365itpros.com/2022/04/21/exchange-online-distinguished-names/) shouldn’t cause a problem. To test, I created a new room mailbox called the Cleggan Room. Its Name was set to the Display Name, so I updated it to change it to the ExternalDirectoryObjectId.
get-mailbox -Identity ‘Cleggan Room’ | ft name, displayname
Name DisplayName
—- ———–
c5630fbd-d807-4425-bba8-bd71a0fd7195 Cleggan Room
The Places API doesn’t return the new room because it takes time to synchronize between Exchange Online and the Places service. In any case, I changed the script to remove the call to Get-MgPlaceAsRoom and the Graph request to find workspaces to a simple:
$RoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited
and
[array]$CalendarData = Get-MgUserCalendarView -UserId $Room.emailAddress -StartDateTime $Start -EndDateTime $End -All -PageSize 250
to be
[array]$CalendarData = Get-MgUserCalendarView -UserId $Room.primarySMTPAddress -StartDateTime $Start -EndDateTime $End -All -PageSize 250
The script ran as expected and reported the meeting I created in the Cleggan room:
Meeting Room Statistics from 26-Sep-2024 to 27-Sep-2024
——————————————————-
Total events found: 1
Online events: 1 100.00%
Most popular rooms
——————
Name Count
—- —–
Cleggan Room 1
Most active meeting organizers
——————————
Name Count
—- —–
Jess Cooper (Information Technology) 1
I conclude that having a GUID in the Name property isn’t an issue. There’s something else.
I assume that this works for your ROOM Headquarter 1:
$Room = Get-Mailbox -Identity “411cc9f5-2da0-418a-9b1a-c36b68b2acc5”
[array]$CalendarData = Get-MgUserCalendarView -UserId $Room.primarySMTPAddress -StartDateTime $Start -EndDateTime $End -All -PageSize 250
This just checks that the scripy can read the calendar data from a room that you’re finding problems with. Also, when you run the code below, are the problem rooms included in the $RoomMailboxes array?
Write-Host “Looking for room mailboxes and workspaces…”
[Array]$RoomMailboxes = Get-MgPlaceAsRoom -All -PageSize 500
If (!($RoomMailboxes)) {
Write-Host “No room mailboxes found – exiting” ; break
}
# No SDK cmdlet available for workspaces, so we need to run a Graph API request
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.workspace”
[array]$WorkSpaces = Invoke-MgGraphRequest -Uri $Uri -Method GET
$WorkSpaces = $WorkSpaces.Value
# Combine workspaces with room mailboxes if any are found
If ($WorkSpaces) {
$RoomMailboxes = $RoomMailboxes + $WorkSpaces
}
# Eliminate any room mailboxes or workspaces that don’t have an email address
$RoomMailboxes = $RoomMailboxes | Where-Object {$_.EmailAddress -ne $Null} | Sort-Object DisplayName
The Real Person!
The Real Person!
If I run this against the “problem” meeting room, I get calendar data
Import-Module Microsoft.Graph.Calendar
Get-MgUserCalendarView -UserId “4791b3cd-342c-4a73-a12e-318555674d77” -Startdatetime “2024-09-01T19:00:00-08:00” -Enddatetime “2024-09-26T19:00:00-08:00”
Subject Id
——- —
John Smith AAMkADFiYjk0ODc5LTIzZmMtNDlkZS1hOWI1LWMwYzcyNDQ5OWEzNQBGAAAAAABdXSqkdeIyQpPc3FB-Q7klBwCVOYwHjpHSR7RBz…
Adam Smith AAMkADFiYjk0ODc5LTIzZmMtNDlkZS1hOWI1LWMwYzcyNDQ5OWEzNQBGAAAAAABdXSqkdeIyQpPc3FB-Q7klBwCVOYwHjpHSR7RBz…
Knud Eddie Nordin AAMkADFiYjk0ODc5LTIzZmMtNDlkZS1hOWI1LWMwYzcyNDQ5OWEzNQBGAAAAAABdXSqkdeIyQpPc3FB-Q7klBwCVOYwHjpHSR7RBz…
Knud Eddie Nordin AAMkADFiYjk0ODc5LTIzZmMtNDlkZS1hOWI1LWMwYzcyNDQ5OWEzNQBGAAAAAABdXSqkdeIyQpPc3FB-Q7klBwCVOYwHjpHSR7RBz…
The Real Person!
The Real Person!
When running this script I get the below error, however it does provide output for my rooms and calendars. I was just wondering if it’s possible some data could be missing since I am getting this error?
Line |
148 | [array]$WorkSpaces = Get-GraphData -Uri $Uri -AccessToken $Token
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Response status code does not indicate success: 404 (Not Found).
It seems like Microsoft made a change in workplace access, possibly because of their Places initiative. It means that accessing the V1.0 endpoint for the Places resource and casting to just fine workspaces doesn’t work. However, if you change the URI to $Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.workspace”, I suspect that it will work because this does:
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.workspace”
$Data = Invoke-MgGraphRequest -Uri $Uri -Method GET
The Real Person!
The Real Person!
I got this error:
+ ForEach (Get-Mailbox -Identity “bf5f1c5d-b0b7-4ee1-af5d-6128ad2a9f57” …
+ ~
Missing variable name after foreach.
At C:\Temp\Tony2.ps1:67 char:70
+ … Each (Get-Mailbox -Identity “bf5f1c5d-b0b7-4ee1-af5d-6128ad2a9f57”) {
+ ~
Unexpected token ‘)’ in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParseException
+ FullyQualifiedErrorId : MissingVariableNameAfterForeach
Below is from the script
Write-Host (“Scanning room mailboxes for calendar events from {0} to {1}” -f $StartDate, $EndDate)
$CalendarInfo = [System.Collections.Generic.List[Object]]::new()
ForEach (Get-Mailbox -Identity “bf5f1c5d-b0b7-4ee1-af5d-6128ad2a9f57”) {
$Data = $false # Assume no data in the targeted range
[array]$CalendarData = Get-MgUserCalendarView -UserId $Room.primarySMTPAddress -StartDateTime $Start -EndDateTime $End -All -PageSize 250
You must have changed the script (possibly a paste that went wrong)?
The code is:
Write-Host (“Scanning room mailboxes for calendar events from {0} to {1}” -f $StartDate, $EndDate)
$CalendarInfo = [System.Collections.Generic.List[Object]]::new()
ForEach ($Room in $RoomMailboxes) {
Hi Tony
Much gratitude for such a nice article written. I am new to graph, is it possible to get this data in a csv?
Any idea on this error?
Get-GraphData : System.Net.WebException: The remote server returned an error: (401) Unauthorized.
at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()
At C:\ReportRoomMailboxUsage.PS1:142 char:25
+ [Array]$RoomMailboxes = Get-GraphData -Uri $Uri -AccessToken $Token
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-GraphData
When you see a 401 error it means that the request doesn’t have the necessary permission to complete. In this case, does the app have consent to use the Mail.Read.All application permission?
If I wanted to use this to report only on Teams meetings so we could try and get a more accurate man hour count of Teams meetings could I do that?
Filter the set of meetings found for a mailbox to select the online meetings. That’s what I would do.
I ended up modifying the $CalendarData line to $CalendarData = $CalendarData | Where-Object {$_.isCancelled -eq $False -and $_.isOnlineMeeting -eq $True} and it looks like it pulled the information I needed.
Yep. That’s what I would do.
Hi Tony,
thanks for your script! But I have one question about it. I adjusted the value to get 100+ items via Graph. In total we have 236 Rooms.
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.room?`$top=300”
But even when running this it is only returning data of 117 Rooms. What can I do to make sure to have all my rooms available within the results?
Regards,
Nils
Try finding room mailboxes with Exchange Online instead (Get-ExoMailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited) and see if this finds all the mailboxes. There might be something in the Places API that’s not picking up some of your mailboxes (it’s a beta API after all).
Hi Tony,
(Get-ExoMailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited) is giving all 236 rooms. Also when using GraphExplorer via Browser it is giving me correct number of rooms by typing this URL “https://graph.microsoft.com/beta/places/microsoft.graph.room?`$top=300”
If simply replacing the [Array]$RoomMailboxes with mentioned ExchangeQuery to get all Rooms script returns nothing any longer.
How can I easily come around this?
Bill Wheeler wrote he solved this by simply adding a list of rooms to the URI. How can I get this done?
Regards,
Nils
Regards,
Nils
You should get the same results with the query in Graph Explorer and the app unless there’s something blocking access to mailboxes for the app (like RBAC for applications).
I can’t debug this issue for you because I don’t have access to your data. If the Get-ExoMailbox cmdlet works, use the array it produces and process the 236 rooms that way.
Subject is coming as email address of the organizer. I tried to see $event.subject, it’s the same. So, is this by design. Any work around s possible?.
As you noted, this is the data the Graph returns. Did you check the actual data in the room calendar?
Actual data is showing correctly in the calendars
I’ve asked a contact in Microsoft about the issue.
how do i extract all users calendar data.
Is the event ID a unique value to compare the same meeting with users calendar data?
See https://practical365.com/room-mailbox-meeting-organizer/
Hi Tony,
The event output gets the subject as the organizer’s name. Not correct subject value, any input on this?
Example from about output
Subject : Ken Bowers
Thanks,
Samee.
The script outputs the subject returned by the Graph ($Event.Subject). Sometimes, this is the name of the organizer. Have a look at the code…
Works clean when run against ALL (default). When I attempt to limit the array to a single room mailbox it returns no data. I’ve verified the room mailbox calendar has many items, and the room mailbox has accepted these items. output is just zeros, no errors for permissions or no object found. know where I should start investigating? Notably, this room is does not appear in the list when I run the default ALL process.
# Find room mailboxes – this ignores room mailboxes marked as workspaces
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.room”
[Array]$RoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -Identity [primarySMTPAddress Redacted]
Have you tried running:
$RoomMailbox = Get-ExoMailbox -Identity “My Room Mailbox”
$URI = “https://graph.microsoft.com/V1.0/users/” + $Room.primarySmtpAddress + “/calendar/calendarView?startDateTime=” + $Start + “&endDateTime=” + $End
$Data = Get-GraphData -Uri $Uri -AccessToken $Token
Make sure that $Start and $End are populated with the start end end times to search for.
Thanks Tony.
Getting the same results (no data, with divide-by-zero errors on the percentages) with these modifications. Tested using different rooms, and rooms that appear in the default report using the whole env in the array.
Make sure that the URI contains correct values. Sometimes it doesn’t and you get zero returns because the lookup fails to find the room mailbox or any data for the dates passed in the URI.
First off, thanks for the information and script as I finally got around to setting this up
Second, when i run the script it pulls data from some conference rooms, but gives the follow error on others, which others it doesn’t indicate. Rechecked API/ Permissions and they are set as specified in article.
Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.
at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.GetResponse(WebRequest request)
at Microsoft.PowerShell.Commands.WebRequestPSCmdlet.ProcessRecord()
At C:\xxxx\xxxxxxxxxxx.ps1:158 char:28
+ … [array]$CalendarData = Get-GraphData -Uri $Uri -AccessToken $Token
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
+ FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Get-GraphData
Any assistance would be great!
I’d check the URI for the room that you have a problem with. The code is: $Uri = “https://graph.microsoft.com/V1.0/users/” + $Room.emailAddress + “/calendar/calendarView?startDateTime=” + $Start + “&endDateTime=” + $End
Is the email address correct? Check it against the room that the script fails on.
awesome script sir got it working but did encounter 403 on some of the room mailboxes.
Im trying to check if it was a permission just like in the thread below.
See my other response. I suspect it’s something to do with the email address of the room. Maybe there’s something strange with some of the rooms.
Hi! Thanks a lot for the sample code.
I am having problem with the code as the run takes over 1h meaning the token will expire.
Also the token does not include the refresh token nor expires_on values meaning I can’t refresh it.
How can I alter the code so that it checks if the token is valid and refreshes it?
Access tokens issued to apps that use the client credentials authentication flow. It is the app’s responsibility to track the age of its access token and renew if necessary. How many meeting rooms are you analyzing? There must be a large number to take over an hour (or they have tons of meetings).
An example of how to renew access tokens is included in the Teams and Groups activity report script (see this article: https://office365itpros.com/2023/02/03/powershell-tricks-groups-report/)
Let me answer myself:
Since the default token lifetime is 60 minutes and I failed to change the policy, I handled this differently:
I added new variable right after: $tokenRequest = Invoke-WebRequest -Method ….
$Global:renewtime = (get-date).AddMinutes(45).ToString(“HH:mm”)
Now, I added some comparing in the foreach loop (ForEach ($Room in $RoomMailboxes) {….
$currenttime = (get-date).ToString(“HH:mm”)
$left=new-timespan -start $currenttime -end $renewtime
IF ($left.minutes -lt 15)
{
$Token = GetAccessToken
}
… code continues here as it was.
The If statement calls the GetAccessToken function again if there is less than 30 minutes before the token expires. This could be lower too, but I didn´t want to push it, since at my current query there was over 3000 reservations for single room and they seem to take a long time..
I know it’s ugly, but hey, it seems to work!
Ugly but effective beats nice but flawed any day
Question: Does your script account for recurring events? I’ve started something similar, but using the PowerShell SDK, with Get-MgUserEvent cmdlet, which I assume is the wrapper for calendarview. It doesn’t list any of the specific instances, but allows you to get the pattern properties. I was getting ready to delve into Invoke for instances, because the SDK cmd (Get-MgUserEventInstance) requires start and end date, but doesn’t accept them as parameters.
Very nice article and script. I’m a huge fan of your book O365 for IT Pros. Reference it all the time.
AFAIK, it does. The events are read direct from room mailboxes and each occurrence of an event is a discrete item in the mailbox.
I realized that instead of using CalendarView, I was using a different endpoint (users/{UserId}/Events/{EventId}), which doesn’t retrieve specific occurrences. Don’t I feel sheeepppiiiisshhh. Sorry for noise.
No issue. We all live and learn.
Hi Tony,
First of all, thanks for your amazing script. While i am trying to run the script, i hit into errors
Scanning room mailboxes for calendar events from 1/8/2023 5:07:39 PM to 3/10/2023 5:07:39 PM
Attempted to divide by zero.
At C:\Users\eddychong\Downloads\mailbox.ps1:219 char:1
+ $PercentOnline = ($OnlineMeetings.Count/$TotalEvents).toString(“P”)
While i suspect it could not find any room mailboxes, so i added 2 write-host for debug after the #Find workspaces script.
# Find workspaces
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.workspace”
[array]$WorkSpaces = Get-GraphData -Uri $Uri -AccessToken $Token
Write-Host “Debug: Worksspace is” $WorkSpaces
# Combine workspaces with room mailboxes if any are found
If ($WorkSpaces) { $RoomMailboxes = $RoomMailboxes + $WorkSpaces }
$RoomMailboxes = $RoomMailboxes | Where-Object {$_.EmailAddress -ne $Null}
Write-Host “Debug: Room mailboxes are” $RoomMailboxes
The output showing no mailboxes. Can you advise if there is anything that i need to modify?
Debug: Worksspace is @{@odata.context=https://graph.microsoft.com/beta/$metadata#places/microsoft.graph.workspace; value=System.Object[]}
Debug: Room mailboxes are
Scanning room mailboxes for calendar events from 1/8/2023 5:07:39 PM to 3/10/2023 5:07:39 PM
Attempted to divide by zero.
The script uses the Places API to find room mailboxes. Sometimes it takes a little while for new room mailboxes to show up in that API. This is normally OK because room mailboxes tend to be quite static. You don’t add or remove them on an ongoing basis. But for testing, you might want to replace the calls that find room mailboxes with Get-ExoMailbox -RecipientTypeDetails RoomMailbox…. There’s an earlier discussion about how to do this. You also need to change the property used to construct the URI to fetch calendar info. Again, details are in an earlier thread.
Hi, the script is very helpful – many thanks!
I am trying to narrow the GET to one roomlist, but when I modify the URI then it brakes.
https://graph.microsoft.com/v1.0/places/xyz@aaa.onmicrosoft.com/microsoft.graph.roomlist/rooms
Anyone has done it ?
If you only want to access a specific mailbox, change the population of the $RoomMailboxes array to:
$RoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -Identity ‘Room Mailbox to Query’
After that, the script will process as normal, even though only one item is in the $RoomMailboxes array.
the change of array:
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.room”
[Array]$RoomMailboxes = Get-GraphData -Uri $Uri -AccessToken $Token
If (!($RoomMailboxes)) {Write-Host “No room mailboxes found – exiting” ; break}
to:
$Uri = “https://graph.microsoft.com/beta/places/microsoft.graph.room”
[Array]$RoomMailboxes = Get-ExoMailbox -RecipientTypeDetails RoomMailbox -Identity ‘myTESTroom@zwz.com’
If (!($RoomMailboxes)) {Write-Host “No room mailboxes found – exiting” ; break}
Then I get :
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\70J6816\Downloads\ReportRoomMailboxUsage.ps1:131 char:28
+ … [array]$CalendarData = Get-GraphData -Uri $Uri -AccessToken $Token
What’s in the array? Hopefully one mailbox…
And what’s in the $URI variable. The Graph is complaining that it can’t find the object you’re requesting.
The original line is $Uri = “https://graph.microsoft.com/V1.0/users/” + $Room.emailAddress + “/calendar/calendarView?startDateTime=” + $Start + “&endDateTime=” + $End
Change it to $Uri = “https://graph.microsoft.com/V1.0/users/” + $Room.PrimarySmtpAddress + “/calendar/calendarView?startDateTime=” + $Start + “&endDateTime=” + $End
You’re using Exchange to return information instead of the Graph. The information is different depending on which source you use….
yeah, replaced email address with SMTP address and now it’s working.
Thank you. 🙂
Hi Tony,
do you think its possible to report on which days are most popular. That is, for a given room for a given timeframe, what % of bookings were monday,tuesday,etc.
My thoughts are its unlikely as the ‘day’ doesnt seem to be part of the meeting, just the date, so the script would need to calculate all the dates for ‘mondays’ for that timeframe, then count how many of those there were, then the other days, then figure out %. Just seems too crazy, but hey, my manager is asking for it.
Anything is possible when you have the data. For instance, I was able to hack out this example chart to show the most popular day for a specific room. The same could be used to output the overall most popular day across all rooms:
Summary usage pattern for the Board Conference Room room
Monday events: 1 (4.55%) oo>
Tuesday events: 16 (72.73%) oooooooooooooooooooooooooooooooooooo>
Wednesday events: 2 (9.09%) ooooo>
Thursday events: 1 (4.55%) oo>
Friday events: 1 (4.55%) oo>
Saturday events: 1 (4.55%) oo>
Sunday events: 0 (0.00%) >
Try the enhanced script as described in https://office365itpros.com/2023/02/10/room-mailboxes-usage-pattern/ and see if that works for you.
Hi Tony,
i followed the instruction (app registration + api permission ) but i still got this error
Get-GraphData : System.Net.WebException: The remote server returned an error: (403) Forbidden.
What’s can be my mistake ?
ps : does the script manage multilanguage ? (my room mailboxes are in French)
It’s a permissions problem (403 – see https://practical365.com/common-graph-api-errors-powershell/).
I have not tested with French mailboxes, but I can’t see that there should be a problem because the recipient type doesn’t change.
solved, just like Vibhu Gupta said, i removed all permissions and it works perfectly now
thanks a lot for this script 😉
Glad you got things sorted.
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!
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.
The Real Person!
The Real Person!
Hello Tony,
First of all, thank you for your script.
I have the same issue, and after investigation, the request https://graph.microsoft.com/beta/places/microsoft.graph.room does not return an odata.nextlink. It is necessary to loop using a skip and check if the value is empty to exit the loop.
Hi,
The script uses a function called Get-GraphData to fetch information using Graph API requests. The function includes the code necessary to handle pagination, so you shouldn’t have to worry about the nextlinks etc.
I don’t see a reference to https://graph.microsoft.com/beta/places/microsoft.graph.room in the script. What line do you see it?
BTW, the Graph SDK supports Places now, so you could do something like
[array]$Places = Get-MgPlaceAsRoom -All
instead of using a Graph API request… that way, you wouldn’t have to worry about nextlinks etc. Maybe I should rewrite the script to use the SDK.
Hi Bill,
what did you do to have a specific list of rooms within the URI as workaround? Can you share the code / how you were able to slove this?
Regards,
Nils
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!
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.
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
———————————————————————–
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…
$URI = https://graph.microsoft.com/V1.0/users//calendar/calendarView?startDateTime=2022-11-12T12:36:09&endDateTime=2023-01-12T12:36:09 ( I just ran it on 60 day to speed up the script)
I see three rooms in the array after the last successful room, but they do exist in the address book.
If I just wanted to run the report against a certain roomlist, where / how would I edit the script to do that?
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.
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”
Did you check that you’re using application rather than delegate permissions?
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.
As I say, Graph permissions can be a real bear to deal with…
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
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.
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?
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
I have added Directory.Read.All as per your recommendation, no difference unfortunately.
Test your token and see what permissions it has.
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
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”
],
fix the emailaddress to primarysmtpaddress in $uri. It will work
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.
Yes, added also User.Read.All, still 403…
What call is returning the 403? (what URL are you trying to access)?
Try adding the Directory.Read.All permission.
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.
Are you using application permissions?
Nice article Tony very usefully, just take care of the App Secret in your script (remove and reset it) as its visible!
The app secret isn’t much use without the app identifier and the tenant identifier…