O365 E5 Licence report

%3CLINGO-SUB%20id%3D%22lingo-sub-1850316%22%20slang%3D%22en-US%22%3EO365%20E5%20Licence%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850316%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20trying%20to%20use%20Powershell%20to%20return%20a%20report%20of%20all%20users%20who%20have%20the%20Power%20BI%20pro%20option%20enabled%20under%20office%20365%20E5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20clarify%2C%20this%20is%20not%20the%20Power%20BI%20Pro%20licence%20that%20can%20be%20purchased%20separately%2C%20our%20users%20are%20licenced%20via%20their%20E5%20licence.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E(Get-MsolAccountSku%20%7C%20where%20%7B%24_.AccountSkuId%20-eq%20%22hopuk%3AENTERPRISEPREMIUM%22%7D).ServiceStatus%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EThis%20just%20returns%20the%20E5%20licence%20and%20I%20can't%20go%20down%20a%20level%20into%20the%20services%20beneath%20it.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EI've%20also%20tried%20the%20following%20(kindly%20supplied%20by%20a%20colleague)%3A%3C%2FDIV%3E%3CDIV%3E%3CP%3E%23%20Connect%20to%20the%20SPO%20Service%3CBR%20%2F%3EConnect-SPOService%3CBR%20%2F%3EConnect-MsolService%3C%2FP%3E%3CP%3E%23%20The%20license%20-%20enterprise%20pack%20is%20E3%20and%20enterprise%20premium%20is%20E5%3CBR%20%2F%3E%24AccountSkuId%20%3D%20%22%3CMYDOM%3EENTERPRISEPREMIUM%22%3C%2FMYDOM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20The%20service%20that%20you%20want%20the%20script%20to%20check%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fenterprise-users%2Flicensing-service-plan-reference%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fenterprise-users%2Flicensing-service-plan-reference%3C%2FA%3E%3CBR%20%2F%3E%24Lserivce%20%3D%20%22BI_AZURE_P2%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20Get%20all%20the%20users%20in%20the%20tenant%2C%20this%20query%20takes%201%20to%202%20minutes%3CBR%20%2F%3E%24allusers%20%3D%20Get-MsolUser%20-MaxResults%2020000%20%7C%20Where-Object%20%7B%20%24_.isLicensed%20-eq%20%22TRUE%22%20%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20Set%20counter%20up%3CBR%20%2F%3E%24number%20%3D%200%3CBR%20%2F%3E%24numberofusers%20%3D%20%24allusers.count%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%23%20For%20each%20user%20found%3CBR%20%2F%3Eforeach%20(%24user%20in%20%24allusers.UserPrincipalName)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20Add%201%20to%20counter%20after%20each%20user%3CBR%20%2F%3E%24number%2B%2B%3CBR%20%2F%3Ewrite%20%22Processing%20%24number%20%5C%20%24numberofusers%20%24(%24user)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20Get%20the%20license%20details%3CBR%20%2F%3E%24licensedetails%20%3D%20(Get-MsolUser%20-UserPrincipalName%20%24user%20).Licenses%20%7C%20Where%20%7B%24_.AccountSkuId%20-eq%20%24AccountSkuId%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%20Get%20all%20the%20services%20in%20the%20license%20for%20that%20user%2C%20only%20return%20the%20services%20that%20have%20been%20activated%3CBR%20%2F%3E%24service%20%3D%20%24licensedetails.ServiceStatus%20%7C%20Where%20%7B%24_.ProvisioningStatus%20-eq%20%22success%22%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%23%20Get%20just%20the%20names%20of%20the%20services%20for%20the%20user%20stripping%20everything%20else%20out%3CBR%20%2F%3E%24results%20%3D%20%24service.serviceplan.servicename%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%23%20If%20the%20service%20is%20found%20in%20the%20list%20of%20services%20do%20something%3CBR%20%2F%3Eif(%24results%20-match%20%24Lserivce)%3CBR%20%2F%3E%3CBR%20%2F%3E%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewrite-host%20%24user%20-ForegroundColor%20green%3CBR%20%2F%3E%3CBR%20%2F%3E%23export%20the%20name%20to%20the%20CSV%20file%3CBR%20%2F%3E%24user%20%7C%20Export-Csv%20-Path%20%22C%3A%5Cusers%5Clicencesexport1.csv%22%20-Append%20-force%3CBR%20%2F%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20site%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fenterprise-users%2Flicensing-service-plan-reference%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fenterprise-users%2Flicensing-service-plan-reference%3C%2FA%3E%26nbsp%3Bseems%20to%20suggest%20the%20service%20should%20just%20be%20called%20Power%20BI%20Pro%20but%20that%20returned%20an%20empty%20CSV%20which%20took%20nearly%20an%20hour%20to%20run.%20I%20can%20see%20there%20is%20a%20GUID%20included%20in%20that%20page%20too%20but%20none%20of%20the%20cmdlets%20I've%20tried%20have%20seemed%20to%20allow%20me%20to%20search%20for%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20greatly%20appreciated%20before%20the%20laptop%20goes%20out%20the%20window...!%3C%2FP%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1850316%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ee5%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Elicences%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%20Pro%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

