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.

Analyze Mailbox Items Class or Type

The purpose of this script is to go through every single item in a mailbox and produce a report listing the class (or type) of each item and how many items of each class were found in the mailbox:

Processed 52 folders and 19163 items.

Name Value
---- -----
IPM.Appointment                          194
IPM.Appointment.ReadiManagerMeeting      2
IPM.Contact                              164
IPM.Note                                 16911
IPM.Note.Exchange.ActiveSync.MailboxLog  2
IPM.Note.Microsoft.Conversation          1616
IPM.Note.Microsoft.Conversation.Voice    67
IPM.Note.Microsoft.Missed                2
IPM.Schedule.Meeting.Canceled            3
IPM.Schedule.Meeting.Request             43
IPM.Schedule.Meeting.Resp.Neg            3
IPM.Schedule.Meeting.Resp.Pos            20
IPM.Schedule.Meeting.Resp.Tent           2
IPM.Sharing                              120
IPM.StickyNote                           14

I created this script to have an idea of how items had been archived by EnterpriseVault across the users’ mailboxes (IPM.NOTE.EnterpriseVault.Shortcut class). The script reports on all item classes, but it can easily be changed to only look for a particular class, such as EV stubs for example.

To download the complete final script, please head on to the TechNet Script Gallery.

Meeting Room Random Free/Busy Error MapiExceptionRpcServerTooBusy

The other day I was faced with an issue where one or more meeting rooms would randomly not show their free/busy information depending on how many meeting room I was querying at the same time in Outlook. If using Outlook on the Web (aka OWA), a few rooms would not have free/busy information available.

After some digging, I found the following two events logged in the Exchange server (the output has been truncated):

Log Name:      Application
Source:        MSExchange Availability
Event ID:      4009
Task Category: Availability Service
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      EXAIO
Description:
Process Microsoft.Exchange.InfoWorker.Common.Delayed`1[System.String]: Unable to open connection for mailbox SMTP:R_IT_01B_CF@nunomota.pt. Exception returned is: Microsoft.Exchange.Data.Storage.StorageTransientException: Cannot query rows in a table. ---> Microsoft.Mapi.MapiExceptionRpcServerTooBusy: MapiExceptionRpcServerTooBusy: Unable to query table rows.

Log Name:      Application
Source:        MSExchange Availability
Event ID:      4009
Task Category: Availability Service
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      EXAIO
Description:
Process Microsoft.Exchange.InfoWorker.Common.Delayed`1[System.String]: Unable to open connection for mailbox SMTP:R_HR_04B_CF@nunomota.pt. Exception returned is: Microsoft.Exchange.Data.Storage.StorageTransientException: Cannot open mailbox /o=ADIAEXCHANGE/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Configuration/cn=Servers/cn=EXAIO/cn=Microsoft System Attendant. ---> Microsoft.Mapi.MapiExceptionRpcServerTooBusy: MapiExceptionRpcServerTooBusy: Unable to make connection to the server.

The problem was that the RPC Pool limit was being reached. For each database we can have up to 32 concurrent RPC connections from the same client process. In my case, most meeting rooms were distributed across mainly two databases:

Count Name
----- ----
   83 MDB01
   51 MDB02
    5 MDB03
    3 MDB04
    7 MDB05
    5 MDB06
    4 MDB07
    1 MDB08
    1 MDB09

As you probably guessed, the resolution is to simply split meeting rooms across all databases (when possible). Once I distributed them so there is only 17/18 meeting rooms per database, this error no longer happened.

Exchange Web Services Editor

Using PowerShell, Exchange administrators can develop scripts to do almost everything. However, there are occasions where a script that uses Exchange Web Services (EWS) is required, may that be for an administrative task or to develop an entire application.

 

I have developed a few EWS scripts but I admit I am no expert on the subject. As such, sometimes I rely on a great tool that not many people are aware of, the EWS Editor. This tool has 3 main goals:

1.      "Demonstrate the Exchange Web Services Managed API functionality and simplicity to developers through its source code;

2.      Demonstrate the Exchange Web Services SOAP traffic used to perform actions initiated through an explorer user interface;

3.      Assist non-developers in debugging and understanding Exchange stores by exploring items, folders, and their properties in depth."

 

Each release of EWS Editor includes the distribution of the EWS Managed API it was built for. EWSEditor uses .NET Framework version 4.5 which can be downloaded here: .NET Framework 4.5. The latest version of EWS Editor (from May 2015) uses .EWS Managed API 2.2, which requires a minimal of .NET 3.5. EWS Editor can be downloaded from CodePlex.

 

