Exporting Microsoft 365 Licence Data for the ENow License Cost Report

Purpose

This KB explains how to use Microsoft Graph PowerShell to export Microsoft 365 license information into the license summary for the license cost section of the ENow Dashboard. The script connects to Microsoft Graph, enumerates all subscribed SKUs, calculates unassigned license counts, detects direct assignments vs. group-based licensing, and outputs the exact columns required by ENow. The logic is based on Get-MgSubscribedSku for subscription inventory and LicenseAssignmentStates on user objects to distinguish direct from group-based assignments.

Prerequisites

1. PowerShell version
   • Windows PowerShell 5.1 or PowerShell 7+ on Windows Server/Windows 10/11.
2. Microsoft Graph PowerShell modules
   Install the Graph modules (once per machine):

Install-Module Microsoft.Graph -Scope CurrentUser

The script specifically requires:

• Microsoft.Graph.Authentication
• Microsoft.Graph.Users
• Microsoft.Graph.Groups
• Microsoft.Graph.Identity.DirectoryManagement

1. Permissions / roles
   The account running the script must have sufficient directory rights to read license and group information, for example:
   • Global Reader, Reports Reader, or higher (Global Admin, License Admin, etc., depending on your tenant policy).
   • When prompted, Graph will request delegated scopes:
     • Organization.Read.All
     • Directory.Read.All
     • Group.Read.All
     • User.Read.All
2. Network access
   • Outbound HTTPS to graph.microsoft.com and login.microsoftonline.com.

What the script does

At a high level, the script:

1. Connects to Microsoft Graph using the Graph PowerShell SDK.
2. Retrieves all subscribed SKUs in the tenant with Get-MgSubscribedSku to know how many licenses are enabled and consumed for each product.
3. Retrieves all groups that have license assignments (AssignedLicenses) and builds a map of SKU → list of groups assigning that SKU.
4. Retrieves all users, including AssignedLicenses and LicenseAssignmentStates, and from that:
   • Uses the licenseAssignmentState.assignedByGroup property to detect whether a license is direct or inherited from a group (if assignedByGroup is null, the license is directly assigned).
   • Tallies a DirectAssignments count per SKU.
5. For each subscribed SKU, calculates:
   • TotalUnassigned = Enabled licenses (prepaid units) − Consumed units, with a floor of 0.
6. Builds a CSV row with the exact ENow columns:
   • LicenseSkuName → SKU part number (e.g. ENTERPRISEPACK).
   • License → same SKU part number (used as key).
   • Cost → set to 0 (can be filled later from a separate cost table).
   • DefaultCost → set to 0.
   • Groups → semicolon-delimited list of group display names that assign that SKU.
   • ExtensionAttribute, ExtensionAttributeValue, ExtensionAttributeDelimiter → blank placeholders.
   • DirectAssignments → number of users with a direct assignment of that SKU.
   • TotalUnassigned → remaining pool of unassigned licenses for that SKU.
7. Exports the result to ENow_License_Cost_CSV_Import.csv in the current directory.

Save the following script as Export-ENowLicenseCostCsv.ps1:

#Requires -Modules Microsoft.Graph.Authentication, Microsoft.Graph.Users, Microsoft.Graph.Groups, Microsoft.Graph.Identity.DirectoryManagement

param(
    [string]$OutputCsv = ".\ENow_License_Cost_CSV_Import.csv"
)

$ErrorActionPreference = "Stop"

$requiredScopes = @(
    "Organization.Read.All",
    "Directory.Read.All",
    "Group.Read.All",
    "User.Read.All"
)

function Connect-GraphIfNeeded {
    $ctx = Get-MgContext -ErrorAction SilentlyContinue
    if (-not $ctx) {
        Connect-MgGraph -Scopes $requiredScopes -NoWelcome | Out-Null
    }
}