Hi - I am trying to use Powershell to return a report of all users who have the Power BI pro option enabled under office 365 E5.

 

To clarify, this is not the Power BI Pro licence that can be purchased separately, our users are licenced via their E5 licence.

 

I have tried the following:

 

(Get-MsolAccountSku | where {$_.AccountSkuId -eq "hopuk:ENTERPRISEPREMIUM"}).ServiceStatus
 
This just returns the E5 licence and I can't go down a level into the services beneath it.
 
I've also tried the following (kindly supplied by a colleague):

# Connect to the SPO Service
Connect-SPOService
Connect-MsolService

# The license - enterprise pack is E3 and enterprise premium is E5
$AccountSkuId = "<mydom>ENTERPRISEPREMIUM"

 

# The service that you want the script to check - https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-refe...
$Lserivce = "BI_AZURE_P2"

 

# Get all the users in the tenant, this query takes 1 to 2 minutes
$allusers = Get-MsolUser -MaxResults 20000 | Where-Object { $_.isLicensed -eq "TRUE" }

 

# Set counter up
$number = 0
$numberofusers = $allusers.count

 


# For each user found
foreach ($user in $allusers.UserPrincipalName)

 

{

 

# Add 1 to counter after each user
$number++
write "Processing $number \ $numberofusers $($user)"

 

# Get the license details
$licensedetails = (Get-MsolUser -UserPrincipalName $user ).Licenses | Where {$_.AccountSkuId -eq $AccountSkuId}

 

 

# Get all the services in the license for that user, only return the services that have been activated
$service = $licensedetails.ServiceStatus | Where {$_.ProvisioningStatus -eq "success"}

 


# Get just the names of the services for the user stripping everything else out
$results = $service.serviceplan.servicename

 


# If the service is found in the list of services do something
if($results -match $Lserivce)

{

 

write-host $user -ForegroundColor green

#export the name to the CSV file
$user | Export-Csv -Path "C:\users\licencesexport1.csv" -Append -force
}

 

}

 

This site https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-refe... seems to suggest the service should just be called Power BI Pro but that returned an empty CSV which took nearly an hour to run. I can see there is a GUID included in that page too but none of the cmdlets I've tried have seemed to allow me to search for that.

 

Any help greatly appreciated before the laptop goes out the window...!

1 Reply

To list all users who have a given service plan enabled, use this:

 

Get-MsolUser | ? {$_.Licenses.ServiceStatus | ? {$_.ServicePlan.ServiceName -eq "BI_AZURE_P2" -and $_.ProvisioningStatus -eq "Success"}}

 

If you want to further narrow down by license type, just add another clause:

 

Get-MsolUser | ? {($_.Licenses.AccountSkuId -eq "tenant:ENTERPRISEPREMIUM") -and ($_.Licenses.ServiceStatus | ? {$_.ServicePlan.ServiceName -eq "BI_AZURE_P2" -and $_.ProvisioningStatus -eq "Success"})}