The M365 Licensing Audit Nobody Wants to Do

Every Microsoft 365 tenant I've assessed has been overspending on licences. Not by a little: typically 15 to 30%, sometimes more. The waste follows the same patterns every time: E5 licences on service accounts that don't need them, Copilot seats assigned to mailboxes that send three emails a month, Defender P2 entitlements on users who aren't in scope for EDR, and the perennial favourite, disabled accounts that still hold paid licences because nobody told HR to tell IT to tell the licence admin to reclaim them.

The audit isn't hard. It doesn't get done because it's tedious, it's nobody's explicit responsibility, and the M365 Admin Centre makes it just difficult enough to cross-reference usage against assignment that most people give up after an afternoon of clicking.

This post fixes that. We'll use Microsoft Graph API to pull licence inventory, per-user assignments, and workload activity data, then cross-reference them to produce a waste report you can hand to your finance team. Everything runs in PowerShell, every script is reusable, and the whole process takes about twenty minutes once you've set it up. If you're new to working with Graph API programmatically, the Microsoft Graph API for Architects post covers endpoint selection, authentication patterns, and PowerShell fundamentals for tenant assessments.

Why the admin centre isn't enough

The M365 Admin Centre shows you two things well: how many licences you've purchased and how many are assigned. It shows you almost nothing about whether those assigned licences are being used.

You can see that 500 of your 600 E5 licences are assigned. What you can't easily see is that 47 of those 500 users haven't opened Outlook in 90 days, 83 have never touched a Defender feature, and 12 are service accounts that could run on Exchange Online Plan 1 at a fraction of the cost.

The Admin Centre also doesn't help with the structural questions that matter for cost optimisation:

• Which users are on E5 but only using E3-level features?
• Which service accounts hold full user licences when they need a single workload?
• Which disabled or blocked accounts still consume paid licences?
• Are you paying for add-ons (Copilot, Teams Premium, Intune Suite) that nobody's using?
• How many licences are assigned via group-based licensing versus direct assignment, and do those groups still reflect reality?

Graph API answers all of these. Let's build the audit.

Setting up Graph access

You need an Entra ID app registration with the following application permissions:

Grant admin consent, generate a certificate or client secret (certificate preferred; this is an audit tool, treat it accordingly), and connect:

# Connect with app-only auth (certificate)
Connect-MgGraph -ClientId $appId -TenantId $tenantId -CertificateThumbprint $thumbprint
 
# Or interactively for a one-off audit (delegated)
Connect-MgGraph -Scopes "Organization.Read.All","User.Read.All","AuditLog.Read.All","Reports.Read.All"

If you've just granted AuditLog.Read.All via admin consent, it can take a few minutes to propagate. During this window, queries that include the SignInActivity property will return a 403. Either wait and retry, or query users without SignInActivity first and add it once the consent has propagated.

The SignInActivity property requires Entra ID Premium P1 or P2 licensing to populate. In tenants without Entra ID Premium, this property returns null regardless of API permissions. For those tenants, rely on the M365 usage reports (getOffice365ActiveUserDetail) to determine last activity dates instead.

If you're running this against a client tenant, use a multi-tenant app registration or request delegated access. The permissions above are read-only. You're not changing anything, which makes the consent conversation straightforward.

The code snippets below are kept concise for readability. They show the core logic without parameter blocks, error handling, or config file loading. The companion repository contains production-ready versions of every script with proper parameterisation, structured output, and reusable configuration files for SKU naming and pricing.

By default, M365 usage reports obfuscate user-identifying information. To get actual UPNs in your usage data, which you need for this audit, a Global Admin must go to Settings, then Org Settings, then select Reports on the Services tab, and uncheck "Conceal user, group, and site names in all reports". Without this, you'll get hashed identifiers that can't be cross-referenced against licence assignments.

Step 1: pull the licence inventory

First, establish what the organisation is paying for. The subscribedSkus endpoint returns every licence SKU in the tenant with purchased and consumed counts.

# Get all subscribed SKUs
$skus = Get-MgSubscribedSku -All
 
