Script to download user license assignment information to CSVs

[merill]

Save the script (e.g. Get-GraphUserLicense.ps1) and run it to create the download.

If you are using Graph PowerShell for the first time you might need to install and import the modules (see commented lines below).

<#
.SYNOPSIS
    Generates tenant-wide user license assignment report by creating one Excel-sheet (CSV) per user license type.
#>

[CmdletBinding()]param()

function GetProductInfo() {
    # Get the latest product names. If you wish to avoid downloading the csv, download the csv manually and comment out the Invoke-WebRequest line.
    # CSV Source: https://learn.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference
    Write-Verbose 'Getting latest Microsoft product names and service plan identifiers'
    $m365licenseInfoCsvPath = Join-Path $licenseReportPath 'M365LicenseInfo.csv'
    $ProgressPreference = 'SilentlyContinue' 
    Invoke-WebRequest -Uri 'https://raw.githubusercontent.com/merill/license/main/license.csv' -OutFile $m365licenseInfoCsvPath

    $m365licenseInfoCsv = Import-Csv $m365licenseInfoCsvPath

    foreach ($line in $m365licenseInfoCsv) {
        if (!$productNames.ContainsKey($line.GUID)) {
            $productNames[$line.GUID] = $line.Product_Display_Name
        }
        $planKey = $line.GUID + $line.Service_Plan_Id
        if (!$planNames.ContainsKey($planKey)) {
            $planNames[$planKey] = $line.Service_Plans_Included_Friendly_Names + '(' + $line.Service_Plan_Name + ')'
        }
    }
}

function GetLicenseTypesInTenant {
    # Get a list of all licences that exist within the tenant
    Write-Verbose 'Getting license types.'
    $licenseTypes = Get-MgSubscribedSku | `
        Select-Object -Property SkuId, SkuPartNumber, ConsumedUnits, ServicePlans -ExpandProperty PrepaidUnits | `
        Where-Object { $_.ConsumedUnits -ge 1 }
    $licenseTypeSummaryReportPath = Join-Path $licenseReportPath ($date + '__LicenseSummary.csv')

    $licenseTypeSummary = @()
    foreach ($item in $licenseTypes) {
        $itemInfo = [ordered]@{
            SkuId         = $item.SkuId
            SkuPartNumber = $item.SkuPartNumber
            Name          = GetProductDisplayName $item.SkuId
            Total         = $item.Enabled
            Assigned      = $item.ConsumedUnits
            Available     = ($item.Enabled - $item.ConsumedUnits)
        } 
        $licenseTypeSummary += $itemInfo
    }
    $licenseTypeSummary | Export-Csv $licenseTypeSummaryReportPath

    return $licenseTypes
}

function GetProductDisplayName($skuId) {
    if ($productNames.ContainsKey($skuId)) {
        return $productNames[$skuId]
    }
    else {
        #License Info file might be out of date, couldn't find new product
        return $skuId
    }
}

function GetServicePlanDisplayName($skuId, $servicePlanId, $servicePlanName) {
    $planKey = $skuId + $servicePlanId
    if ($planNames.ContainsKey($planKey)) {
        return $planNames[$planKey]
    }
    else {
        #License Info file might be out of date, couldn't find new product/plan
        return $servicePlanName
    }
}

#Import-Module Microsoft.Graph.Users
#Import-Module Microsoft.Graph.Identity.DirectoryManagement
Connect-MgGraph -Scopes Directory.Read.All

Write-Verbose ('START TIME: ' + $ScriptStart)

$licenseReportPath = '.\'
$date = Get-Date -Format 'yyyyMMdd'
$ScriptStart = (Get-Date)
$productNames = @{}
$planNames = @{}

$m365licenseInfoCsv = GetProductInfo

$licenseTypes = GetLicenseTypesInTenant

Write-Verbose 'Getting all users with licenses assigned (this may take a while).'
$licensedUsers = Get-MgUser -Filter 'assignedLicenses/$count ne 0' -PageSize 999 `
    -ConsistencyLevel eventual -CountVariable licensedUserCount `
    -All -Select UserPrincipalName, DisplayName, JobTitle, Office, AssignedLicenses, AssignedPlans

foreach ($licenseType in $licenseTypes) {    
    $userLicenseSummaryReportPath = Join-Path $licenseReportPath ($date + '_' + $licenseType.SkuPartNumber + '.csv')
    Write-Verbose "Generating $userLicenseSummaryReportPath"

    $users = $licensedUsers | Where-Object { $_.AssignedLicenses.SkuId -contains $licenseType.SkuId }

    $userLicenseSummary = @()
    foreach ($user in $users) {
        $itemInfo = [ordered]@{
            Date              = $date
            UserPrincipalName = $user.UserPrincipalName
            DisplayName       = $user.DisplayName + ''
            JobTitle          = $user.JobTitle + ''
            Office            = $user.Office + ''
            License           = $licenseType.SkuPartNumber
            LicenseName       = GetProductDisplayName $licenseType.SkuId
        }
        $userLicense = $user.AssignedLicenses | Where-Object { $_.SkuId -eq $licenseType.SkuId }
        foreach ($plan in $licenseType.ServicePlans) {
            # Set status of disabled plans in report
            $status = $true
            if ($userLicense.DisabledPlans -contains $plan.ServicePlanId) {
                $status = $false
            }
            $spName = GetServicePlanDisplayName $licenseType.SkuId $plan.ServicePlanId $plan.ServicePlanName
            $itemInfo[$spName] = $status
        }
        $userLicenseSummary += $itemInfo
    }    
    $userLicenseSummary | Export-Csv $userLicenseSummaryReportPath
}

$ScriptEnd = (Get-Date)
$RunTime = New-Timespan -Start $ScriptStart -End $ScriptEnd
$Total = "Elapsed Time: {0}:{1}:{2}" -f $RunTime.Hours, $Runtime.Minutes, $RunTime.Seconds
Write-Verbose "END TIME: $ScriptEnd"
Write-Verbose $Total
Write-Verbose 'Script Completed.'

[devZero75]

you may want to fix the copy-past error on line 16

instead of: $m365licenseInfoCsvPath = Import-Csv $m365licenseInfoCsvPath
it should be: $m365licenseInfoCsv = Import-Csv $m365licenseInfoCsvPath

[merill]

Cheers tx. Fixed.