Assigned Microsoft 365 licenses and services per OU

The script generates a report containing OUs directly located in a domain with the number of Microsoft 365 licenses and services assigned to users in the OUs. To execute the script, create a report with corresponding custom columns. The domain for report generation is selected in a parameter.

Parameters:

$domainParameterName - Specifies the name of the AD object picker parameter used to select the domain for report generation with the param- prefix.
$licenseSkuToColumnID - Maps SKU part numbers of Microsoft 365 licenses with identifiers of the custom columns that will store the number of licenses (sum of assigned). The columns should be of the Number type. To get the identifier of a custom column:
1. In the Report-specific columns section, on the Columns tab, right-click the custom column.
2. In the context menu, navigate to Copy and click Column ID.
3. The column identifier will be copied to clipboard.
$serviceSkuToColumnID - Maps SKU part numbers of Microsoft 365 services with identifiers of the custom columns that will store the number of services(sum of assigned). The columns should be of the Number type.

Edit Remove

PowerShell

$domainParameterName = "param-Domain" # TODO: modify me
$licenseSkuToColumnID = @{
    "DEVELOPERPACK_E5" = "{58efe397-609b-474a-84a2-baa3649d59f1}"
    "POWERAPPS_PER_USER" = "{14c92962-2514-4083-aa8e-83874db65a0c}"
} # TODO: modify me
$serviceSkuToColumnID = @{
    "SHAREPOINTENTERPRISE" = "{cdac6aa7-2f94-420b-ab0a-171f9472e82b}"
    "POWERAPPS_PER_USER" = "{909386b3-d66d-4bad-b846-465fce609870}"
} # TODO: modify me

# Connect to AzureAD
$domainDN = $Context.GetParameterValue($domainParameterName)
$domain = $Context.BindToObjectByDN($domainDN)
$token = $Context.CloudServices.GetAzureAuthAccessToken($domain, "https://graph.windows.net/")
$tenant = $Context.CloudServices.GetO365Tenant($domain)
$credential = $tenant.GetCredential()
try
{
    Connect-AzureAD -AccountId $credential.AppId -AadAccessToken $token -TenantId $tenant.TenantId
    
    # Get license info
    $licensesInfos = @{}
    $subscribedSku = Get-AzureADSubscribedSku
    $subscribedSku | %%{$licensesInfos.Add($_.SkuId, @{SkuPartNumber = $_.SkuPartNumber; ServicePlans = $_.ServicePlans})}
    
    # Get users
    $licensedUsers = Get-AzureADUser -All:$True | where {($_.AssignedLicenses.Count -ne 0) -and ($NULL -ne $_.ImmutableId)}
    $userGuidToLicenses = @{}
    $guidsBytesToSearch = New-Object System.Collections.ArrayList
    foreach ($user in $licensedUsers)
    {
        $licenseSkuPartNumbers = New-Object System.Collections.ArrayList
        $servicesSkuPartNumbers = New-Object System.Collections.Generic.HashSet[System.String]
        foreach ($assignedLicense in $user.AssignedLicenses)
        {
            $licenseInfo = $licensesInfos[$assignedLicense.SkuId]
            if ($licenseSkuToColumnID.ContainsKey($licenseInfo.SkuPartNumber))
            {
                $licenseSkuPartNumbers.Add($licenseInfo.SkuPartNumber)
            }
            
            foreach ($service in $licenseInfo.ServicePlans)
            {
                $serviceName = $service.ServicePlanName
                if (!$serviceSkuToColumnID.ContainsKey($serviceName) -or 
                    $assignedLicense.DisabledPlans.Contains($service.ServicePlanId))
                {
                    continue
                }
                
                $servicesSkuPartNumbers.Add($serviceName)
            }
        }
        
        try
        {
            $guidBytes = [System.Convert]::FromBase64String($user.ImmutableId)
        }
        catch
        {
            $Context.LogMessage("An error occurred while parsing immutable ID '$($user.ImmutableId)' for user '$($user.UserPrincipalName)'. Error: " + $_.Exception.Message, "Warning")
            continue
        }
        $userGuidToLicenses.Add([Guid]$guidBytes, @{Licenses = $licenseSkuPartNumbers; Services = $servicesSkuPartNumbers})
        $guidsBytesToSearch.Add($guidBytes)
    }
    
    # Search users
    $searcher = $Context.CreateGuidBasedSearcher($guidsBytesToSearch)
    $searcher.BaseObjectPath = $domainDN
    $searcher.VirtualRoot = $False
    $ouDNToColumns = @{}
    $columndIDToValue = @{}
    $licenseSkuToColumnID.Values | %%{$columndIDToValue.Add($_, 0)}
    $serviceSkuToColumnID.Values | %%{$columndIDToValue.Add($_, 0)}
    try
    {
        $searchIterator = $searcher.ExecuteSearch()
        while ($Context.MoveNext($searchIterator))
        {
            # Get parent
            $searchResult = $searchIterator.Current
            $dnObject = New-Object Softerra.Adaxes.Ldap.DN $searchResult.Properties["distinguishedName"].Value
            $parentDN = $dnObject.Parent
            
            while (-not (([String]::IsNullOrEmpty($parentDN.Parent.Leaf.Type)) -or
                ($parentDN.Parent.Leaf.Type -ieq "DC")))
            {
                $parentDN = $parentDN.Parent
            }
            
            $parentDNString = $parentDN.ToString()
            if (!$ouDNToColumns.ContainsKey($parentDNString))
            {
                $ouDNToColumns.Add($parentDNString, $columndIDToValue.Clone())
            }
            
            # Count licenses
            $guid = [Guid]$searchResult.Properties["objectGUID"].Value
            $enabledLicense = $userGuidToLicenses[$guid].Licenses
            foreach ($skuPartNumber in $enabledLicense)
            {
                $columndID = $licenseSkuToColumnID[$skuPartNumber]
                $ouDNToColumns[$parentDNString][$columndID]++
            }
            
            # Count services
            $enabledServices = $userGuidToLicenses[$guid].Services
            foreach ($skuPartNumber in $enabledServices)
            {
                $columndID = $serviceSkuToColumnID[$skuPartNumber]
                $ouDNToColumns[$parentDNString][$columndID]++
            }
        }
    }
    finally
    {
        if ($searchIterator) { $searchIterator.Dispose() }
    }
    
    foreach ($dn in $ouDNToColumns.Keys)
    {
        # Add ou to report
        $ou = $Context.BindToObjectByDN($dn)
        $Context.Items.Add($ou, $ouDNToColumns[$dn], $NULL)
    }
}
finally
{
    Disconnect-AzureAD
}

Comments 2

Quentin Nov 28, 2023

Hello
Is there a way to use this script on Sub-OU and not only on root OU ?
My users OU are not located in "DC=domain,DC=lan" but under "OU=company,DC=domain,DC=lan", this script don't work in this case, only count licence affected on company OU
Thanks

Support Nov 28, 2023

Hello Quentin,

As an option, you can just select the OU as value for the corresponding parameter. It should then give you the OUs located directly under the selected one.

Script repository

Parameters:

Got questions?