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.

UPDATE (March/2020): I have finally written this newer version, specifically targeted at Exchange Online only, this time using Graph API! Please check it here.

This new EWS script, available in GitHub, 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.

67 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. If you want to run it in Exchange 2010 and Win Server 2008 R2, you need do below adjustment.

    Instll powershell 4.0

    Add below code to Turst all certificate

    ## Code From http://poshcode.org/624
    ## Create a compilation environment
    $Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
    $Compiler=$Provider.CreateCompiler()
    $Params=New-Object System.CodeDom.Compiler.CompilerParameters
    $Params.GenerateExecutable=$False
    $Params.GenerateInMemory=$True
    $Params.IncludeDebugInformation=$False
    $Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

    $TASource=@'
    namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
    public TrustAll() {
    }
    public bool CheckValidationResult(System.Net.ServicePoint sp,
    System.Security.Cryptography.X509Certificates.X509Certificate cert,
    System.Net.WebRequest req, int problem) {
    return true;
    }
    }
    }
    '@
    $TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
    $TAAssembly=$TAResults.CompiledAssembly

    # create an instance of the TrustAll and attach it to the ServicePointManager
    $TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
    [System.Net.ServicePointManager]::CertificatePolicy=$TrustAll


    Install EWS Web Api 1.2 instead of 2.0 or 2.2

    Warm Regards
    NNIT-Messaging team

    ReplyDelete
  6. 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
  7. 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
  8. 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
    2. Thanks a lot, it's super
      I had problem with AM/PM resolution when other as EN culture set, so I changed it to:
      (Get-Date $meeting.Start -UFormat %H) -lt "12")

      When you need to report all rooms, you can read them directly from AD with this command:
      (Get-ADuser -Filter {msExchRecipientTypeDetails -eq 16} -Properties EmailAddress).EmailAddress

      (They have to be created as RoomType mailboxes)



      Beedo

      Delete
    3. Nice! Thanks for the tip Beedo! :)

      Delete
  9. Hello Nuno,
    I'm duplicating this message from TechNet Gallery:

    I've tried to run your script on my work space but it gives me an error:
    "Unable to connect ***@***.com. Please check permissions: Exception calling
    "Bind" with "2" argument(s): "Exchange Server doesn't support the requested version.". Skipping ***@***.com."
    We use Exchange 2010 SP3, so I've already changed your code for it. We also don't use Exchange Online.
    What do you think could be the source of this error?


    Thanks,
    Avi711

    ReplyDelete
  10. Man, you are life saver! Thanks!

    ReplyDelete
  11. Man, you are lifesaver! Thanks!

    ReplyDelete
    Replies
    1. Hehe, thank you! :) Don't forget to check the new version that uses Graph API: https://letsexchange.blogspot.com/2020/03/exchange-online-meeting-room-statistics.html

      Delete
  12. I have error below if I try run it with O365

    VERBOSE: Unable to connect to level@kross.pl. Please check permissions: Exception calling "Bind" with "2" argument(s): "The SMTP address has no mailbox associated with it.". Skipping level@kross.pl.


    Can you help me?

    ReplyDelete
    Replies
    1. As the error suggests, it seems that a room with that address does not exist. Have you double-checked it?

      Delete
  13. I have hybrid setup and Mailbox are on Exchange 2013 Version 15.0

    Should I add like this

    $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange15.00)

    ReplyDelete
  14. Hi ,

    On running the script i get error : Unable to connect to xyz@domain.com. Please check permissions: Exception calling "Bind" with "2"
    argument(s): "The request failed. The remote server returned an error: (401) Unauthorized.". Skipping xyz@domain.com.

    I have hybrid environment, all the room mailbox is synced with AD, while i have Full Access on Mailbox and Reviewer Access on Calendar.

    ReplyDelete
    Replies
    1. Hi,

      Is the mailbox you are trying to connect to on-prem or cloud? I would say this is most likely permissions!

      Delete
  15. am getting error VERBOSE: Unable to connect to room@xxxx.org Please check permissions: Exception calling "Bind" with "2" argument(s): "The SMTP address has no mailbox associated with it.". Skipping room@xxxx.org. am running 2010 sp3 Hybrid, account used has full permission as it accessible on outlook.

    ReplyDelete
    Replies
    1. Are you sure you typed the correct SMTP address for the meeting room? Try: "Get-Mailbox room@xxxx.org" and see if it resolves to the meeting room you want.

      Delete
  16. This comment has been removed by the author.

    ReplyDelete
  17. Hi Nuno,
    Running this in windows powershell from my PC, I get the error "unable to connect to the (mailbox SMTP) that I have full access to and then it says Exchange server doesn't support the requested version" You can't run Get-mailbox in windows PS


    ReplyDelete
    Replies
    1. Hi. Yes, you need to run the script from the Exchange Management Shell.

      Delete
  18. Im running into an errow where "Exception calling "AutodiscoverUrl" with "1" argument(s): "Autodiscover blocked a potentially insecure redirection to
    https://autodiscover-s.outlook.com/autodiscover/autodiscover.xml."

    Is there a workaround for this?

    ReplyDelete
    Replies
    1. I don't think your AutoDiscover is configured correctly... Where is the internal record pointing to? Also, is the mailbox you want to get stats from on-prem or cloud?

      Delete
  19. Hello, I am getting the following error:

    VERBOSE: Unable to connect to LIB-PTM-Conference-Room@mydomain.com. Please check permissions: Cannot convert argument "1", with value: "LIB-PTM-Conference-Room@mydomain.com", for "FolderId" to type"Microsoft.Exchange.WebServices.Data.Mailbox": "Cannot convert the "LIB-PTM-Conference-Room@mydomain.com" value of type "Microsoft.Exchange.Data.SmtpAddress" to type Microsoft.Exchange.WebServices.Data.Mailbox".". Skipping

    I have verified I have granted full access mailbox permission on the room mailboxes, and also "view only organization management" role. I am also running this from a 2012R2 server with the Exchange tools and Exchange Managed services API 2.2 installed, from the Exchange shell as administrator.

    ReplyDelete
    Replies
    1. Hi,

      Did you change the script at all? How are you calling the script?
      Please make sure you are using the latest version from here: https://gallery.technet.microsoft.com/Exchange-Meeting-Room-2aab769a

      Delete
  20. For me it's not producing any results. Nothing happens after command execution. I get get-cred window, where I enter my cloud permissions and that's it. CSV file is completely blank too.

    ReplyDelete
    Replies
    1. Hi. Are you using the latest version from TechNet?

      Delete
  21. hi, i have an error when i try to connect to O365. I use MFA, it's probably the problem ? I don't have any error on environment with no MFA. thanks

    ReplyDelete
    Replies
    1. Hi. Yes, it will be. But if you authenticate using the PowerShell module for MFA, you can then run the script. Is that not working?

      Delete
  22. Hi, Thank you for the script! I can't connect to most of the user calendars with this error, some work, but most do not. They do exist and have calendars. I'm a global admin. Any pointers?
    -----------
    VERBOSE: Binding to the user@domain.org Calendar folder.
    VERBOSE: Unable to connect to user@domain.org. Please check permissions: Exception calling "Bind" with "2" argument(s): "The specifie
    d folder could not be found in the store.". Skipping user@domain.org.
    -------------

    ReplyDelete
    Replies
    1. Hi, You're welcome! :) If some work and others don't, then it is either permissions or the wrong SMTP address. Could you please check these two?

      Delete
  23. Hi, I have 5 rooms that i want the stats from. But the problem is I want it day by day

    So day 1
    Room 1
    Room 2
    Room 3
    Room 4
    Room 5
    Day 2 etc

    But if I run the script I have to login again if I do day two. Is it possible to skip that once connected to office 365 powershell?

    ReplyDelete
    Replies
    1. Hi Mitchel,

      Yes! Instead of using "$cred = Get-Credential" you could export your credentials to a secure XML file and then import them every time you run the script, without having to manually type your username and/or password :)
      For example: https://www.jaapbrasser.com/quickly-and-securely-storing-your-credentials-powershell/

      Regards,
      Nuno

      Delete
  24. Getting error message "The response received from the service didn't contain valid XML" Please advise.

    ReplyDelete
    Replies
    1. Is that for on-prem or cloud? For cloud, I have released a new version that uses Graph API instead: https://gallery.technet.microsoft.com/scriptcenter/Exchange-Online-Meeting-4894b38f

      Delete
  25. Since Technet has been removed. please send me the new location of the script

    ReplyDelete
    Replies
    1. Hi Brijesh,
      Please check the latest Graph API version in this post: https://letsexchange.blogspot.com/2020/03/exchange-online-meeting-room-statistics.html

      Best regards,
      Nuno

      Delete
  26. Hi Nuno, Thank you for your reply. If possible can you provide me the onprem script for the same.

    ReplyDelete
    Replies
    1. Hi Brijesh,

      Sure, you can find it here: https://github.com/NunoFilipeMota/PublicScripts/blob/main/GetMeetingRoomStats_EWS.ps1

      Regards,
      Nuno

      Delete
  27. Thank you Nuno but it seems some error with -all parameter. I get below error message

    C:\Secure\Get-MeetingRoomStats.ps1 : A parameter cannot be found that matches parameter name 'All'.

    ReplyDelete
    Replies
    1. Hi Brijesh. This script does not have that parameter. You have to specify either a file with the addresses of the rooms you want to analyse with the -RoomListFile, or use the -RoomListSMTP to specify the rooms when calling the script.

      Delete
  28. Hello, perfect dcript , rly, is it possible to do statistics per hour to find out which time is the busiest? exchange 2016

    ReplyDelete
    Replies
    1. Thanks! :)

      Yes, it is possible, but it won't be straightforward... You'll probably have to add a loop inside "ForEach ($meeting in $fiItems.Items) {" to check for meetings at each our, and then move the "$romObj = New-Object PSObject -Property @{" to inside this ForEach.

      Regards,
      Nuno

      Delete
  29. Any way we could get this original script cant seem to find the code anywhere.

    ReplyDelete
    Replies
    1. Hi Erwin. The very first version of the script is available in the article mentioned right at the beginning of the post. For the EWS and Graph API versions, there are links in the post.

      Regards, Nuno

      Delete
  30. Hi, very interesting script. New to all this. Script does not appear to find EWS Managed API which I see installed in the default location at C:\Program Files\PackageManagement. How do I include this location in where the script looks for the .dll?

    ReplyDelete
    Replies
    1. Hi Andreas. That's strange as the script should detect it through the Registry... If you want to manually specify it's location, you can just update the $EWSdll variable, or replace the whole function with something like:

      $WebServicesdll = "C:\Program Files\Microsoft\Exchange\Web Services\x.x\Microsoft.Exchange.WebServices.dll"
      [void][Reflection.Assembly]::LoadFile($WebServicesdll)

      Delete
  31. hello, in comment, you had specified " 0.3 - 201911 - Added "-All" switch to automatically process all meeting rooms in the environment" but this parameter is not recognised.

    ReplyDelete
    Replies
    1. Hi. Are you trying to use the EWS or Graph API version? If Graph API, are you using this one: https://github.com/NunoFilipeMota/PublicScripts/blob/main/GetMeetingRoomStats_GraphAPI.ps1 ?

      Delete
  32. Hello, Does this script support exchange 2016 CU22 ?I am trying to run this script against E2k16 servers, i am not getting error while executing's but the excel file is empty. I have got around 200+ meeting rooms. Any help will be appreciated

    ReplyDelete
  33. Hi Nuno , i am trying to use get room statistics using EWS . i am getting below error - i checked that there is no firewall/Azure conditional access policy error. i changed my domain to domain.com to hide my domain. Please do suggest. Thank you always for script, it worked for two years for me.

    ==============================================================

    .\Get-MeetingRoomStats.ps1 -RoomListSMTP IRV_RH_BIG2_FL2_Conf_Rm_A@domain.com
    Exception calling "AutodiscoverUrl" with "1" argument(s): "Autodiscover blocked a potentially insecure redirection to
    https://autodiscover-s.outlook.com/autodiscover/autodiscover.xml. To allow Autodiscover to follow the redirection, use the AutodiscoverUrl(string,
    AutodiscoverRedirectionUrlValidationCallback) overload."
    At C:\Manoj\Room scripts\Get-MeetingRoomStats.ps1:105 char:3
    + $service.AutodiscoverUrl($Mailbox)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : AutodiscoverLocalException

    ReplyDelete
    Replies
    1. Hi. If you want to run the script against Exchange Online, please use this version of the script instead that uses Graph API: https://letsexchange.blogspot.com/2020/03/exchange-online-meeting-room-statistics.html

      Delete
  34. Hi i downloaded the latest version from Github and i am able to connect to O365 when i run the script with -All it finds 10 meetings rooms but fails with "Unable to query Graph API: 'Response status code does not indicate success: 404 (Not Found).'"

    ReplyDelete
    Replies
    1. Hi. If you want to run the script against Exchange Online, please use this version of the script instead that uses Graph API: https://letsexchange.blogspot.com/2020/03/exchange-online-meeting-room-statistics.html

      Delete
  35. Hi. Seems to be a lot of confusion around the usage of the '-All' parameter to capture all rooms. I'm having the same problem while using the EWS version with Exchange 2016. The '-All' parameter is listed as working according to the notes of the script but it isn't recognized when running the script, i'm getting the same error.

    ReplyDelete
  36. im getting a the ampersand & character is not allowed. the & operator is reserved for future use; wrap an ampersand in double quatation marks to pass it as part of a string error.

    ReplyDelete