Selecting the tenant SMTP address from get-mailbox's emailaddresses attribute

 When performing tenant to tenant migrations, it's critical to use their tenant address so that post vanity domain name removal you can still reference and access the source side tenant.  For some time I did this pretty manually, while knowing there had to be a better way.  So without much more chatter, here's how I do this in Excel now.

First, get-mailbox -resultsize unlimited | export-csv .\temp\allmailboxes.csv -nti

Then, open in Excel, format as a table, and add a column for tenantaddress

Within that column, here's the code you'd want:

Consider the randomness of emailaddresses with smtp, sip, x400/500,etc.  Many times I want a report of all of one kind of address or the alias of each domain, so I came up with this:

 

=MID([@emailaddresses],FIND("smtp:",[@emailaddresses])+5,(SEARCH("@tenantname.onmicrosoft.com",[@emailaddresses])-FIND("smtp:",[@emailaddresses])-5))

 

Things to update for your needs:

1. In Yellow, the address type of "smtp" means non primary.  FIND is case sensitive, so if you want only primary, you should use PrimarySMTPaddress instead of this.  If you want case insensitive, change FIND to SEARCH above.
2. In Blue, the domain name with the tenant address you are seeking
3. In Red, if you change from say SMTP: to X500: the 5's work.  If you do SIP: then you want a 4 as its one less character.
   

 

 

End result:

 

 

Since I know in my case I wanted their tenantname alias, I can re-concatenate that from here, or adjust my search to catch exactly the whole email, depending on your Excel skills.

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:

• https://ingogegenwarth.wordpress.com/2016/11/15/troubleshhot-exchange-with-logparsermapi-client-access-logs/
• https://docs.microsoft.com/en-us/Exchange/clients/mapi-over-http/configure-mapi-over-http?view=exchserver-2019

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

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.

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

-----------                                -------------

[email protected]                               =>

[email protected]                               =>

[email protected]                               =>