# Build a clean summary
$licenceSummary = $skus | Where-Object { $_.AppliesTo -eq "User" } | Select-Object @{
    Name = "Licence"; Expression = { $_.SkuPartNumber }
}, @{
    Name = "Purchased"; Expression = { $_.PrepaidUnits.Enabled }
}, @{
    Name = "Assigned"; Expression = { $_.ConsumedUnits }
}, @{
    Name = "Available"; Expression = { $_.PrepaidUnits.Enabled - $_.ConsumedUnits }
}, @{
    Name = "Utilisation"; Expression = {
        if ($_.PrepaidUnits.Enabled -gt 0) {
            [math]::Round(($_.ConsumedUnits / $_.PrepaidUnits.Enabled) * 100, 1)
        } else { 0 }
    }
}
 
$licenceSummary | Sort-Object Licence | Format-Table -AutoSize

This gives you output like:

Licence                    Purchased  Assigned  Available  Utilisation
-------                    ---------  --------  ---------  -----------
ENTERPRISEPREMIUM                600       547         53         91.2
EMSPREMIUM                       600       312        288         52.0
Microsoft_365_Copilot            200        87        113         43.5
FLOW_FREE                    10000       241       9759          2.4
POWER_BI_STANDARD            10000        56       9944          0.6

The SKU part numbers are cryptic. Microsoft publishes a mapping table of SKU part numbers to friendly names, but it's incomplete and frequently outdated. For a production audit, maintain your own lookup table:

$skuNames = @{
    "ENTERPRISEPREMIUM"                = "Office 365 E5"
    "ENTERPRISEPACK"                   = "Office 365 E3"
    "EMSPREMIUM"                       = "Enterprise Mobility + Security E5"
    "SPE_E3"                           = "Microsoft 365 E3 (unified)"
    "SPE_E5"                           = "Microsoft 365 E5 (unified)"
    "SPB"                              = "Microsoft 365 Business Premium"
    "O365_BUSINESS_PREMIUM"            = "Microsoft 365 Business Standard"
    "SMB_BUSINESS_PREMIUM"             = "Microsoft 365 Business Standard - Prepaid Legacy"
    "Microsoft_365_Copilot"            = "Microsoft 365 Copilot"
    "Microsoft_Teams_Exploratory_Dept" = "Microsoft Teams Exploratory"
    "FLOW_FREE"                        = "Power Automate Free"
    "POWER_BI_STANDARD"                = "Microsoft Fabric (Free)"
    # Add your tenant's SKUs here
}

Any SKU below 70% utilisation is worth investigating. Free-tier SKUs, such as Microsoft Fabric (Free) and Power Automate Free, with thousands of unassigned licences are noise; ignore them. Paid SKUs well below that threshold are either over-purchased or not being assigned to users who need them.

Step 2: map per-user licence assignments

Now pull every user and their assigned licences. This is where the real analysis starts.

# Get all users with licence details and sign-in activity
# SignInActivity requires AuditLog.Read.All (listed in the permissions table above)
$users = Get-MgUser -All -Property "Id,DisplayName,UserPrincipalName,AccountEnabled,
    UserType,AssignedLicenses,SignInActivity,Department,JobTitle" `
    -ConsistencyLevel eventual -CountVariable userCount
 
# Build per-user licence map
$userLicences = foreach ($user in $users) {
    foreach ($licence in $user.AssignedLicenses) {
        $skuName = ($skus | Where-Object { $_.SkuId -eq $licence.SkuId }).SkuPartNumber
        [PSCustomObject]@{
            UPN           = $user.UserPrincipalName
            DisplayName   = $user.DisplayName
            Enabled       = $user.AccountEnabled
            UserType      = $user.UserType
            Department    = $user.Department
            SKU           = $skuName
            DisabledPlans = ($licence.DisabledPlans | Measure-Object).Count
            LastSignIn    = $user.SignInActivity.LastSignInDateTime
        }
    }
}

Finding waste pattern 1: disabled accounts with licences

The most common and most easily resolved waste.

$disabledWithLicences = $userLicences |
    Where-Object { $_.Enabled -eq $false -and $_.SKU -notmatch "FREE" } |
    Group-Object SKU |
    Select-Object @{Name="Licence"; Expression={$_.Name}},
        Count,
        @{Name="Users"; Expression={ ($_.Group | Select-Object -ExpandProperty UPN) -join "; " }}
 
$disabledWithLicences | Format-Table -AutoSize

