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

Sources:

Wednesday, January 24, 2018

Exchange - Changing from ForwardingAddress to ForwardingSMTPAddress for multiple mailboxes

Whoa, has it really been almost TWO YEARS since I've posted?  Well, if you still follow over here, be sure to follow me on twitter at https://twitter.com/chrislehratx - I tend to share out articles, blogs, and oneliners related to the Microsoft Exchange and/or Skype products over there.  Far more frequent updates than here.  But today, my script went from a one-liner to a multiliner, so I figured I would blog.

I had a weird need arise where users that were using ForwardingAddress were moved to the cloud, and the mail contact object that was selected for them was NOT synchronized to Azure AD, so it got stripped from the mailbox configuration.  The fix was to reference the mail contact per mailbox, get the SMTP address and then post a set-mailbox command with this data.

Script below - enjoy.

A few notes:

  1. In the get-mailbox, you can change the OU to a | where { $_.something -match ""} in order to capture your target list of mailboxes for your own situation.  I happened to have them all in an OU together.
  2. In the set-mailbox, this currently has a -whatif - you should NEVER ever do bulk AD updates without this first, so I left it in the script.

 # Get all the target mailboxes and assign to $x - change below to match your target audience as needed!
$x = get-mailbox -resultsize unlimited -organizationalunit "OU=TargetOU,OU=Users,OU=Mailboxes,DC=contoso,DC=com"
foreach ($mailbox in $x) {
# capture their SMTP address in the contact in a string
$SMTPforwardingaddress = ((get-mailcontact -Identity (((get-mailbox $mailbox).forwardingaddress).distinguishedname)).emailaddresses | where { ($_.isprimaryaddress -eq $true) -and ( $_.prefixstring -eq "SMTP")}).addressstring
# Null out their forwarding and replace with SMTPforwarding
write-host "Setting $mailbox to forward to $SMTPForwardingaddress"
set-mailbox -identity $mailbox -whatif -forwardingaddress $null -ForwardingSmtpAddress $SMTPforwardingaddress
}


If you like to understand Shell that one beast mode line broken out:

$SMTPforwardingaddress = ((get-mailcontact -Identity 
(((get-mailbox $mailbox).forwardingaddress).distinguishedname)).emailaddresses 


This does a get-mailbox, references the existing forwarding address object's DN, then the EmailAddresses, which is a ProxyAddresses format

| where { ($_.isprimaryaddress -eq $true) -and ( $_.prefixstring -eq "SMTP")}).addressstring


This captures what should be THE ONE address that is the primary and SMTP address (as opposed to X400)

And then once we have the string, the set-mailbox command nulls the ForwardingAddress and sets the string for ForwardingSMTPAddress

Finally - here's what it looks like (heavily redacted) when run (at least with -whatif still in there!)

Wednesday, March 16, 2016

Configuring Skype for Business CDR and QoE Reporting services to be highly available

Frequently I see customers successfully implement CDR and QoE reporting on two SQL reporting servers, but find that their reporting services web URL only works on the SQL server that is the primary node for these databases.   When you attempt a connection to the SQL Reporting server that the databases are not mounted on, you receive an error similar to the below:



An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CDRDB'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


In order to make Reporting services truly highly available, you need to complete the steps below from this Microsoft TechNet article Associating Monitoring Reports with a mirror database in Skype for Business Server 2015.

"To get Monitoring Reports to automatically failover to the mirror database, you must add the mirror database as a "failover partner" to the two databases that are used by Monitoring Reports (one database for Call Detail Record data, and the other for Quality of Experience (QoE) data). (Note that this step should be performed after you have installed Monitoring Reports.) You can add the failover partner information by manually editing the connection string values used by these two databases."

So on each http://SQLreportingServer01/Reports URL, click into the Reporting Instance:


Click into Reports_Content:


Click into CDRDB:


In the "Connection String" dialog you will see something similar to:
data source=(local);initial catalog=LcsCDR

Edit this content to add the following (in bold)
data source=(local);Failover Partner=SQLREportNode02;initial catalog=LcsCDR

If you had a configuration where you specified an instance name, this would look more like this:
data source=(local\CDRReporting);Failover Partner=SQLREportNode02\CDRReporting;initial catalog=LcsCDR


Note:  If you have security set to use credential stored securely, you will need to retype the password to make this change!

Once you complete these tasks for the CDRDB, repeat these steps on the QMSDB.

Once you complete these tasks on both CDRDB and QMSDB, repeat the process on your second SQL reporting node.

Once these tasks are completed, both of your reporting URLs should work regardless of where your S4B backend SQL CDR/QoE databases are mounted.


Monday, November 09, 2015

Find all Exchange mailboxes missing an Office 365 license in one line of code



Hybrid scenario – customer moved mailboxes and didn’t license them.

Pre-req of MSOL and EXO shell loaded in the same instance - super easy with my script!

$msol = get-msoluser -all | where { $_.isLicensed -eq $true }; $mbox = get-mailbox -ResultSize 5555555; Compare-Object -ReferenceObject $msol.userprincipalname -DifferenceObject $mbox.userprincipalname

Output options:

-excludedifferent
-includeequal

See more on Compare-Object here, here and here.

Sample output (neither output option shows just the delta items)

InputObject                                SideIndicator
-----------                                -------------
user1@contoso.com                               =>
user2@contoso.com                               =>
user3@contoso.com                               =>