Get AzureAD license details for all users
The Story
So here’s the story. Some time ago we wanted to use the possibility to manage AzureAD licenses through Active Directory groups. We do have AzureAD P1 (which is necessary) to do this. The process is dead simple:
- Create proper AD groups on-premises
- Wait for sync (or orce it) to Azure AD
- Go to proper blade (in Azure AD) and assign groups to products
- Add people to on-premises Active Directory
- Get some coffee
Here’s the thing though - till now we were using our on-premises automation to assign licenses to people directly using MSOL commands.
It shouldn’t be hard to get all members of Microsoft Flow Free, right? Or Office 365 E3 license right? Right????
The Problem
In Intune you can easily export all devices into csv and then do whatever you wish with it. When it comes to licenses - you get first 20 people with Load more option in GUI. And then you click and click and click to get all 181 users. Then you can hit ctrl + A and ctrl + c and parse it.
Want to try with PowerShell? Well, it isn’t hard to get a SINGLE user membership.
$user = Get-AzureADUser -SearchString 'mczerniawski' | Where-Object {$_.UserType -eq 'Member'}
Get-AzureADUserLicenseDetail -ObjectId $user.objectid | Select-Object -ExpandProperty ServicePlans
AppliesTo ProvisioningStatus ServicePlanId ServicePlanName
--------- ------------------ ------------- ---------------
Company Success 6c57d4b6-3b23-47a5-9bc9-69f17b4947b3 RMS_S_PREMIUM
Company Success bea4c11e-220a-4e6d-8eb8-8ea15d019f90 RMS_S_ENTERPRISE
Company Success 41781fb2-bc02-4b7c-bd55-b576c07bb09d AAD_PREMIUM
...
Company Success 76846ad7-7776-4c40-a281-a386362dd1b9 FLOW_O365_P2
Company Success c68f8d98-5534-41c8-bf36-22fa496fa792 POWERAPPS_O365_P2
...
Company Success 2049e525-b859-401b-b2a0-e0a31c4b1fe4 BI_AZURE_P0
But it ain’t that simple to get all users of a single group/license assignment!
There is no cmdlet for this!
At least as of now - 25.01.2019
I haven’t tried with Mirosoft Graph yet, so I decided to go straight PowerShell.
The Resolution
My apporach was to:
- get all users from Azure AD
- get their license information
- return some proper object
So I’ve run this:
Import-Module azuread
Connect-AzureAD
$allusers = Get-AzureADUser -All $true
$AllUserDetails = foreach ($user in $allusers){
$licenseDetails = Get-AzureADUserLicenseDetail -ObjectId $user.objectid | Select-Object -ExpandProperty ServicePlans
$License = foreach ($licenseRow in $licenseDetails) {
@{
ServicePlanName = $licenseRow.ServicePlanName
ProvisioningStatus = $licenseRow.ProvisioningStatus
AppliesTo = $licenseRow.AppliesTo
ServicePlanId = $licenseRow.ServicePlanId
}
}
[pscustomObject]@{
ObjectID = $user.ObjectID
DisplayName = $user.DisplayName
UserPrincipalName =$user.UserPrincipalName
ObjectType = $user.ObjectType
UserType = $user.UserType
AccountEnabled =$user.AccountEnabled
AssignedPlans = $user.AssignedPlans
LastDirSyncTime = $user.LastDirSyncTime
AssignedLicense = $License
}
}
$AllUserDetails | ConvertTo-Json -Depth 99 | Out-File $ExportPath
But it took ages (like 10 minutes and I got suspicious) to execute. So I’ve added a little bit of verbosity. Also I wanted to have a better naming than BPOS_S_TODO_2 
It took a bit of time (as Microsoft is adding/renaming a whole lot) but I managed to get a list of names for each SKU (this is a csv list):
SKUID;SKUName
BI_AZURE_P0;Power BI (free)
BI_AZURE_P2;Power BI Pro
WIN10_PRO_ENT_SUB;Windows 10 Enterprise E5
INTUNE_O365;Intune
VISIOONLINE;Visio Online Plan 2
POWERFLOWSFREE;PowerApps and Logic Flows
PROJECTWORKMANAGEMENT;Office 365 Enterprise E3
EXCHANGE_S_ENTERPRISE;Office 365 Enterprise E3
BPOS_S_TODO_2;Office 365 Enterprise E3
FORMS_PLAN_E3;Office 365 Enterprise E3
YAMMER_ENTERPRISE;Office 365 Enterprise E3
MCOSTANDARD;Office 365 Enterprise E3
FLOW_O365_P2;Office 365 Enterprise E3
SWAY;Office 365 Enterprise E3
TEAMS1;Office 365 Enterprise E3
OFFICESUBSCRIPTION;Office 365 Enterprise E3
STREAM_O365_E3;Office 365 Enterprise E3
Deskless;Office 365 Enterprise E3
POWERAPPS_O365_P2;Office 365 Enterprise E3
Microsoft Stream;Microsoft Stream Trial
DYN365_ENTERPRISE_P1;Dynamics 365 Customer Engagement Plan
POWERAPPS_P2_VIRAL;Microsoft PowerApps Plan 2 Trial (d5368ca3-357e-4acb-9c21-8495fb025d1f)
FLOW_P2_VIRAL_REAL;Microsoft PowerApps Plan 2 Trial (d20bfa21-e9ae-43fc-93c2-20783f0840c3)
FLOW_P2_VIRAL;Microsoft Flow Free (50e68c76-46c6-4674-81f9-75456511b170)
DYN365_CDS_VIRAL;Microsoft Flow Free (17ab22cd-a0b3-4536-910a-cb6eb12696c0)
EXCHANGE_S_STANDARD;Exchange Online (Plan 1)
AAD_PREMIUM;Enterprise Mobility + Security E3
ADALLOM_S_DISCOVERY;Enterprise Mobility + Security E3
RMS_S_PREMIUM;Enterprise Mobility + Security E3
MFA_PREMIUM;Enterprise Mobility + Security E3
INTUNE_A;Enterprise Mobility + Security E3
Dynamics_365_Hiring_Free_PLAN;Dynamics 365 for Talent
DYN365_CDS_DYN_APPS;Dynamics 365 for Talent
POWERAPPS_DYN_APPS;Dynamics 365 for Talent
Dynamics_365_for_HCM_Trial;Dynamics 365 for Talent
FLOW_DYN_APPS;Dynamics 365 for Talent
Dynamics_365_Onboarding_Free_PLAN;Dynamics 365 for Talent
DYN365_BUSINESS_Marketing;Dynamics 365 for Marketing
MCOMEETADV;Audio Conferencing
SPZA;AppConnect
AAD_BASIC;Azure Active Directory Basic
POWERVIDEOSFREE;PowerApps and Logic Flows
POWERFLOWSFREE;PowerApps and Logic Flows
POWERAPPSFREE;PowerApps and Logic Flows
PROJECT_PROFESSIONAL;Project Online Professional
SHAREPOINT_PROJECT;Project Online Professional
PROJECT_CLIENT_SUBSCRIPTION;Project Online Professional
Final Round
Now, the final code look somewhat like this:
Import-Module azuread
Connect-AzureAD
$currentDate = Get-Date -Format yyyyMMdd_HHmmss
$LicenseSKU = Import-Csv -Path <Path to csv file with SKU>
$JSONExportFileName = 'users_licenses_{0}.json' -f $currentDate
$ExportPath= Join-Path .\ -ChildPath $JSONExportFileName
$counter = 1
$allusers = Get-AzureADUser -All $true
$AllUserDetails = foreach ($user in $allusers){
$percentComplete = $counter / $allusers.count * 100
$Activity = @{
Activity = "Processing user {$counter} of $($allusers.count)"
Status = "Processing user {$($User.DisplayName)}"
PercentComplete= $percentComplete
CurrentOperation = 'Getting user license detail from AzureAD'
}
Write-Progress @Activity
#get license details
$licenseDetails = Get-AzureADUserLicenseDetail -ObjectId $user.objectid | Select-Object -ExpandProperty ServicePlans
$License = foreach ($licenseRow in $licenseDetails) {
@{
ServicePlanName = $licenseRow.ServicePlanName
FullServicePlanName = $LicenseSKU | Where-Object {$PSItem.SKUID -eq $licenseRow.ServicePlanName} | Select-Object -ExpandProperty SKUName
ProvisioningStatus = $licenseRow.ProvisioningStatus
AppliesTo = $licenseRow.AppliesTo
ServicePlanId = $licenseRow.ServicePlanId
}
}
[pscustomObject]@{
ObjectID = $user.ObjectID
DisplayName = $user.DisplayName
UserPrincipalName =$user.UserPrincipalName
ObjectType = $user.ObjectType
UserType = $user.UserType
AccountEnabled =$user.AccountEnabled
AssignedPlans = $user.AssignedPlans
LastDirSyncTime = $user.LastDirSyncTime
AssignedLicense = $License
}
$counter++
}
$AllUserDetails | ConvertTo-Json -Depth 99 | Out-File $ExportPath
For my domain (around 5,5k User objects) it takes about 30 minutes to complete. I’m storing the result in JSON for further processing and historical information.
The Report
Now that I have all users and their licenses I can get GROUPS with MEMBERS!
$result = @{}
foreach ($row in $AllUserDetails) {
foreach ($al in $row.assignedLicense) {
if ($al.ServicePlanName) {
$sku = $al.ServicePlanName
if($result.ContainsKey($sku)) {
$currentUsers = $result[$sku]
$newValue = @($currentUsers) + @([pscustomobject]@{
userPrincipalName = $row.UserPrincipalName
DisplayName = $row.DisplayName
}
)
$result[$sku] = $newValue
}
else {
$result[$sku] = [pscustomobject]@{
userPrincipalName = $row.UserPrincipalName
DisplayName = $row.DisplayName
}
}
}
}
}
$result
This nasty bit of code does a nasty thing:
- it goes through each row
- if SKU (the all-saying Abbreviation - like
POWERAPPS_P2_VIRAL) is found for the first time - it’s added to a hashtable where key is the SKU and value is an object with userPrincipalName and DisplaName - if SKU was already found before - new value with userPrincipalName and DisplayName are added to currenty key
The Holy Grail
NOW, I’m finally able to get what I wanted - all users for given group - and add them to my on-premises Active Directory group!
$GroupName='VISIOONLINE'
$users = $result[$GroupName] | Select-Object -ExpandProperty userprincipalname
$ADGroupName = 'Cloud_Visio_Plan2'
foreach ($user in $users) {
$usertest = Get-ADuser -Filter {userPrincipalName -eq $user}
Write-Host "Processing user {$user} - $($usertest.samaccountname)"
Add-ADGroupMember -Identity $ADGroupName -Members $usertest
}
It wasn’t that hard right? Right?…
The Next Step
The next step is to achieve the same thing with Grap API. MAYBE it will be easier!
Leave a comment