In a typical 2,000-user tenant, this finds 30 to 80 disabled accounts still holding paid licences. At £51.60 a month for an E5, the UK list price with Teams in September 2026, that's £18,576 to £49,536 a year in pure waste (30 x £51.60 x 12 and 80 x £51.60 x 12). The fix is trivial: a scheduled script or lifecycle workflow that strips licences when accounts are disabled.

Finding waste pattern 2: service accounts on full licences

Service accounts, shared mailboxes, and room mailboxes often get assigned the same E3 or E5 licence as a regular user. Most of them need far less.

# Find potential service accounts with premium licences
# Heuristic: no sign-in activity + naming convention patterns
$potentialServiceAccounts = $userLicences | Where-Object {
    $_.Enabled -eq $true -and
    $_.SKU -match "ENTERPRISE|SPE_E" -and
    (
        $_.UPN -match "^(svc|service|admin|noreply|do-not-reply|mailbox|room|shared)" -or
        $_.LastSignIn -eq $null -or
        $_.LastSignIn -lt (Get-Date).AddDays(-180)
    )
}
 
$potentialServiceAccounts | Select-Object UPN, SKU, LastSignIn | Format-Table -AutoSize

This is heuristic: the naming convention filter will catch the obvious ones, and the "no sign-in for 180 days" filter will catch the rest. Review the output manually. A shared mailbox under 50GB needs no licence at all; above 50GB it needs an Exchange Online Plan 2 licence, which raises the limit to 100GB (Plan 1 alone doesn't raise it), not a full E5. If you also need In-Place Archive, you need Plan 2, or Plan 1 with the Exchange Online Archiving add-on.

Finding waste pattern 3: guest users with licences

External users (UserType = Guest) occasionally end up with paid licences, usually through group-based licensing where the group membership isn't properly scoped.

$guestsWithLicences = $userLicences |
    Where-Object { $_.UserType -eq "Guest" -and $_.SKU -notmatch "FREE" }
 
$guestsWithLicences | Select-Object UPN, SKU | Format-Table -AutoSize

Step 3: cross-reference against usage

This is where the audit gets interesting. Knowing who has a licence is useful, but the savings live in knowing who uses the features that licence provides.

The getOffice365ActiveUserDetail report returns per-user activity across Exchange, OneDrive, SharePoint, Teams, and Yammer, including the last activity date for each workload.

# Pull 180-day usage report
# Note: Returns a CSV via redirect. Invoke-MgGraphRequest handles this
$uri = "https://graph.microsoft.com/v1.0/reports/getOffice365ActiveUserDetail(period='D180')"
Invoke-MgGraphRequest -Uri $uri -OutputFilePath "$env:TEMP\M365Usage.csv"
$usage = Import-Csv "$env:TEMP\M365Usage.csv"

The CSV contains columns like Has Exchange License, Exchange Last Activity Date, Has Teams License, Teams Last Activity Date, and Assigned Products, which lists every licence assigned to each user as a comma-separated string.

Finding waste pattern 4: E5 users using only E3 features

This is the biggest single savings opportunity in most tenants. E5 costs about one and a half times what E3 costs, a premium of £18.10 a month with Teams (£51.60 against £33.50), and the premium features (Defender for Office 365 P2, Purview Information Protection, advanced compliance, Phone System) are only relevant to a subset of users.

# Join usage data with licence assignments
# Identify E5 users who haven't used any E5-exclusive workload
 
# Define E5-exclusive activity indicators
# If a user has E5 but their activity is entirely within E3-level workloads,
# they're a downgrade candidate
 
$e5Users = $userLicences | Where-Object { $_.SKU -match "ENTERPRISEPREMIUM|SPE_E5" }
 
$e5UsageAnalysis = foreach ($e5User in $e5Users) {
    $activity = $usage | Where-Object { $_.'User Principal Name' -eq $e5User.UPN }
 
    if ($activity) {
        [PSCustomObject]@{
            UPN                   = $e5User.UPN
            Department            = $e5User.Department
            ExchangeLastActive    = $activity.'Exchange Last Activity Date'
            TeamsLastActive       = $activity.'Teams Last Activity Date'
            SharePointLastActive  = $activity.'SharePoint Last Activity Date'
            OneDriveLastActive    = $activity.'OneDrive Last Activity Date'
            # A user with no activity beyond basic Exchange/Teams/SharePoint
            # is a candidate for E3 downgrade
            AnyActivity           = (
                $activity.'Exchange Last Activity Date' -ne "" -or
                $activity.'Teams Last Activity Date' -ne "" -or
                $activity.'SharePoint Last Activity Date' -ne ""
            )
            LastAnyActivity       = @(
                $activity.'Exchange Last Activity Date'
                $activity.'Teams Last Activity Date'
                $activity.'SharePoint Last Activity Date'
                $activity.'OneDrive Last Activity Date'
            ) | Where-Object { $_ -ne "" } |
                ForEach-Object { [datetime]$_ } |
                Sort-Object -Descending |
                Select-Object -First 1
        }
    }
}

The Graph API usage reports don't differentiate between E3 and E5 feature usage. Exchange activity is Exchange activity regardless of licence tier. E5-exclusive value (Defender, Purview, Phone System) requires checking service plan enablement or using workload-specific reporting endpoints.

A pragmatic approach: if a user's only M365 activity in 180 days is basic Exchange, Teams, and SharePoint, and they're not in a Defender or Purview policy scope, they're a downgrade candidate. The service plan data from step 2 tells you which E5-specific plans are actually enabled, and the usage data tells you whether they're being used.

Finding waste pattern 5: Copilot licences with no adoption

This one stings because Copilot licences are expensive. At £23.10 a user a month, an organisation that bought 200 licences is spending £55,440 a year (200 x £23.10 x 12). If adoption is low, the waste compounds fast.

# Copilot-specific usage isn't in the standard O365 activity report
# Use the Copilot usage report endpoint (requires Reports.Read.All)
$uri = "https://graph.microsoft.com/beta/reports/getMicrosoft365CopilotUserCountSummary(period='D180')"
 
# For per-user Copilot activity, check:
$uri = "https://graph.microsoft.com/beta/reports/getMicrosoft365CopilotUsageUserDetail(period='D180')"
Invoke-MgGraphRequest -Uri $uri -OutputFilePath "$env:TEMP\CopilotUsage.csv"
# The report can return multiple rows per user (one per active day).
# Deduplicate to keep only the most recent activity per user.
$copilotUsage = Import-Csv "$env:TEMP\CopilotUsage.csv" |
    Sort-Object 'User Principal Name', 'Last Activity Date' -Descending |
    Sort-Object 'User Principal Name' -Unique
 
# Cross-reference: who has a Copilot licence but hasn't used it?
$copilotLicensed = $userLicences | Where-Object { $_.SKU -match "Copilot" }
$copilotActive = $copilotUsage | Where-Object { $_.'Last Activity Date' -ne "" }
 
$copilotInactive = $copilotLicensed | Where-Object {
    $_.UPN -notin $copilotActive.'User Principal Name'
}
 
Write-Host "Copilot licences assigned: $($copilotLicensed.Count)"
Write-Host "Copilot users with activity: $($copilotActive.Count)"
Write-Host "Copilot licences with no activity: $($copilotInactive.Count)"
Write-Host "Estimated monthly waste: £$('{0:N2}' -f ($copilotInactive.Count * 23.10))"

The Copilot usage endpoints are beta at the time of writing. The schema has expanded recently (additional activity breakdown columns were added in late 2025), so your import/parsing code should be resilient to new columns. The core columns (User Principal Name, Last Activity Date) remain stable.

Step 4: produce the waste report

Bring it all together into a single output that quantifies the waste in terms finance teams understand: annual cost. The E5, E3 and Copilot figures below are Microsoft's confirmed UK list prices; the add-on prices (EMS E5, Teams Premium, Intune Plan 1) are as published when this post was written and should be checked against current pricing before anyone relies on them.

# Estimated monthly costs per SKU (adjust to your EA/CSP pricing)
$skuMonthlyCost = @{
    "ENTERPRISEPREMIUM"     = 51.60  # E5 per user/month GBP, UK list price with Teams
    "ENTERPRISEPACK"        = 33.50  # E3, UK list price with Teams
    "SPE_E5"                = 51.60  # M365 E5 unified
    "SPE_E3"                = 33.50  # M365 E3 unified
    "EMSPREMIUM"            = 12.30  # EMS E5
    "Microsoft_365_Copilot" = 23.10  # Copilot, UK list price with Teams
    "TEAMS_PREMIUM"         = 8.40   # Teams Premium
    "INTUNE_P1"             = 6.80   # Intune Plan 1 standalone
}
 
# Calculate waste by category
$wasteCategories = @(
    [PSCustomObject]@{
        Category    = "Disabled accounts with licences"
        Users       = ($disabledWithLicences | Measure-Object -Property Count -Sum).Sum
        MonthlyCost = ($userLicences |
            Where-Object { $_.Enabled -eq $false -and $_.SKU -notmatch "FREE" } |
            ForEach-Object { $skuMonthlyCost[$_.SKU] } |
            Measure-Object -Sum).Sum
    }
    [PSCustomObject]@{
        Category    = "Service accounts on premium licences"
        Users       = ($potentialServiceAccounts | Measure-Object).Count
        MonthlyCost = ($potentialServiceAccounts |
            ForEach-Object { $skuMonthlyCost[$_.SKU] } |
            Measure-Object -Sum).Sum
    }
    [PSCustomObject]@{
        Category    = "Guests with paid licences"
        Users       = ($guestsWithLicences | Measure-Object).Count
        MonthlyCost = ($guestsWithLicences |
            ForEach-Object { $skuMonthlyCost[$_.SKU] } |
            Measure-Object -Sum).Sum
    }
    [PSCustomObject]@{
        Category    = "Copilot licences with no activity"
        Users       = ($copilotInactive | Measure-Object).Count
        MonthlyCost = ($copilotInactive | Measure-Object).Count * 23.10
    }
)
 
$totalMonthlyWaste = ($wasteCategories | Measure-Object -Property MonthlyCost -Sum).Sum
$totalAnnualWaste = $totalMonthlyWaste * 12
 
$wasteCategories | Format-Table Category, Users, @{
    Name = "Monthly (GBP)"; Expression = { "£{0:N0}" -f $_.MonthlyCost }
} -AutoSize
 
Write-Host "`nTotal estimated monthly waste: £$('{0:N0}' -f $totalMonthlyWaste)"
Write-Host "Total estimated annual waste:  £$('{0:N0}' -f $totalAnnualWaste)"

For a hypothetical 2,000-user tenant on mostly E5 licensing, the numbers typically look something like this, using the confirmed E5 and Copilot list prices of £51.60 and £23.10 a month:

Those figures come from the same maths as the script above: 62 x £51.60, 18 x £51.60, 7 x £51.60 and 41 x £23.10, each multiplied by 12 for the annual figure.

And this doesn't even include the E5-to-E3 downgrade candidates. At the current E5 premium of £18.10 a month, in an estate of this size those would typically add another £26,000 to £44,000 a year in savings.

Step 5: the structural fixes

Finding the waste is easy; what separates an audit from a governance practice is preventing it from recurring.

Automate licence reclamation on account disable

If your user lifecycle runs through Entra ID, whether via HR-driven provisioning, lifecycle workflows, or a manual disable process, add a licence stripping step. Entra ID Lifecycle Workflows can trigger on the leaver scenario and remove all licence assignments as part of the offboarding flow; this feature needs an Entra ID Governance or Entra Suite licence.

If you're not using lifecycle workflows, a scheduled script that checks for disabled accounts with licences and reclaims them weekly is the minimum viable approach. Run it with -WhatIf first to see what it would remove before you let it remove anything:

# Weekly scheduled task: reclaim licences from disabled accounts
[CmdletBinding(SupportsShouldProcess)]
param()
 
$disabled = Get-MgUser -All -Filter "accountEnabled eq false" `
    -Property "Id,UserPrincipalName,AssignedLicenses" |
    Where-Object { $_.AssignedLicenses.Count -gt 0 }
 
foreach ($user in $disabled) {
    $licencesToRemove = $user.AssignedLicenses | Select-Object -ExpandProperty SkuId
    if ($PSCmdlet.ShouldProcess($user.UserPrincipalName, "Remove $($licencesToRemove.Count) licence(s)")) {
        Set-MgUserLicense -UserId $user.Id `
            -AddLicenses @() `
            -RemoveLicenses $licencesToRemove
        Write-Host "Removed $($licencesToRemove.Count) licence(s) from $($user.UserPrincipalName)"
    }
}

This script needs the LicenseAssignment.ReadWrite.All application permission, the least privileged permission that can remove a licence, in addition to the read permissions listed under Setting up Graph access.

Move to group-based licensing

If you're still assigning licences directly to users, stop. Group-based licensing (GBL) ties licence assignment to Entra ID group membership, which means your licence posture is governed by the same group structures that control access, policy, and application assignment.

The benefit for cost control is direct. When someone moves department, their group membership changes and their licence assignment follows automatically. Removing someone from the group on departure reclaims the licence. To see who has E5, you look at one group, not 2,000 individual user objects.

The migration from direct to group-based licensing is a topic in its own right, but the Graph API makes it auditable:

# Identify users with direct licence assignments (not via group)
$directAssigned = Get-MgUser -All `
    -Property "Id,UserPrincipalName,LicenseAssignmentStates" |
    Where-Object {
        $_.LicenseAssignmentStates | Where-Object {
            $_.AssignedByGroup -eq $null -and $_.State -eq "Active"
        }
    }
 
Write-Host "Users with direct licence assignments: $($directAssigned.Count)"
$directAssigned | Select-Object UserPrincipalName,
    @{Name="DirectLicences"; Expression={
        ($_.LicenseAssignmentStates |
            Where-Object { $_.AssignedByGroup -eq $null } |
            Measure-Object).Count
    }} | Sort-Object DirectLicences -Descending | Select-Object -First 20

Schedule the audit

Running these scripts manually once is useful, but governance means running them automatically every week and surfacing the results in a dashboard that stakeholders can check themselves. If you're implementing broader automation practices for M365, the Configuration as Code for Endpoint Management post explains how to version-control, test, and deploy infrastructure configuration systematically. The next section builds exactly that dashboard.

Step 6: building the licence waste dashboard

Scripts that produce console output serve only the person running them, not everyone else who needs to see the waste. If you want licence waste to stay visible and stay fixed, you need a dashboard that updates itself and that finance, IT management, and service owners can access without asking you to re-run a script.

The architecture is straightforward: an Azure Automation runbook runs the audit on a schedule, writes the results to a SharePoint list, and Power BI connects to that list with scheduled refresh. No Azure SQL, no blob storage, no additional Azure spend beyond the Automation account.

The SharePoint list schema

Create a SharePoint list called LicenceAuditResults on a site accessible to your IT governance stakeholders. The schema needs to support both the summary data (for the headline cards) and the per-user detail (for drill-through).

LicenceAuditResults list

Create a second list for the inventory summary:

LicenceInventorySummary list

The Azure Automation runbook

The runbook consolidates the scripts from the earlier sections and writes results to SharePoint. You'll need a System-Assigned Managed Identity on your Automation Account with the Graph permissions listed under Setting up Graph access, plus Sites.ReadWrite.All for writing to SharePoint (or Sites.Selected with explicit site-level permissions for least privilege).

# Runbook: Invoke-LicenceAudit.ps1
# Schedule: Weekly (Sunday 02:00 UTC)
# Identity: System-Assigned Managed Identity
# Requires PowerShell 7.4 or later (Azure Automation retires 7.1 and 7.2 support on 30 September 2026)
 
# Authenticate using Managed Identity
Connect-MgGraph -Identity
 
# --- Configuration ---
$siteId     = "contoso.sharepoint.com,<site-guid>,<web-guid>"
$auditListId    = "<LicenceAuditResults-list-guid>"
$summaryListId  = "<LicenceInventorySummary-list-guid>"
$auditDate  = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ssZ")
 
$skuMonthlyCost = @{
    "ENTERPRISEPREMIUM"     = 51.60
    "ENTERPRISEPACK"        = 33.50
    "SPE_E5"                = 51.60
    "SPE_E3"                = 33.50
    "Microsoft_365_Copilot" = 23.10
    # Extend for your tenant
}
 
$skuFriendlyNames = @{
    "ENTERPRISEPREMIUM"     = "Office 365 E5"
    "ENTERPRISEPACK"        = "Office 365 E3"
    "SPE_E5"                = "Microsoft 365 E5 (unified)"
    "SPE_E3"                = "Microsoft 365 E3 (unified)"
    "Microsoft_365_Copilot" = "Microsoft 365 Copilot"
    # Extend for your tenant
}
 
# --- Step 1: Licence Inventory ---
$skus = Get-MgSubscribedSku -All
foreach ($sku in ($skus | Where-Object { $_.AppliesTo -eq "User" })) {
    $fields = @{
        "AuditDate"             = $auditDate
        "SKUPartNumber"         = $sku.SkuPartNumber
        "FriendlyName"          = $skuFriendlyNames[$sku.SkuPartNumber] ?? $sku.SkuPartNumber
        "Purchased"             = $sku.PrepaidUnits.Enabled
        "Assigned"              = $sku.ConsumedUnits
        "Utilisation"           = if ($sku.PrepaidUnits.Enabled -gt 0) {
            [math]::Round(($sku.ConsumedUnits / $sku.PrepaidUnits.Enabled) * 100, 1)
        } else { 0 }
        "EstimatedMonthlySpend" = $sku.ConsumedUnits * ($skuMonthlyCost[$sku.SkuPartNumber] ?? 0)
    }
    New-MgSiteListItem -SiteId $siteId -ListId $summaryListId -BodyParameter @{ fields = $fields }
}
 
# --- Step 2: Per-User Analysis ---
$users = Get-MgUser -All -Property "Id,DisplayName,UserPrincipalName,AccountEnabled,
    UserType,AssignedLicenses,SignInActivity,Department"
 
# Pull usage report
$usagePath = "$env:TEMP\M365Usage_$(Get-Date -Format yyyyMMdd).csv"
$uri = "https://graph.microsoft.com/v1.0/reports/getOffice365ActiveUserDetail(period='D180')"
Invoke-MgGraphRequest -Uri $uri -OutputFilePath $usagePath
$usage = Import-Csv $usagePath
 
foreach ($user in $users) {
    foreach ($licence in $user.AssignedLicenses) {
        $skuName = ($skus | Where-Object { $_.SkuId -eq $licence.SkuId }).SkuPartNumber
        $monthlyCost = $skuMonthlyCost[$skuName] ?? 0
 
        # Skip free SKUs
        if ($monthlyCost -eq 0) { continue }
 
        # Determine waste category
        $wasteCategory = $null
 
        if (-not $user.AccountEnabled) {
            $wasteCategory = "Disabled Account"
        }
        elseif ($user.UserType -eq "Guest") {
            $wasteCategory = "Guest"
        }
        elseif ($user.UserPrincipalName -match "^(svc|service|admin|noreply|shared|room)") {
            $wasteCategory = "Service Account"
        }
        else {
            # Check usage
            $activity = $usage | Where-Object {
                $_.'User Principal Name' -eq $user.UserPrincipalName
            }
            $lastActivity = @(
                $activity.'Exchange Last Activity Date'
                $activity.'Teams Last Activity Date'
                $activity.'SharePoint Last Activity Date'
                $activity.'OneDrive Last Activity Date'
            ) | Where-Object { $_ -ne "" } |
                ForEach-Object { [datetime]$_ } |
                Sort-Object -Descending |
                Select-Object -First 1
 
            if (-not $lastActivity -or $lastActivity -lt (Get-Date).AddDays(-90)) {
                $wasteCategory = "Inactive User"
            }
        }
 
        # Only write waste records (not every user)
        if ($wasteCategory) {
            $fields = @{
                "AuditDate"             = $auditDate
                "UserPrincipalName"     = $user.UserPrincipalName
                "DisplayName"           = $user.DisplayName
                "Department"            = $user.Department ?? "Unset"
                "AccountEnabled"        = $user.AccountEnabled
                "UserType"              = $user.UserType
                "AssignedSKU"           = $skuName
                "SKUFriendlyName"       = $skuFriendlyNames[$skuName] ?? $skuName
                "WasteCategory"         = $wasteCategory
                "LastSignIn"            = $user.SignInActivity.LastSignInDateTime
                "LastWorkloadActivity"  = $lastActivity
                "EstimatedMonthlyCost"  = $monthlyCost
            }
            New-MgSiteListItem -SiteId $siteId -ListId $auditListId -BodyParameter @{ fields = $fields }
        }
    }
}
 
Write-Output "Licence audit complete. Records written to SharePoint."

A few important notes on this runbook:

• The system-assigned managed identity needs Graph application permissions granted via PowerShell. You can't do this through the portal for managed identities. Use New-MgServicePrincipalAppRoleAssignment to grant the required permissions to the Automation Account's service principal.
• The runbook writes new rows each run rather than overwriting. This gives you trend data in Power BI, showing waste decreasing (or increasing) over time. Set a retention policy or a Power Automate flow to purge records older than 12 months to keep the list manageable.
• SharePoint lists support up to 30 million items, but performance degrades beyond a few hundred thousand. For most tenants, weekly waste records (only the waste, not every user) will stay well within limits. If you're running this against a 50,000-user tenant, consider writing to a filtered subset or switching to Azure Table Storage.

The Power BI dashboard

Connect Power BI Desktop to both SharePoint lists using the SharePoint Online List connector. Build the following pages:

Page 1: Executive summary

• Card visuals showing total monthly waste (£), total annual waste (£), and number of wasted licences
• A donut chart breaking waste down by WasteCategory
• A bar chart showing waste by SKUFriendlyName, which licence types are being wasted most
• A line chart showing total monthly waste over time from the AuditDate dimension; this is the trend line that proves the audit is working
• A slicer on AuditDate to compare snapshots

Page 2: Licence inventory

• A table visual from LicenceInventorySummary showing every SKU, purchased versus assigned, utilisation percentage, monthly spend
• Conditional formatting on utilisation: red below 50%, amber 50 to 70%, green above 70%
• A stacked bar chart showing purchased versus assigned versus wasted per SKU

Page 3: Waste detail, drill-through

• A table visual from LicenceAuditResults with all columns, where people find specific accounts to action
• Slicers on WasteCategory, Department, SKUFriendlyName, and AccountEnabled
• Export to Excel enabled so IT admins can take action lists offline
• Drill-through from Page 1's donut or bar chart into this detail page, pre-filtered by the segment clicked

Page 4: Department breakdown

• A matrix visual showing departments as rows, waste categories as columns, and costs as values
• This is the page that gets department heads to care. Nobody wants to be the team with the most licence waste

DAX measures

A few calculated measures to make the visuals cleaner:

// Total Monthly Waste (uses ALL to ignore slicer context when finding latest date)
Total Monthly Waste =
VAR LatestAuditDate = CALCULATE(MAX(LicenceAuditResults[AuditDate]), ALL(LicenceAuditResults))
RETURN
CALCULATE(
    SUM(LicenceAuditResults[EstimatedMonthlyCost]),
    LicenceAuditResults[AuditDate] = LatestAuditDate
)
 
// Total Annual Waste
Total Annual Waste = [Total Monthly Waste] * 12
 
// Waste Trend (for line chart, sum per audit date)
Waste By Audit Date =
SUM(LicenceAuditResults[EstimatedMonthlyCost])
 
// Licence Utilisation %
Utilisation Pct =
DIVIDE(
    SUM(LicenceInventorySummary[Assigned]),
    SUM(LicenceInventorySummary[Purchased]),
    0
) * 100

Scheduled refresh

Publish the report to a Power BI Pro workspace. Configure scheduled refresh to run daily (or match your runbook cadence) by adding the SharePoint Online data source credentials in the dataset settings. Use the Automation Account's schedule to run the runbook weekly on Sunday night, and set Power BI refresh for Monday morning. Your stakeholders get an updated view at the start of each week.

The result is a self-refreshing dashboard that surfaces licence waste without anyone having to run a script, ask for a report, or dig through the Admin Centre. When the finance team asks "are we optimising our M365 spend?", you point them at the dashboard.

What this doesn't cover (yet)

This audit focuses on the straightforward waste: licences assigned to accounts that don't need them, or aren't using them. There are deeper optimisation questions that deserve their own posts:

• E3 vs. E5 right-sizing at scale goes beyond "who's using E5 features" to "what's the optimal licence mix for this organisation's security and compliance requirements". That's a design decision, not an audit.
• Add-on rationalisation means evaluating whether standalone add-ons, such as Intune Suite, Entra ID Governance and Teams Premium, deliver enough value to justify their cost against upgrading the base licence.
• Seasonal and project-based licensing needs a different approach for organisations with contractors or seasonal workers.
• Microsoft has released a beta API (/admin/cloudLicensing) that consolidates subscription and consumption data into a single endpoint. Watch it, but it isn't stable enough yet to build audit tooling around.

For now, run the scripts, build the dashboard, and show finance the numbers. In my experience, the first audit always pays for the time it took to run, and usually several times over.

Next up: if you're questioning whether E5 is worth it at all, the E3 vs. E5 decision framework will help you work through the trade-offs systematically.