Create CSV files for SKUs and service plans

Connect-MgGraph -Scope Directory.Read.All -NoWelcome

Connect-MgGraph -Scope Directory.Read.All -NoWelcome
 
# Define the output file name for the CSBV file generated by the script
$OutputServicePlanFile = "c:\Temp\ServicePlanDataComplete.csv"
$OutputSKUfile = "c:\Temp\SkuDataComplete.csv"
 
# Download the file from Microsoft
$DownloadFileURL = 'https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv'
$LocalFilePath = ((New-Object -ComObject Shell.Application).Namespace('shell:Downloads').Self.Path) + "\ProductIdentifiers.csv"
Invoke-WebRequest -Uri $DownloadFileURL -OutFile $LocalFilePath
 
If (Test-Path -Path $LocalFilePath) {
    Write-Host "Entra ID Prpduct Identifiers file downloaded successfully to $LocalFilePath"
} else {
    Write-Host "Failed to download the file. Script exiting"
    break
}
 
#Import the Product names and service plan identifiers for licensing CSV file downloaded from https://learn.microsoft.com/en-us/entra/identity/users/licensing-service-plan-reference
# Remember to move the CSV file downloaded from Microsoft to c:\temp\
[array]$Identifiers = Import-Csv -Path $LocalFilePath
#select all SKUs with friendly display name
[array]$SKU_friendly = $identifiers | Select-Object GUID, String_Id, Product_Display_Name -Unique
#select the service plans with friendly display name
[array]$SP_friendly = $identifiers | Select-Object Service_Plan_Id, Service_Plan_Name, Service_Plans_Included_Friendly_Names -Unique
 
# Get prpduct subscriptions used in tenant
[Array]$Skus = Get-MgSubscribedSku
 
# Generate CSV of all product SKUs used in tenant
$Skus | Select-Object SkuId, SkuPartNumber, @{Name = "DisplayName"; Expression = { ($SKU_friendly | Where-object -Property GUID -eq $_.SkuId).Product_Display_Name } } `
    | Export-Csv -NoTypeInformation $OutputSKUfile
# Generate list of all service plans used in SKUs in tenant
$SPData = [System.Collections.Generic.List[Object]]::new()
ForEach ($S in $Skus) {
    ForEach ($SP in $S.ServicePlans) {
        $SPLine = [PSCustomObject][Ordered]@{
            ServicePlanId          = $SP.ServicePlanId
            ServicePlanName        = $SP.ServicePlanName
            #use 'Service_Plans_Included_Friendly_Names' from $SKU_friendly for 'ServicePlanDisplayName'
            ServicePlanDisplayName = ($SP_friendly | Where-Object { $_.Service_Plan_Id -eq $SP.ServicePlanId }).Service_Plans_Included_Friendly_Names | Select-Object -First 1
        }
        $SPData.Add($SPLine)
    }
}
$SPData | Sort-Object ServicePlanId -Unique | Export-csv $OutputServicePlanFile -NoTypeInformation
 
Write-Host ("The CSV files are now available as input for the licensing report script: {0} and {1}" -f $OutputSKUfile, $OutputServicePlanFile)