All posts by Jack

Modify-Sublicense PowerShell function for modifying Office 365 Sublicenses


An example call would be:

Modify-Sublicense -upn "" -PrimaryLicense "TENANT:ENTERPRISEPACK" -SublicensesToRemove @("SWAY","YAMMER_ENTERPRISE") -SublicensesToAdd @("SHAREPOINTENTERPRISE", "SHAREPOINTWAC")

If you’re new to powershell, scroll down past the code below for some additional tips.

Naturally, sublicenses you had before will REMAIN unless you’ve removed them.
Sublicenses you DID NOT HAVE before will NOT BE ADDED unless you specifically add them.

This is important because of how the licensing works, which has been covered in other blog posts my myself and others.

I’ve used the function below as part of a larger script with good results.


## this function Adds and or removes sublicenses 
## Pass in the -SublicensesToAdd and -SublicensesToRemove as an array ( use $var = @("value", "Value")  )
##  to a user's Pre-Existing License (for example TENANT:ENTERPRISEPACK)
function Modify-SubLicense($upn, $PrimaryLicense, $SublicensesToAdd, $SublicensesToRemove)
	$spouser = get-msoluser -userprincipalname $upn
	#assemble a list of sub-licenses types the user has that are currently disabled, minus the one we're trying to add 
	$disabledServices = $($spouser.Licenses | where {$_.AccountSkuID -eq $PrimaryLicense}).servicestatus | where {$_.ProvisioningStatus -eq "Disabled"}  | select -expand serviceplan | Select ServiceName 
	#disabled items need to be in an array form, next 2 lines build that...
	$disabled = @()
	foreach  ($item in $disabledServices.servicename) {$disabled += $item}
	write-host "   DisabledList before changes: $disabled" -foregroundcolor yellow
	#if there are other sublicenses to be removed (Passed in via -SublicensesToRemove) then lets add those to the disabled list.
	foreach  ($license in $SublicensesToRemove)
		$disabled += $license 
	#cleanup duplicates in case the license to remove was already missing
	$disabled = $disabled | select -unique
	#If there are licenses to ADD, we need to REMOVE them from the list of disabled licenses
	$disabled = $disabled | ?{$SublicensesToAdd -notContains $_}
	write-host "    DisabledList after changes: $Disabled" -foregroundColor green
    $LicenseOptions = New-MsolLicenseOptions -AccountSkuId $PrimaryLicense -DisabledPlans $disabled
	set-msoluserlicense  -userprincipalname $upn -licenseoptions  $LicenseOptions

New to powershell?

Here are a few tips:

  • Since the code is a function, you’ll need to copy-paste it to a script of your own before you can use it
  • In powershell, functions need to appear in your script above/before you use them.
  • There are ways to load the function in memory, so you can call it as if it was a native command. See How to add functions to your powershell session
  • An array in powershell can have zero or 1 or many items, if you need to pass a single value, just pass it as an array with one value. That would look like this: @("Value1", "Value2")

– Jack

Script to dump all your assigned Office 365 user licenses

As part of a licence transition from one level of office 365 to another, I wanted a way to record/audit who had what licences before/after the audit.

The script below dumps all the relevant information to a tab delimited text file.

Before we get to the script, I want to talk about some of the design considerations….

If we were to normalize the data we’d have 3 different sources of raw data:

  • List of users
  • List of possible licenses levels
  • List of possible sublicense levels.

To record all this in a relational database, you’d want a table for each of the above.

Then you’d need a few table to record the relationships and which users have which licenses.

Since many business users aren’t comfortable with relational data structures, I decided to de-normalize the data in favor of making it easy to consume.

In english, that means that much of the data will be repeated.

