Introduction
Exchange has several different types of mailboxes, one of them being room mailboxes. A room mailbox is a resource mailbox that is assigned to a physical location, such as a conference room, an auditorium or a training room for example. After an administrator creates room mailboxes, users can easily reserve rooms by including room mailboxes in meeting requests, providing a simple and efficient way of organizing meetings for users.
For several Exchange versions now that room mailboxes have been available and some organizations make extensive use of them for all their meeting room bookings. In certain cases, having statistical information about these rooms helps organizations plan or redesign their offices in a more efficient way. This information can show how often certain rooms are utilized, the average meeting duration, who tends to book more meetings, and so on.
In this article we will develop a script to provide us with some of this information and to serve as a stepping stone to gather further information depending on the reader’s particular needs.
Script
Since the Exchange built-in cmdlets do not provide us with the information we need, for this script we basically have two options: we either use Exchange Web Services (EWS) or use Outlook to gather this information. Although EWS is always my preferred option, this time I will use Outlook to keep the script simpler to explain and understand.
There is a drawback however… Because it uses Outlook, the meeting room(s) we want to gather data from need to be added in Outlook as an additional mailbox. This is not a big problem when we want to analyze a few rooms, but it becomes a problem when we have dozens and dozens of rooms, or even hundreds. In this case, what I recommend is to do them in batches of 25. This is an issue that EWS simply does not have, so if you have many rooms to gather data from, EWS is highly recommended.
To give ourselves access to the meeting room mailbox(s), we can use the Exchange Admin Center or PowerShell through the following cmdlet:
Add-MailboxPermission <meeting_room> -User <user> -AccessRights FullAccess -InheritanceType All
Once we have access to all the meeting rooms we want to process, we just need to open Outlook and ensure their mailboxes appear on the left hand side:
Figure 1
To start with, we define a couple of parameters that will delimit the period of time for which we want to analyze the rooms for, such as a month, a whole year, or anything we want:
[CmdletBinding()]
Param (
[Parameter(Position = 0, Mandatory = $False)]
[DateTime] $From = “12/01/2015”,
[Parameter(Position = 0, Mandatory = $False)]
[DateTime] $To = “01/01/2016”
)
All Microsoft Office applications support automation, the ability of a program to expose itself to, for example, VBA or PowerShell in this case, so that PowerShell can control it. In order for us to manipulate Outlook, first we need to have access to Outlook’s object library. The Application object is the root object of the Outlook object model, and represents the entire Outlook application.
So next we try to create an object, connect to Outlook and get a list of all the rooms we gave ourselves access to above. If any of this fails, we throw an error and exit the script:
Try {
$outlook = New-Object -Com Outlook.Application -ErrorAction Stop
$mapi = $outlook.GetNameSpace(“MAPI”)
$roomList = $mapi.Folders
} Catch {
Write-Warning “Unable to create Outlook COM Object and connect to Outlook”
Exit
}
For each meeting room, we will create an object with all its statistics and then add it to a collection of objects so at the end we can easily export the results:
[Array] $roomsCol = @()
Next we go through every meeting room we added in Outlook and process its calendar. Note that the code will process any mailbox added in Outlook, not just resource mailboxes. In a way this is good because it means we can get statistics for shared mailboxes as well, for example, but on the other hand we need to exclude any mailboxes we do not want to process, just like our own mailbox.
For this scenario, I only need to exclude my own mailbox. So, if the name of the mailbox that I am processing is my own, we check if that is the current mailbox we are processing and, if it is, we simply jump (continue) to the next mailbox in the ForEach loop using Continue:
ForEach ($room in $roomList) {
$roomName = $room.Name
If ($roomName -eq “[email protected]”) {Continue}
Write-Host $roomName -ForegroundColor Green
Once we have a mailbox to process, we make sure its Calendar is not empty. If there is at least one item, we save them all into the $calItems variable. We then sort all the items by their start date, we make sure recurring meetings are also included and we restrict the items we want based on the timespan we specified at the beginning of the script and remove all the other ones outside that period:
If ($room.Folders.Item(“Calendar”).Items) {
$calItems = $room.Folders.Item(“Calendar”).Items
$calItems.Sort(“[Start]”)
$calItems.IncludeRecurrences = $True
$dateRange = “[End] >= ‘{0}’ AND [Start] <= ‘{1}'” -f $From.ToString(“g”), $To.ToString(“g”)
$calItems = $calItems.Restrict($dateRange)
$totalItems = ($calItems | Measure-Object).Count
We now have all the meeting items we want to process in one variable. So we need to go through them one by one and extract all the information we want. For this article we will be gathering the number of meetings for the period specified, their total duration, the average number of attendees per meeting, the percentage of meetings that are in the morning vs those in the afternoon, and finally the percentage of recurring meetings. To gather all of this, we first set some variables to zero, then go through all the meeting items and use those variables to save the information:
[Int] $count = $totalMeetings = $totalDuration = $totalAttendees = $totalAM = $totalPM = $totalRecurring = 0
ForEach ($meeting in $calItems) {
Write-Progress -Activity “Processing $count / $totalItems”
$totalMeetings++
$totalDuration += $meeting.Duration
$totalAttendees += ($meeting.RequiredAttendees.Split(“;”)).Count
If ((Get-Date $meeting.Start -UFormat %p) -eq “AM”) {$totalAM++} Else {$totalPM++}
If ($meeting.IsRecurring) {$totalRecurring++}
$count++
}
At this stage, we have all the information we want in those 6 variables, so what we need to do now is save them to an object and then add that object to out object collection. In the following section we also calculate our averages and percentages and round our results to zero decimal places:
$romObj = New-Object PSObject -Property @{
Room = $roomName
Meetings = $totalMeetings
Duration = $totalDuration
AvgDuration = If ($totalMeetings -ne 0) {[Math]::Round($totalDuration / $totalMeetings, 0)} Else {0}
AvgAttendees = If ($totalMeetings -ne 0) {[Math]::Round($totalAttendees / $totalMeetings, 0)} Else {0}
AMperc = If ($totalMeetings -ne 0) {[Math]::Round($totalAM * 100 / $totalMeetings, 0)} Else {0}
PMperc = If ($totalMeetings -ne 0) {[Math]::Round($totalPM * 100 / $totalMeetings, 0)} Else {0}
RecPerc = If ($totalMeetings -ne 0) {[Math]::Round($totalRecurring * 100 / $totalMeetings, 0)} Else {0}
}
$roomsCol += $romObj
The final step is to simply export everything to a CSV file (or print it onto the screen):
$roomsCol | Select Room, Meetings, Duration, AvgDuration, AvgAttendees, AMperc, PMperc, RecPerc | Sort Room | Export-Csv “C:\Scripts\MeetingRoomStats.csv” -NoTypeInformation
The end result will look something like this:
Figure 2
In this example, I only searched one meeting room called ITD – 16A – Small CF VC. For the month of December we can see that a total of 115 meetings were booked, out of which 55% are recurring meetings. The average meeting duration was 46 minutes, each meeting had an average of 8 attendees and there were slightly more meetings booked in the morning.
Final Script
The final script looks like this:
[CmdletBinding()]
Param (
[Parameter(Position = 0, Mandatory = $False)]
[DateTime] $From = “12/01/2015”,
[Parameter(Position = 0, Mandatory = $False)]
[DateTime] $To = “01/01/2016”
)
Try {
$outlook = New-Object -Com Outlook.Application -ErrorAction Stop
$mapi = $outlook.GetNameSpace(“MAPI”)
$roomList = $mapi.Folders
} Catch {
Write-Warning “Unable to create Outlook COM Object and connect to Outlook”
Exit
}
# Array to include all the objects for all meeting rooms
[Array] $roomsCol = @()
# Process all the meeting rooms one by one. Please note that mailboxes to which we have access in Outlook will also be processed
ForEach ($room in $roomList) {
$roomName = $room.Name
# Exclude our own mailbox from processing.
If ($roomName -eq “[email protected]”) {Continue}
# Print the name of the current meeting room being processed
Write-Host $roomName -ForegroundColor Green
# Check if the meeting room has any calendar items. If yes, then get all the items within the timespan specified at the beginning of the script
If ($room.Folders.Item(“Calendar”).Items) {
$calItems = $room.Folders.Item(“Calendar”).Items
$calItems.Sort(“[Start]”)
$calItems.IncludeRecurrences = $True
$dateRange = “[End] >= ‘{0}’ AND [Start] <= ‘{1}'” -f $From.ToString(“g”), $To.ToString(“g”)
$calItems = $calItems.Restrict($dateRange)
$totalItems = ($calItems | Measure-Object).Count
# Set some variables that will be used to save meeting information and process all meetings one by one
[Int] $count = $totalMeetings = $totalDuration = $totalAttendees = $totalAM = $totalPM = $totalRecurring = 0
ForEach ($meeting in $calItems) {
Write-Progress -Activity “Processing $count / $totalItems”
$totalMeetings++
$totalDuration += $meeting.Duration
$totalAttendees += ($meeting.RequiredAttendees.Split(“;”)).Count
If ((Get-Date $meeting.Start -UFormat %p) -eq “AM”) {$totalAM++} Else {$totalPM++}
If ($meeting.IsRecurring) {$totalRecurring++}
$count++
}
# Save the information gathered into an object and add the object to our object collection
$romObj = New-Object PSObject -Property @{
Room = $roomName
Meetings = $totalMeetings
Duration = $totalDuration
AvgDuration = If ($totalMeetings -ne 0) {[Math]::Round($totalDuration / $totalMeetings, 0)} Else {0}
AvgAttendees = If ($totalMeetings -ne 0) {[Math]::Round($totalAttendees / $totalMeetings, 0)} Else {0}
AMperc = If ($totalMeetings -ne 0) {[Math]::Round($totalAM * 100 / $totalMeetings, 0)} Else {0}
PMperc = If ($totalMeetings -ne 0) {[Math]::Round($totalPM * 100 / $totalMeetings, 0)} Else {0}
RecPerc = If ($totalMeetings -ne 0) {[Math]::Round($totalRecurring * 100 / $totalMeetings, 0)} Else {0}
}
$roomsCol += $romObj
}
}
$roomsCol | Select Room, Meetings, Duration, AvgDuration, AvgAttendees, AMperc, PMperc, RecPerc | Sort Room | Export-Csv “C:\Scripts\MeetingRoomStats.csv” -NoTypeInformation
Conclusion
In this article, we developed a script to provide us with some basic statistics for Exchange meeting rooms’ usage. This can be used in reports for management or for planning purposes for example.
Can i save the above script in one file and run the script will work?
Hi Sam,
Yes, you can. Just make sure that 1) you give yourself full access to the mailbox; 2) it appears in your Outlook profile; and 3) you update the script with your email address (so the script doesn’t scan your calendar).
Regards,
Nuno
Nuno – what if we get the error that we can’t create the COM object? I am in an Office 365 environment with a hybrid server. Thanks!
Disregard, I figured it out. The script must be ran as standard user, not as admin.
Hi Will,
Apologies for the late reply. Glad you managed to solve it!
Best regards,
Nuno
Hi Nuno
At the beginning of the article, you mention this can be done via EWS, is that explained in a separate article or are you able to explain and show how as we have close to 1500 rooms that I need to get utilization reports done for.
Hi Pedro,
Unfortunately, I started writing the same script using EWS but never finished it… As such, I haven’t published it yet I’m afraid.
Best regards, Nuno
Hi Pedro,
Good news! I finally got around to finishing the EWS script. You can find it here: http://letsexchange.blogspot.co.uk/2017/05/exchange-meeting-room-statistics.html
It makes it easier to gather stats for all the rooms in your environment. Just make sure AutoDiscover is working, that you have the EWS Managed API installed and that your account has at least Reviewer rights to all the rooms’ calendars.
Let me know how it goes!
Regards, Nuno
Hi Nuno
The script works well on premise. Is there anything that can be done to get the script to run in O365?
Hi Pedro,
Sorry for the delay in replying.
Check my latest script on TechNet (https://gallery.technet.microsoft.com/Exchange-Meeting-Room-2aab769a) which uses EWS and works both with on-prem and online meeting rooms!
Regards,
Nuno
Hi Nuno,
As Pedro I’m on O365 Online and no statistics report are available, do you know if we can have your script to be used in O365 Online ?
Regards,
Martin
Hi Martin,
Please check my latest script on TechNet (https://gallery.technet.microsoft.com/Exchange-Meeting-Room-2aab769a) which uses EWS and works both with on-prem and online meeting rooms!
Regards,
Nuno
Hello Numo. When I run this script, I get this message:
cmdlet Write-Progress at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Status:
If I hit enter, it gives me this error:
Write-Progress : Cannot bind argument to parameter ‘Status’ because it is an empty string.
At C:\temp\MeetingRoomStats-Outlook.ps1:91 char:36
+ Write-Progress <<<< -Activity "Processing $count / $totalItems"
+ CategoryInfo : InvalidData: (:) [Write-Progress], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.PowerShell.Commands.Writ
eProgressCommand
If I enter any text and hit enter, it just keeps asking over and over again.
What is it looking for and can it be avoided?
Thanks!
Never Mind. I had to comment out the line that begins “write-progress” and it worked after that.
Glad to hear that Erik!
Don’t forget to download the latest version of the script from TechNet.
Regards,
Nuno
Hi Nuno,
Is there a chance to have script that does not make a Average of meeting? I need to know how many meeting were booked the last three months. I need to see how many people attended the specific meeting, in which meetingroom. Can that be done?
We need to figure out how the meetingrooms are being booked, do we have enough, do we have too many big meetingrooms and so on. Therefore we cannot use average numbers…
Hi Michael,
Sure, it doesn’t take much work to update the script for what you want. Using my latest script on TechNet (https://gallery.technet.microsoft.com/Exchange-Meeting-Room-2aab769a) which uses EWS instead, all you have to do is put the:
$romObj = New-Object PSObject -Property @{
inside the:
ForEach ($meeting in $fiItems.Items) {
and update the room object so that instead of saving averages, it saves the data for every single meeting.
Hope this helps.
Regards,
Nuno
My account is MFA enabled so i would like to use it as Connect-EXOPSSession but at line 102 throwing error, which is basically Get-Credentials and other parameters. So can you update or help to work the same script with MFA?
Also how do I put more than 100 room mailboxes smtp address by using any CSV list?
Any suggestion please
Get-MeetingRoomStats.ps1:102 char:3
+ $srvCred = New-Object System.Net.NetworkCredential($cred.User …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Tried with my O365 admin account but its giving below error. Already given my Global admin account Full Access and Reviewer permission on O365 room mailbox. But still below error.
After providing permission I am also able to access room mailbox from OWA fine.
My room mailbox hosted in O365 but sync from onpremise
VERBOSE: Binding to the [email protected] Calendar folder.
VERBOSE: Unable to connect to [email protected]. Please check
permissions: Exception calling “Bind” with “2” argument(s): “The request failed. The remote server
returned an error: (401) Unauthorized.”. Skipping [email protected]
Can you please help
Hello Nuno,
I am getting the following error while running the EWS script, can you please help.
PS D:\Himanshu\Scripts> .\roomstats.ps1
Creating Exchange Service Object using AutoDiscover
cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
-Message Binding to the [email protected] Calendar folder.
Unable to connect to [email protected]. Please check permissions: Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401) Unauthorized.”. Skipping [email protected].
-Message Binding to the [email protected] Calendar folder.
Unable to connect to [email protected]. Please check permissions: Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401) Unauthorized.”. Skipping [email protected].