Friday, 13 February 2015

Auditing Exchange Rooms for Double Bookings

I recently wrote a post over on the Exchange Server TechNet forum about how we have seen an increase in meeting room double bookings since upgrading from Exchange 2010 to Exchange 2013. I won't repeat it all here (you can read all the details over there), but the bottom line is that in certain situations Exchange 2013 accepts room double bookings.

We're working through the process of getting the Exchange Server product group to recognise that the new behaviour is not ideal, and one of the data points we needed was how many times we have had rooms double booked.

To get the information I came up with a PowerShell script which utilizes the Exchange Web Services API to query mailboxes and identify instances where rooms are double-booked.

This script is what I consider, in Microsoft KB style, "Fast Publish". It isn't pretty, I'm certain it isn't perfect, but it does work.

The script takes several parameters, the most important of which are Roomname, StartDate, and EndDate. The parameters are fairly self explanatory, with the exception of LoadAllProperties - this is a switch which will call the Load() method on each of the appointment items in conflict. Using this switch will give you a lot more detail about each conflicting appointment, but it comes with a penalty in the time the script takes. I would suggest running without the switch first, and using it if you decide you need the extra details.


# Get-RoomDoubleBookings.ps1
# Script to audit Exchange rooms to find double-bookings
# Ben Lye - www.onesimplescript.com

# Parameters for the script
[CmdletBinding(DefaultParametersetName="Common")]
param(
 [Parameter(Mandatory=$False)][string]  $Roomname = "*",
 [Parameter(Mandatory=$False)][datetime] $StartDate = (Get-Date),
 [Parameter(Mandatory=$False)][datetime] $EndDate = (Get-Date).AddDays(365),
 [Parameter(Mandatory=$False)][string]  $EwsPath = "C:\Program Files (x86)\Microsoft\Exchange\Web Services\2.1\Microsoft.Exchange.WebServices.dll",
 [Parameter(Mandatory=$False)][string]  $EwsUrl = $Null,
 [Parameter(Mandatory=$False)][string]  $EwsVersion = "Exchange2013",
 [Parameter(Mandatory=$False)][switch]  $LoadAllProperties = $False,
 [Parameter(Mandatory=$False)][switch]  $OutputCsv,
 [Parameter(Mandatory=$False)][string]  $CsvFile = ".\DoubleBookings.csv",
 [Parameter(Mandatory=$False)][switch]  $OutputHtml,
 [Parameter(Mandatory=$False)][string]  $HtmlFile = ".\DoubleBookings.html"
 )

# Test the path to the Exchange Web Services DLL
If ( !(Get-Item -Path $EwsPath -ErrorAction SilentlyContinue) ) {
 Throw "EWS Managed API could not be found at $($EWSManagedApiPath)."
} Else {
 # Load EWS Managed API
 Add-Type -Path $EwsPath
}

# Create EWS Object
$Service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::$EwsVersion)

# Find all the rooms using an AD lookup
$Searcher = new-object DirectoryServices.DirectorySearcher([ADSI]"")
$Searcher.Filter = "(&(samaccountname=$Roomname)(objectCategory=person)(msExchRecipientTypeDetails=16)(!msExchHideFromAddressLists=*))"
$Rooms = $Searcher.FindAll()
$Rooms = $Rooms | Sort-Object -Property Path

# Create global variables
$RoomCounter = 1 # Counter for the progress bar
$Conflicts = @() # Array to store any conflicting appointments