The fields we collect are:

  • UPN (usually email)
  • Lastname
  • FirstName
  • Displayname (It’s common for utility accounts like conference rooms to not have a first/last name so I include the displayname)
  • The Name of the Licence assigned to the user (the ‘primary’ license, otherwise known as the “MSOLAccountSKU”
  • The name of the sublicense that goes with the Primary *(See below)*
  • The deployment status of the sublicense


Notes about the ‘sublicense’ what I mean here is this: Say you have an E3 license, this is composed of a bunch of ‘sub’ liceneses that you can turn on-off independent of one another, examples of sub-licenses would be Exchange, SharePoint, Sway, Office Online, etc…

The sublicense values are not uniqe, for example, both E1 and E3 have a ‘Sway’ sublicense type.

Here’s another interesting tidbit: All primary licenses have at least one sublicense.

I wanted to flatten the data as much as possible so the sublicense field is a combo of the primary and sublicense

This will all be a lot clearer once you run the script so here it is…

write-host "Note, you must connect to MSOL first before running this script!"
#$users = get-msoluser -all
#$users = get-msoluser -maxresults 10
$users = get-msoluser -all | sort Lastname, firstname
$scriptpath = $(get-location).path
$file = "$scriptpath\MSOLLICENSEUSAGEREPORT_$(get-date -f yyyy-dd-MM_HHmm).csv"

# "sep=`t" | out-file $file #this helps excel open the file correctly...
$line = "UPN($($users.count))`t LastName`t FirstName`tDisplayName`t Primary`t Primary:secondary`t secondaystatus"
write-host $line
$line | out-file $file -append

$i = 0
foreach($user in $users)
	$count = $user.licenses.count
	# note get-msolaccountsku shows all licenses  

	if ($user.isLicensed -eq $false) 
		#write-host "$($user.UserPrincipalName) (0)" -foregroundcolor yellow
		$line = "$($user.UserPrincipalName)`t$($User.LastName)`t $($user.firstname)`t $($user.displayname)`tNonAssigned`tNonAssigned:None`tNone"
		$line | out-file $file -append
		write-host $line -foregroundcolor yellow
	foreach ($license in $user.licenses)
		write-host "$($user.UserPrincipalName) ($count) $($license.AccountSkuID)" -foregroundcolor yellow	
		$active = $license.servicestatus | where {$_.ProvisioningStatus -ne "disabled"}
		if ($active.count -le 0)
		    $Line = "$($user.UserPrincipalName)`t$($User.LastName)`t$($user.firstname)`t$($user.displayname)`t$($license.AccountSkuID)`t$($license.AccountSkuID):NoneAssigned`tEmptyLicense"
			write-host $line -foregroundcolor red
			$line | out-file $file -append
		foreach ($one in $active)
			#Write-Host "   $($one.ServicePlan.ServiceName): $($one.ProvisioningStatus) " -foregroundcolor green 
            $line =  "$($user.UserPrincipalName)`t$($User.LastName)`t$($user.firstname)`t$($user.displayname)`t$($license.AccountSkuID)`t$($license.AccountSkuID):$($one.ServicePlan.ServiceName)`t$($one.ProvisioningStatus)"
			write-host $line 
			$line | out-file $file -append
write-host "-----------------------------------------------------------------------------------"
write-host "Report is complete $i of $($users.count) users pulled for the report accounted for."
write-host "Saved as $file " -foregroundcolor green


PS Script to list all Office 365 licenses and sublicenses available (AccountSKUID and ServiceName)

We’re transitioning some licensing at work and I thought it would be helpful to have a ‘catalog’ of all the available options.

The script below will produce such a list…

#note, you must run connect-msol first!
$licenses = get-msolaccountsku
Write-host "This script will output all the license and sublicense types for your tenant"
write-host "(Technically, these are AccountSkuID and ServiceStatus.Serviceplan.Servicename)" -foregroundcolor gray
write-host " "
write-host "Primary License (AccountSkuID)" -foregroundcolor green
write-host "   Sub License (AccountSkuID.ServiceStatus.ServicePlan.ServiceName)" -foregroundcolor yellow
write-host "----------------------------------------------------------------------------"
foreach ($license in $licenses)
    $line = "{0,-35} {1,18} {2,18}" -f $license.accountskuID, "($($license.activeunits) active)", "($($license.consumedunits) used)" 
    write-host $line -foregroundcolor green
    foreach ($sublicense in $license.servicestatus)
    	write-host  "   $($sublicense.serviceplan.servicename)" -foregroundcolor yellow


Office 365 Client Performance Analyzer

Microsoft has a tool available for measuring Office 365 performance.

The tool tests dns response time, latency to the datacenter where your SPO or Exchange are located, and bandwidth.

It then displays results in either green or red based on the performance you’re getting.

The article linked above has both the download link and an explanation of each value.

It’s a handy tool, in that you can give it to your users if they say SPO is slow, and collect the data back. It’s much easier than asking them to time a page load, plus the page load wouldn’t say why the load is slow, just that it is slow. This tool will help provide data that can explain the why.

  • Jack

Replace an expiring Client Secret in an app for SPO

What are we even calling these things these days? Apps for SharePoint Online? Apps for Office 365?

This article is about the apps we build using the new app model that was introduced along with SharePoint 2013. It’s the main way of developing functionality for SharePoint Online.

The apps are hosted in Azure (or on the hosting provider of your choosing)

When these Apps are created/installed a Client secret is used to ensure that communication between your externally hosted app and SharePoint Online is secure and not coming from an attacker.

Unfortunately these certificates expire.

Ours have.

The article below talks about replacing them.

We also opened a ticket with Microsoft Premier Support which revealed a few more tidbits.

  • It takes like 24 hours for the new certificate to propogate through your system, leaving your app out of commission for at least that long if you don’t renew before it expires.
  • The article above mentions, but does not give an example of, extending the date from the default 1 year to 3 years. I’ve copied some of the correspondence with Mustaq Patel from Microsoft, who helped us through the process (Thanks Mustaq!)

Note for the scripts below, you’ll need your clientID this is in the web.config of your website that’s hosted in Azure.  As luck would have it, the person at our company who would have had this info was on vacation. Since it’s in the web.config of the running app, it made sense to just pull the actual web.config in use. I did this via FTP, using the steps in this article to configure an FTP account to gain access to the server:

Update: You can also find the clientID by going to any sharepoint site that uses the app, Site Settings->Site App Permissions.

It’ll be the guid between the last pipe symbol and the @ symbol

Example i:oi.t|blahblahblah|abcdef-1234-this-is-aguid-and-is-what-you-want@7a534-no-the-guid-you-want-123

(thanks to Mustaq for pointing this out!)

  1. Connect to MSOnline using tenant admin user with below powershell in SharePoint 2013 powershell
    import-module MSOnline
    $msolcred = get-credential
    connect-msolservice -credential $msolcred
  2. Get ServicePrincipals and keys. Printing $keys will give 3 records, replace each KeyId in key1, key2 and key3. You can also see EndDate of each key. Confirm if your expired key shows there. Also note that clientId needs to match as per your clientId.

    $clientId = "29b6b386-62a6-45c7-beda-abbaea6eecf2<< CHANGE THIS"
    $keys = Get-MsolServicePrincipalCredential -AppPrincipalId $clientId
    Remove-MsolServicePrincipalCredential -KeyIds @("key1","key2","key3") -AppPrincipalId $clientId
  3. Generate new ClientSecret for this clientID. Please note it uses clientId set in #2. Also ClientSecret is valid for 3 years.

    $bytes = New-Object Byte[] 32
    $rand = [System.Security.Cryptography.RandomNumberGenerator]::Create()
    $newClientSecret = [System.Convert]::ToBase64String($bytes)
    $dtStart = [System.DateTime]::Now
    $dtEnd = $dtStart.AddYears(3)
    New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Sign -Value $newClientSecret -StartDate $dtStart  –EndDate $dtEnd
    New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Symmetric -Usage Verify -Value $newClientSecret   -StartDate $dtStart  –EndDate $dtEnd
    New-MsolServicePrincipalCredential -AppPrincipalId $clientId -Type Password -Usage Verify -Value $newClientSecret   -StartDate $dtStart  –EndDate $dtEnd
  4. Copy the output of $newClientSecret.

  5. Replace the Web.config with this ClientId and ClientSecret. Please note we don’t need SecondaryClientSecret appsettings.

  6. Wait for 24 hours to propagate ClientSecret to SPO

Sharepoint Online Permissions oddity

One of my co-workers had an issue with SPO where a user she added to the owners group still had no permissions on the site.

We opened a case with Microsoft and the issue was some weird permissions issue : the Site owners group didn’t have the correct permissions to grant or modify permissions for users or to view the related settings pages in site settings. Microsoft said it was related to the access request list. 

MS provided a KB article:

My Super-Awesome-Fantastic Co-Worker Melissa Seals did all the work on this case, so I can’t claim to have any part in the solution, but it seemed obscure enough that it’s worth documenting here should we ever need it again. (She followed the steps in the article and got it working!)

  • Jack



FINALLY! It’s now possible to migrate data to SharePoint Online without data loss.

After 18 months of persistence, we’re finally able to migrate to SPO.

Up until yesterday, it wasn’t possible to migrate user data from SharePoint if the user was missing from Active Directory. It is now if you’re migrating to SharePoint online and using the new migration API!


Early on we ran into what was a pretty glaring problem for us, and I suspect for anyone else trying to migrate using a client side migration tool.

The Problem:

One of the data types in SharePoint is the “user” data type.
This is most commonly used/seen in document libraries – it shows you who last edited/updated a document.
It’s also commonly used as a field type in a SharePoint list.
For example, you might have a sharepoint list named “legal cases”

The problem is, using the client side API used for most migration tasks involving SharePoint Online, you can’t insert a name of a person if that name can’t be found in Azure Active Directory.

The 18 month Path to Resolution:

While it didn’t take Microsoft 18 months of actual work to fix this, it was about 18 months from start to finish.

I won’t bore you with the details, but the take away here is to never give up. We were told ‘no’ or ‘it can’t be done’ or ‘that’s by design’ multiple times.  This was an important issue so we pressed on, reaching out to every contact we knew, from multiple levels within our organization to multiple levels within Microsoft. All that persistence paid of!


One conversation along the way that was particularly interesting was a special presentation Microsoft had arranged where we got to talk to the person who led Microsoft’s internal migrations from on premise to office 365. I was eager to ask them how they had moved list data with abandoned user data – I was certain they had some internal tool, or did some back door load that wasn’t available to outsiders, or maybe had some script that identified every instance of the missing users and recreated them in AD so the migration could complete.  When I asked, they skirted the issue. I pressed on. After they told me, I understood why. They didn’t do it. When pressed they said they had to leave some data on premise because there was no way to move this data to SPO. On that day, I felt both validated and let down at the same time!

The Solution:

At ignite 2015, Microsoft announced a new Migration API for SharePoint Online.

On the same day, ShareGate announced that it would support this new API with it’s new ‘insane mode’. I spoke with a few people, some thought this new API would resolve the issue, while others said it would not – it didn’t at that time. 🙁

Shortly after our case must have reached one of the senior escalation engineers at Microsoft – I remember being told that the new API resolved the issue, then going back with evidence that it didn’t and I think that’s when traction really picked up. We supplied a Business impact statement and Microsoft added the fix to the list of things they were working on.  The feedback I got down the road was that this ended up being a huge undertaking for them. It wasn’t nearly as easy as one would think, due to how SharePoint is structured internally.  There were multiple setbacks, but we received excellent communication and updates. The time line didn’t bother me – I was thrilled to know it was being worked on.

Fast forward to fairly recently and we received word that the fix was approved and would be moved into our tenant. This was great news! Work didn’t end there however.

Microsoft went into depth about the work done to fix this issue.

While I had expected some new API, or an option when sending data like “override if missing” no such changes were needed. Microsoft updated the migration API to handle all of the needed back end stuff seamlessly. They did not update the CSOM. This meant that for this to work, the new API had to be used.

We were already using ShareGate coupled with insane mode which uses the API. I remember from past conversation with them that ShareGate uses a combination of insane mode and CSOM – even when insane mode is being used – I figured this would mean ShareGate would need to be aware of the changes to the migration API and would need to handle things differently. For example, in the past, ShareGate could replace a missing user with a user of our choice, this would no longer be necessary.

ShareGate was great to work with – they had long been aware of the user data migration issue and understood what I was talking about almost immediately.  Once the API had been updated, the three of us worked together to ensure that Sharegate’s test tenant also had the new Mirgration API updates so they could code up a solution.

I’m soo pleased to say that Sharegate turned this around in about a month, and we received a beta last Friday.  Even more impressive is that the very first beta from ShareGate with support for this worked like a charm!

Even more impressive is that the very first beta from ShareGate with support for this worked like a charm!

Sharegate has released version 5.8 with this functionality Today (2/29/2016).

Confirmed: Microsoft has already rolled this out to all O365 tenants. (as of 2/29/2016)

Special kudos and thanks to Brent Vezzoso and the unnamed hero’s within Microsoft who worked so tirelessly to make this happen. I can’t tell you enough how much this means to me, my company, the SharePoint community, and to Microsoft. You’ve done a great thing here!

  • Jack

Office 365 groups = SharePoint Site confusion

When you create an Office 365 Group, that mechanism creates a onedrive site in sharepoint online.

Now normally onedrive is synonymous with “Mysites” and mysites are all in the* namespace.

In the case of Office 365 groups however, this isn’t the case.

Office 365 groups create onedrives in the* namespace.   This can create confusion if you’re using sharepoint tools to enumerate your sites. The sites Office 365 groups create do not appear in the site listing under SPO site collection administration.

Additionally, the sites don’t appear via powershell when using the get-sposite -limit all command.

Interestingly, if you know the exact url of a group site you can pull that up with get-sposite, it just won’t display when you’re trying to pull a list of all site collections.

Microsoft has lumped the commands needed to enumerate Office 365 Groups in with the exchange commandlets.

The command you want is get-UnifiedGroup.

As of this writing, I still can’t find where to download this command from, but I’ll update the article once I find it…

You’ll need special permissions to run this command – you can find info about that here: