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

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_EWS.ps1 -RoomListSMTP "room.1@domain.com, room.2@domain.com" -From "03/01/2017" "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.

4 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