# Loop through all the rooms
ForEach ($Room in $Rooms) {
 Write-Verbose $Room.properties.displayname
 # Update the progress bar
 Write-Progress -Activity "Scanning Room Mailboxes ($RoomCounter/$($Rooms.count))" -status $Room.properties.displayname -percentComplete ($RoomCounter/$Rooms.count*100)
 
 # Get the SMTP email address of the room mailbox to use in the EWS autodiscover and Calendar bind
 $SmtpAddress = $Room.properties.mail[0]
 
 # Set the EWS Service URL using the parameter or find it with autodiscover
 If ($EwsUrl) {
  $Service.Url = $EwsUrl
 } Else {
  [void]$Service.AutodiscoverUrl($SmtpAddress)
 }
 
  # Update the progress bar
  Write-Progress -Activity "Scanning Room Mailboxes ($RoomCounter/$($Rooms.count))" -status $Room.properties.displayname -CurrentOperation "Binding to calendar folder" -percentComplete ($RoomCounter/$Rooms.count*100)
 
 # Bind to the calendar folder of the mailbox
  $FolderId = new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$SmtpAddress)     
  $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($Service,$FolderId)    
    
  # Define the calendar view  
  $CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate,$EndDate)
  $Calendarview.MaxItemsReturned = 1000  # 1000 is the maximum number of items that can be returned in a CalendarView

 # Update the progress bar
  Write-Progress -Activity "Scanning Room Mailboxes ($RoomCounter/$($Rooms.count))" -status $Room.properties.displayname -CurrentOperation "Searching for meetings" -percentComplete ($RoomCounter/$Rooms.count*100)

  # Find the appointment items
  $AppointmentItems = $Null
  $AppointmentItems = $Service.FindAppointments($Calendar.Id,$CalendarView)
 Write-Verbose "$($AppointmentItems.TotalCount) appointments found"
 
 # Show a warning if there are more than 1000 items returned in the calendar view
 If ($AppointmentItems.TotalCount -ge 1000) {
  Write-Warning "$($AppointmentItems.TotalCount) meetings found in '$($Room.properties.displayname)' for the date range given. This exceeds the maximum API limit of 1000.  All conflicts may not be returned."
 }

 # Update the progress bar
  Write-Progress -Activity "Scanning Room Mailboxes ($RoomCounter/$($Rooms.count))" -status $Room.properties.displayname -CurrentOperation "Evaluating meetings for conflicts" -percentComplete ($RoomCounter/$Rooms.count*100)
 
 # Loop through all the appointment items returned
 For ($ItemIndex = 0; $ItemIndex -lt $AppointmentItems.Items.Count; $ItemIndex++) {
  $ThisItem = $AppointmentItems.Items[$ItemIndex]
  $ConflictCounter = 0
  
  Write-Verbose "Evaluating $($ThisItem.Subject) (Index: $ItemIndex)"
  # Move on if this appointment is cancelled
  If ($ThisItem.IsCancelled -eq $True) {
   Write-Verbose " - $($ThisItem.Subject) is cancelled"
   Write-Verbose ""
   Continue
  }
  
  # Look forward for a meeting which is not overlapping or adjaccent with this one
  $NextItemIndex = 1
  $UpperBoundaryFound = $False
  If (($ItemIndex + $NextItemIndex) -lt $AppointmentItems.Items.Count) {
   Do {
    If ($AppointmentItems.Items[$ItemIndex + $NextItemIndex]) {
     If ($AppointmentItems.Items[$ItemIndex + $NextItemIndex].Start -gt $AppointmentItems.Items[$ItemIndex].End) {
      $UpperBoundaryFound = $True
     }
    } Else {
     $UpperBoundaryFound = $True
    }
    $NextItemIndex ++
   } Until ($UpperBoundaryFound)
  }
  $UpperBoundary = $NextItemIndex - 1

  # Look backwards for a meeting which is not overlapping or adjacent with this one
  $NextItemIndex = -1
  $LowerBoundaryFound = $False
  If ($ItemIndex -gt 0) {
   Do {
     If ($AppointmentItems.Items[$ItemIndex + $NextItemIndex]) {
      If ($AppointmentItems.Items[$ItemIndex + $NextItemIndex].End -lt $AppointmentItems.Items[$ItemIndex].Start) {
       $LowerBoundaryFound = $True
      }
     } Else {
      $LowerBoundaryFound = $True
     }
      
    $NextItemIndex --
   } Until ($LowerBoundaryFound)
  }
  $LowerBoundary = $NextItemIndex + 1
 
  # Calculate the indexes of the appointment items which bound this one - we will check all items inside this range for conflicts
  $LowerIndex = $ItemIndex + $LowerBoundary
  If ($LowerIndex -lt 0 ) {
   $LowerIndex = 0
  }
  $UpperIndex = $ItemIndex + $UpperBoundary
  Write-Verbose "Bounded by $LowerIndex : $UpperIndex"
 
  # Use the boundary indices to check for conflicts with adjacent and overlapping meetings
  For ($AdjacentItemIndex = $LowerIndex; $AdjacentItemIndex -le $UpperIndex; $AdjacentItemIndex++) {
   $Overlapping = $False
   If ($AdjacentItemIndex -ne $ItemIndex) {
    $AdjacentItem = $AppointmentItems.Items[$AdjacentItemIndex]
    If ($AdjacentItem.IsCancelled -eq $True) {
     Continue
    }
    If (($AdjacentItem.Start -le $ThisItem.Start -and $AdjacentItem.End -gt $ThisItem.Start) -Or ($AdjacentItem.Start -lt $ThisItem.End -and $AdjacentItem.End -gt $ThisItem.End) -Or ($AdjacentItem.Start -ge $ThisItem.Start -and $AdjacentItem.End -le $ThisItem.End)) {
     Write-Verbose " - $($ThisItem.Subject) conflicts with $($AdjacentItem.Subject)"
     $ConflictCounter ++
    }
   }
  }

  # If we have conflicting appointments, get some more details for them and store them in teh global array 
  If ($ConflictCounter -gt 0) {
   Write-Verbose " - $($ThisItem.Subject) has $ConflictCounter conflicts"
   $ThisItem | Add-Member -MemberType NoteProperty -Name Room -Value $Room.properties.displayname[0] -Force
   If ($LoadAllProperties) {
    $ThisItem.Load()
   }
   $Conflicts = $Conflicts + $ThisItem 
  }
  Write-Verbose ""
 }
 
 # Increment the room counter
 $RoomCounter ++
}

