Exchange Meeting Room Statistics

 

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:

Image
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:

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

19 thoughts on “Exchange Meeting Room Statistics”

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

  1. 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!

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

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

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

  4. 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!

  5. 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…

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

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

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

Leave a Comment

Your email address will not be published.

Scroll to Top