Exchange Meeting Room Statistics

by [Published on 10 March 2016 / Last Updated on 10 March 2016]

In this article, we will develop a script to provide us with some basic statistics for Exchange meeting rooms’ usage.

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 "nuno.mota@domain.com") {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 "nuno.mota@domain.com") {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.

See Also


The Author — Nuno Mota

Nuno Mota avatar

Nuno is an Exchange MVP working as a Senior Microsoft Messaging Consultant for a UK IT Services Provider in London. He specializes in Exchange, Lync, Active Directory and PowerShell.

Advertisement

Featured Links