try {
    Connect-GraphIfNeeded

    # 1. Get all subscribed SKUs (license products) in the tenant
    $subscribedSkus = Get-MgSubscribedSku -All

    # 2. Get all groups that have assigned licenses
    $groups = Get-MgGroup -All -Property Id,DisplayName,AssignedLicenses |
        Where-Object { $_.AssignedLicenses -ne $null }

    # 3. Get all users, including license assignment states
    $users = Get-MgUser -All -Property Id,AssignedLicenses,LicenseAssignmentStates

    # Build SKU -> Groups map
    $skuToGroups = @{}
    foreach ($group in $groups) {
        foreach ($assignedLicense in $group.AssignedLicenses) {
            $skuKey = [string]$assignedLicense.SkuId

            if (-not $skuToGroups.ContainsKey($skuKey)) {
                $skuToGroups[$skuKey] = [System.Collections.Generic.List[string]]::new()
            }

            if (-not [string]::IsNullOrWhiteSpace($group.DisplayName)) {
                $skuToGroups[$skuKey].Add($group.DisplayName)
            }
        }
    }

    # Build direct assignment counters per SKU
    $directAssignmentCountBySku = @{}

    foreach ($user in $users) {
        $seenDirectSkus = [System.Collections.Generic.HashSet[string]]::new()

        if ($user.LicenseAssignmentStates) {
            foreach ($state in $user.LicenseAssignmentStates) {
                $skuKey = [string]$state.SkuId

                # If assignedByGroup is empty, this is a direct assignment
                if ([string]::IsNullOrWhiteSpace([string]$state.AssignedByGroup)) {
                    [void]$seenDirectSkus.Add($skuKey)
                }
            }
        }

        foreach ($skuKey in $seenDirectSkus) {
            if (-not $directAssignmentCountBySku.ContainsKey($skuKey)) {
                $directAssignmentCountBySku[$skuKey] = 0
            }
            $directAssignmentCountBySku[$skuKey]++
        }
    }

    # Build final rows
    $results = foreach ($sku in $subscribedSkus) {
        $skuKey = [string]$sku.SkuId

        # Enabled (prepaid) vs consumed units
        $enabledUnits = 0
        if ($sku.PrepaidUnits -and $null -ne $sku.PrepaidUnits.Enabled) {
            $enabledUnits = [int]$sku.PrepaidUnits.Enabled
        }

        $consumedUnits = 0
        if ($null -ne $sku.ConsumedUnits) {
            $consumedUnits = [int]$sku.ConsumedUnits
        }

        $totalUnassigned = $enabledUnits - $consumedUnits
        if ($totalUnassigned -lt 0) {
            $totalUnassigned = 0
        }

        # Groups that assign this SKU
        $groupNames = ""
        if ($skuToGroups.ContainsKey($skuKey)) {
            $groupNames = ($skuToGroups[$skuKey] | Sort-Object -Unique) -join ";"
        }

        # Direct user assignments
        $directAssignments = 0
        if ($directAssignmentCountBySku.ContainsKey($skuKey)) {
            $directAssignments = [int]$directAssignmentCountBySku[$skuKey]
        }

        [PSCustomObject][ordered]@{
            LicenseSkuName              = [string]$sku.SkuPartNumber
            License                     = [string]$sku.SkuPartNumber
            Cost                        = 0
            DefaultCost                 = 0
            Groups                      = $groupNames
            ExtensionAttribute          = ""
            ExtensionAttributeValue     = ""
            ExtensionAttributeDelimiter = ""
            DirectAssignments           = $directAssignments
            TotalUnassigned             = $totalUnassigned
        }
    }

    # Export with exact ENow CSV schema
    $results |
        Select-Object LicenseSkuName,License,Cost,DefaultCost,Groups,ExtensionAttribute,ExtensionAttributeValue,ExtensionAttributeDelimiter,DirectAssignments,TotalUnassigned |
        Export-Csv -Path $OutputCsv -NoTypeInformation -Encoding UTF8

    Write-Host "Exported to $OutputCsv"
}
finally {
    Disconnect-MgGraph -ErrorAction SilentlyContinue | Out-Null
}

How to run the script

1. Open PowerShell as your admin account
   • Run PowerShell as a user that has the required directory permissions.
2. Sign in to Microsoft Graph
   1. On first run, a sign-in window will appear.
   2. Sign in with your admin account and consent to the requested Graph scopes (Organization.Read.All, Directory.Read.All, Group.Read.All, User.Read.All)
3. After completion, the script displays:

Exported to .\ENow_License_Cost_CSV_Import.csv

To write the CSV somewhere other than the current directory, pass -OutputCsv:

.\Export-ENowLicenseCostCsv.ps1 -OutputCsv "C:\Temp\ENow_License_Cost_CSV_Import.csv"

CSV column description

The use of licenseAssignmentState.assignedByGroup as a discriminator for direct vs. group-based user assignments follows Microsoft's documented behavior: when assignedByGroup is null, the license is directly assigned to the user; otherwise, it is inherited from the referenced group.

Microsoft reference

• licenseAssignmentState resource type (Microsoft Learn)
• subscribedSku resource type (Microsoft Learn)