# Update the progress bar
Write-Progress -Activity "Scanning Room Mailboxes ($RoomCounter/$($Rooms.count))" -Completed

# Output the conflicting appointments to screen and, if requested, files
If ($Conflicts) {
 $Conflicts = $Conflicts | Sort-Object -Property Room, Start, DateTimeReceived
 If ($OutputCsv) {
  $Conflicts | Export-Csv -Path $CsvFile -NoTypeInformation
 }

 If ($OutputHtml) {
  $Conflicts | Select-Object Room, Start, End, Duration, Subject, @{Name="Organizer";Expression={$_.Organizer.Name}}, DateTimeReceived | ConvertTo-Html -Title "Room Double Booking Report" -Head "" | Out-File $HtmlFile 
 }

 # Return the output
 Return $Conflicts
}
Write-Host
Download Script

If you find this script useful, and that you have conflicting appointments in your Exchange 2013 environment, please pop over to the TechNet forum and leave a reply on my post - we need more people to push Microsoft to fix this.

If you're on Yammer, you can join the discussion about this issue there as well.

- Ben

3 comments:

  1. Extremly helpfull. Thank you very much!

    ReplyDelete
  2. Hi! I receive the following errors when trying to use this script:



    Exception calling "Bind" with "2" argument(s): "Connection did not succeed. Try again later."
    At C:\Users\xxxxx\Desktop\Get-RoomDoubleBookings.ps1:60 char:3
    + $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($Service,$Folde ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceResponseException


    Exception calling "FindAppointments" with "2" argument(s): "The element at position 0 is invalid
    Parameter name: parentFolderIds"
    At C:\Users\xxxxxx\Desktop\Get-RoomDoubleBookings.ps1:71 char:3
    + $AppointmentItems = $Service.FindAppointments($Calendar.Id,$CalendarView)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException


    Any idea what could be wrong?

    ReplyDelete
  3. Ben, we combined what we were seeing on our blog (https://calendarservermigration.blogspot.com/2016/04/double-booked-meeting-rooms-in-office.html) with your comments and are near completing a cmdlet for PowerShell that 1.) reports on the issue but can also 2.) notify meeting organizers in advance of a conflict 3.) if a user is a designated VIP it can cancel the competing reservation and give it to them (obviously notifying the other organizer) 4.) any other features we come up with. We'd be happy to let you be a beta tester for it if you want.

    ReplyDelete