EWS Editor is, in some ways, similar to MFCMapi, another great tool. It does not require installation and unlike MFCMapi it does not require Outlook profiles in order to access mailbox items as it does everything over EWS.

We can check mailbox items and their properties:

We can look into particular folders, its emails, attachments, properties (notice how all the property names are easy to read), etc.:

 

We can test and get information regarding AutoDiscover:

The tool comes with a multitude of EWS Posts examples that we can use to see how they are built or even post them against Exchange and analyze the response:

 

We can also check and/or set Out-Of-Office messages for users (assuming we have the right permissions to do so):

It even comes with a handy Distribution Group expansion tool that allows us to see exactly who is a member of a particular group:

Another great tool is the Debug Log Viewer where we can see all the EWS requests and responses for all the actions we do using this tool (in this case we can see the response from the Messaging group expansion above):

There are many, many other things we can use this tool for:

Create Folder on Users’ Mailboxes

One could think that the Exchange Online and Exchange 2013 New-MailboxFolder cmdlet would allow administrators to create folders on other users’ mailboxes. Unfortunately this is not the case...

 

Basically RBAC (Role Based Access Control) only allows the administrator to run this cmdlet on the mailbox it owns. As we can see below, RBAC has an implicit recipient read and write scope set to Self:

 

So can we create a new role based on MyBaseOptions and update the ImplicitRecipientReadScope to OrganizationConfig? Once again, unfortunately no... You see, if you read the Understanding management role scopes TechNet article, it states that:

 

“You can't change the implicit scopes defined on management roles. You can, however, override the implicit write scope and configuration scope on a management role. When a predefined relative scope or custom scope is used on a role assignment, the implicit write scope of the role is overridden, and the new scope takes precedence. The implicit read scope of a role can't be overridden and always applies.”

 

By the way, the exact same thing applies to the Get-MailboxFolder cmdlet... The good news for this cmdlet is that we can simply use the Get-MailboxFolderStatistics cmdlet to list all folders in any mailbox we want.

 

 

So, as far as I know, there is nothing we can do to make this cmdlet work for other mailboxes the administrator does not own. So is there a way to create folders for other users? Yes, using Exchange Web Services (EWS) script!    :)

 

I have written a few EWS scripts to perform certain actions on mailboxes that are not possible using the native Exchange cmdlets. To achieve this, I am not going to re-invent the wheel as there is already a great script by David Barrett to do exactly what we want. For more information on his script, please check his blog article PowerShell: Create folders in users' mailboxes.

Microsoft Exchange Web Services Managed API 2.2 Released

The Exchange Web Services (EWS) Managed API 2.2 provides a managed interface for developing client applications that use EWS. The EWS Managed API simplifies the implementation of applications that communicate with versions of Exchange starting with Exchange Server 2007 SP1. Built on the EWS SOAP protocol and Autodiscover, the EWS Managed API provides a .NET interface to EWS that is easy to learn, use, and maintain.

This release also includes the Exchange Server 2013 token validation library that we can use to build mail apps for Outlook that can be authenticated by the identity tokens issued by Exchange 2013.

You can download this latest release here.

Exchange Web Services Managed API 1.2 Released

As discussed in my previous post EWS Managed API 1.2 SDK Documentation Available, the EWS Managed API v1.2 is now available for download here.

You can check all the new features in the What’s New website, but for me best additions/improvements are:

• GetPasswordExpirationDate method that allows you to get the domain password expiration date for a specified mailbox user;
• The possibility to set automatic replies, such as Out of Office (OOF) messages;
• The possibility to expand a distribution list to display all members ;
• The possibility of getting user free/busy information.

For more information:

• Gettingstarted with the EWS Managed API
• What’s New

EWS Managed API 1.2 SDK Documentation Available

The new Exchange Web Services Managed API v1.2 documentation is now available.

You can check all the new features in the What’s New website, but for me best additions/improvements are:

• GetPasswordExpirationDate method that allows you to get the domain password expiration date for a specified mailbox user;
• The possibility to set automatic replies, such as Out of Office (OOF) messages;
• The possibility to expand a distribution list to display all members ;
• The possibility of getting user free/busy information.

Unfortunately, you cannot download the new API just yet… As usual, the documentation was released earlier so that administrators/developers can start having a look at what’s coming. But it shouldn't be long before it is released.

For more information:

• Getting started with the EWS Managed API
• What’s New