update: 7/11/2017
If you want to quickly check a single user, see my new blog.
update: 5/26/2017
BIG speed improvement thanks to NickC, who commented on my post. Also, thanks to my colleague Andy, I’ve updated the progress bar calculation so that this script can run on Windows Server 2008 R2. Thanks guys!
(You’ll need to connect to Azure AD (MSOL) prior to running this script, more info on that here.)
In Office 365, licenses (also called SKUs) give users access to various Office 365 services. Each SKU defines a list of services. However, a user might not have access to all services of a given SKU. This is typical when onboarding users to Office 365 for the first time, some organizations choose to disable certain services while leaving others enabled. Office 365 PowerShell is useful to view the status of services on a given user account. This can become tricky when dealing with user accounts that have been assigned multiple SKUs.
In addition, performing a full-service audit (all users/all skus) in PowerShell can be incredibly challenging. This is primarily due to the way Azure AD (MSOL) stores the collection SKUs for a given user. The indexing is dynamic and based on the order of SKU enablement (assignment). For example, I assign User1 with E3 first and EMS second, then the index for E3 is [0] and the index for EMS is [1]. Then for User2 I assign EMS first and E3 second, the indexing for this account is the reverse and EMS is [0] and E3 is [1].
*Note that this index assignment is consistent in both PowerShell and the UI; however, if you enable/disable any services on a previously-assigned SKU using the UI, it will move that SKU to index [0]. If you use PowerShell, it will leave index number intact.
Many blogs which touch on this topic will hard-code the index to [0] in their PowerShell samples, and the assumption is your users have only one SKU or the SKU you care about is on index [0]. For example, blogs will say run the following to get service status for User1.(Get-MsolUser -UserPrincipalName [email protected]).Licenses[0].ServiceStatus
In my experience nearly every organization has more than one SKU. And often there is little to no consistency in the order of enablement nor consistency on which the SKUs are assigned. For example, I worked with one organization that had several thousand users and 28 different SKUs!
I wrote this script to be able to get around the dynamic index limitation, and it will get all licensed users and list their SKUs and corresponding services (and status). I hope this helps you in managing your licenses in Office 365.
(Thank you to Andy Pituch, Rianna Richardson, and Ryan Jackson for their input on this blog)
Full User SKU Service AuditPowerShell
########################Full User SKU Service Audit
#set output file
$outputpath = 'C:TEMP'
$date = Get-Date -Format "yyyy-MM-dd"
$tenant = (((Get-MsolDomain | ?{$_.IsInitial -EQ $true}).Name).Split(".",3) | Select-Object -Index 0).ToUpper()
$outputfile = "$outputpath"+"AzureAD_$tenant-User_SKU_Service_Audit_$date.csv"
#Get all users with a license
$LicensedUsers = Get-MsolUser -All | ?{$_.isLicensed -eq $true}
#Initialize hash table
$azdatastring = @()
$azhash = ""
#Initialize progress bar Loop counter
$loopcount = 1
#Set the upper for the loop counter
$maxcount = $LicensedUsers.count
#loop through each licensed user
Foreach($LicensedUser in $LicensedUsers) {
#Set upn for current user
$upn = $LicensedUser.UserPrincipalName
#Display progress bar
$percentage = [math]::Round($loopcount / $maxcount *100)
$message = "Gathering license info for $UPN ($loopcount of $maxcount)" -f $percentage
Write-Progress -Activity $message -PercentComplete ($percentage) -Status "$percentage % Complete: "
#Initialize index
[int]$index = 0
#Get assigned sku's for current user
$skus = $LicensedUser.Licenses.AccountSkuId
#Set upper for sku count
$scount = $skus.count
#Get all skus, services, status, for current licensed user
DO {
#loop through each sku
$skus | ForEach-Object{
$sku = $_
#get services for current sku
$services = $LicensedUser.Licenses[$index].ServiceStatus
#loop through each service
$services | ForEach-Object {
$service=$_
#store current service and status in hash table
$azhash = @{'UserPrinciPalName'=$upn;'AccountSkuId'=$sku;'ServiceName'=$service.ServicePlan.ServiceName;'ProvisioningStatus'=$service.ProvisioningStatus}
$azdatastring += New-Object PSObject -Property $azhash
}
#increment to next sku/index
$index++
}
}until ($index -eq $scount) #exit when done with all SKUs of current user
#Increment progress bar Loop counter
$loopcount++
}
#export hash to output file
$azdatastring |Select-Object UserPrinciPalName,AccountSkuId,ServiceName,ProvisioningStatus| Export-Csv $outputfile -NoTypeInformation