Thursday, January 17, 2019

Reporting on Office versions in use using MAPI over HTTPS logs and LogParser

Way back in 2014, I blogged about using LogParser to report on Office versions from RPC over HTTPS logs, and I am happy to say it was one of my most referenced articles - and many times, it was referenced by me.  Well, it's now 2019, and after five whole years I had to revisit this finally when a customer moving to the cloud had been using Exchange 2016 already and had transitioned to MAPI over HTTPS already. 

Note: Where the MAPI logs are and how they are formatted changed post CU4, so please keep in mind that these are specific to 2016 CU4 or later.  Pre CU4 the folder and the logs have different path, naming, and fields.

So the technology here isn't hard, but the good news is - MAPI over HTTPS logs are a LOT better and provide greater detail to manipulate.

So, first, figure out which servers are in use for MAPI over HTTPS, navigate to their logging folder at $exinstall\Logging\MapiHttp\Mailbox (post CU4) and grab the files that look like: MapiHttp_YYYYMMDD##-#.log where ##-# are indexes - depending on size, configuration and server traffic, you could have one a day or hundreds per day.  Similarly, if you are a very large org and you only have a day or two of traffic, that may not be sufficient for analysis.

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT ActAsUserEmail, ClientIP, ClientSoftware, ClientSoftwareVersion INTO c:\temp\ClientInfo.csv from 'c:\temp\exchange1\*.log','c:\temp\exchange2\*.log'" -i:CSV -o:csv

Depending on your logging settings and Org size, the processing may take a long time.  I've bolded the fields I am using, but there are many others you can choose to include:

DateTime, RequestId, MapiRequestId, ClientRequestId, RequestType, HttpStatusCode, ResponseCode, StatusCode, ReturnCode, TotalRequestLatency, DeploymentRing, MajorVersion, MinorVersion, BuildVersion, RevisionVersion, AuthenticatedUserEmail, UPN, Puid, TenantGuid, MailboxId, MDBGuid, ActAsUserEmail, ClientIP, SourceCafeServer, EdgeInfo, NetworkDeviceInfo, SessionCookie, SequenceCookie, MapiClientInfo, ClientSoftware, ClientSoftwareVersion, ClientMode, AuthenticationType, AuthModuleLatency, LiveIdBasicLog, LiveIdBasicError, LiveIdNegotiateError, OAuthLatency, OAuthError, OAuthErrorCategory, OAuthExtraInfo, AuthenticatedUser, RopIds, OperationSpecific, GenericInfo, GenericErrors

Then, I open the resulting CSV in Excel, Use the "make this a table" and then use Excel to "Summarize Data with Pivottable" - using the data from my LogParser command above, Here's what my PivotTable configuration looks like:

Finally - with the new things this logging provides, I can provide targeting reporting on versions that are not compatible with Exchange Online, or versions that are not compatible with other requirements (for example MFA won't work in Outlook 2010, and in Outlook 2013, a registry change is needed for ADAL.

The resulting output looks something like this (redacted PII of email address and IP address)

This allows me to hand off to a customer's desktop support team a MUCH more targeted list of machines and devices to address.

Keep in mind the "Count" here is "number of times a particular version of Outlook touched any of the servers audited" so it's not terribly meaningful aside from a guide of frequency.  And finally - remember that these are rolling logs, so infrequently used desktops/laptops may not be present in the data you collect.  And if they roll over very quickly, you may want to consider extending