Monday, May 15, 2017

Exchange Meeting Room Statistics

A while back I wrote an article named Exchange Meeting Room Statistics about a script to gather statistics regarding Exchange meeting room usage for MSExchange.org. For this script to work, we have to give ourselves FullAccess to the meeting rooms’ mailbox, add them into our Outlook profile, and then use an Outlook COM Object to connect to Outlook and gather this information. Far from ideal, especially when trying to analyse dozens of rooms!

I have finally written a new version that uses Exchange Web Services to gather the same information, plus some further stats. All the script requires is for AutoDiscover to be working, the EWS Managed API to be installed, and for the user running the script to have Reviewer permissions to the meeting rooms’ calendar (FullAccess permissions to the room’s mailbox will also work).

UPDATE (15/12/2017): I have updated the script to also work with Exchange Online (Office 365). If you want to analyse meeting rooms in EXO, simply add the -ExchangeOnline switch when running the script.

This new script, available in the TechNet Gallery, will gather statistics such as the number of meetings during the specified times, the total and average meeting duration (in minutes), the total and average number of attendees, how many meetings started in the morning and afternoon, how many recurring meetings, and the 5 five organizers and attendees. It will export all the stats to a CSV file and also print in on the screen:
PS C:\Scripts\> .\Get-MeetingRoomStats.ps1 -RoomListSMTP "room.1@domain.com, room.2@domain.com" -From "03/01/2017" -To "04/01/2017"

From         : 01/Mar/17 0:00:00
To           : 01/Apr/17 0:00:00
RoomEmail    : room.1@domain.com
RoomName     : IT - 16 Floor - Room 16.23
Meetings     : 104
Duration     : 4920
AvgDuration  : 47
TotAttendees : 442
AvgAttendees : 4
RecAttendees : 383
OptAttendees : 59
AMtotal      : 46
AMperc       : 44
PMtotal      : 58
PMperc       : 56
RecTotal     : 38
RecPerc      : 37
TopOrg       : user.1@domain.com (12), user.2@domain.com (9), user.3@domain.com (9), user.4@domain.com (7), user.5@domain.com (5), user.6@domain.com
(4), user.7@domain.com (4), user.8@domain.com (4), user.9@domain.com (4), user.10@domain.com (3),
TopAtt       : user.2@domain.com (25), user.4@domain.com (23), user.1@domain.com (19), user.3@domain.com (16), user.11@domain.com (16),
user.12@domain.com (15), user.9@domain.com (12), user.13@domain.com (11), user.14@domain.com (9), user.15@domain.com (9),


From         : 01/Mar/17 0:00:00
To           : 01/Apr/17 0:00:00
RoomEmail    : room.2@domain.com
RoomName     : IT - 16 Floor - Room 16.24
Meetings     : 121
Duration     : 6178
AvgDuration  : 51
TotAttendees : 570
AvgAttendees : 5
RecAttendees : 537
OptAttendees : 33
AMtotal      : 45
AMperc       : 37
PMtotal      : 76
PMperc       : 63
RecTotal     : 42
RecPerc      : 35
TopOrg       : user.16@domain.com (9), user.17@domain.com (8), user.10@domain.com (8), user.18@domain.com (7), user.19@domain.com (6),
user.20@domain.com (5), user.21@domain.com (5), user.22@domain.com (4), user.6@domain.com (4), user.23@domain.com (4),
TopAtt       : user.24@domain.com (22), user.4@domain.com (20), user.17@domain.com (17), user.25@domain.com (16), user.16@domain.com (15),
user.11@domain.com (12), user.26@domain.com (12), user.21@domain.com (12), user.27@domain.com (11), user.28@domain.com (11),

You can download the script from here.

14 comments:

  1. Thank you for this! It's great. I was having some trouble trying to get it to work with a large number of rooms. We have a request for a report like this for all of our rooms at one campus and that is over 300 conference rooms. I am sure I am over complicating this. I appreciate any help you can provide.

    ReplyDelete
    Replies
    1. Hi,

      Thank you! :)
      Not a problem, that is not too hard. Instead of:

      # Initialize some variables that will be used later in the script
      [Array] $roomsCol = @()

      # Connect to Exchange Server
      $service = Connect-Exchange -Mailbox ($RoomListSMTP.Split(",")[0])

      ForEach ($room in $RoomListSMTP.Split(",") -replace (" ", "")) {


      You can do something like:
      # Initialize some variables that will be used later in the script
      [Array] $roomsCol = @()
      $rooms = (Get-Mailbox -RecipientTypeDetails RoomMailbox -ResultSize Unlimited).primarySmtpAddress

      # Connect to Exchange Server
      $service = Connect-Exchange -Mailbox ($rooms[0])

      ForEach ($room in $rooms) {


      But you’ll have to run it from the Exchange server or from your workstation using the EMS.
      You can also use a Do/While statement to get stats for each room per day for example. You just need to increment the $To and $From and the end of that statement for example.
      Hope this helps!

      Regards,
      Nuno

      Delete
  2. Hi,
    the script is awsome but with our exchange 2010 it returns 0 for meetings, duration and everything else. Only the rooms e-mail adress and room number is correctly returened. where can i look for bugfixing?
    thanks
    martin

    ReplyDelete
    Replies
    1. Hi,

      Thank you! :)
      Are you getting any errors when using the -Verbose parameter? If the script is not able to connect to Exchange or to a meeting room's calendar, it should throw an error. Do you have the required permissions to the meeting rooms' Calendar?
      Try printing the result of $service after it is initiated to ensure you are indeed connected to Exchange.

      Regards,
      Nuno

      Delete
  3. Hi Nuno,

    great post.

    Is it possible to run the above script to collect the room mailbox utilaztion where my mailboxes are there in 0365.

    Please do confirm or what chaanges do we need to done as we have 2013 exchange hybrid model .

    Thank you,
    Druva

    ReplyDelete
    Replies
    1. Hi Rangitha,

      Sorry for the delay in replying. Thank you for the feedback! :)
      Please download the latest version of the script as I have just updated it to work with meeting rooms in Exchange Online!

      Regards,
      Nuno

      Delete
  4. Hi Nuno,

    great artical.

    Could let us know how do we run the same above script with 0365 as we have 2013 exchange with hybrid model and all our room mailboxes on 0365.

    Please help me.

    Thank you,
    Druva

    ReplyDelete
    Replies
    1. Hi Druva,

      Sorry for the delay in replying. Thank you for the feedback! :)
      Please download the latest version of the script as I have just updated it to work with meeting rooms in Exchange Online!

      Regards,
      Nuno

      Delete
  5. what needs to be different for this script to connect to Exchange Online?

    ReplyDelete
    Replies
    1. Hi,

      Sorry for the delay in replying. Please download the latest version of the script as I have just updated it to work with meeting rooms in Exchange Online!

      Regards,
      Nuno

      Delete
  6. getting the following error:

    Unable to connect to roomname@domain.com. Please check Permissions: Exception calling "Bind" with "2" argument(s): "Exchange Server doesn't support the requested version." Skipping roomname@domain.com.

    Notes:
    1) Using an account that is a member of Organization Management and has Full Access to mailbox.
    2) Tried on multiple mailboxes -- same error.
    3) output of $service variable is nothing
    4) using Exchange 2010 SP3 Rollup 15 with EWS API 2.2.

    Any ideas?

    ReplyDelete
    Replies
    1. Hi Steve,

      In the Connect-Exchange function, have you updated the Exchange version in the "$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2013_SP1)" line?

      Regards,
      Nuno

      Delete
  7. I've got a CSV of a couple of thousand rooms, what would be the simplest way to import the list? It's in SMTP format.

    ReplyDelete
    Replies
    1. Hi Derek,

      I would use Import-CSV and then a ForEach to call the script for each room in your CSV file. You would also need to update the last line when the data gets exported to a CSV file to either append it all to the same file, or to create a new file for each meeting room.
      Hope this helps!

      Regards,
      Nuno

      Delete