20 February 2026·12 min read

The M365 Licensing Audit Nobody Wants to Do

Using Microsoft Graph API to pull licence inventory, per-user assignments, and workload activity data to produce a waste report — with scripts, an Azure Automation runbook, and a Power BI dashboard.

microsoft-365graph-apipowershellazurelicensing

Every Microsoft 365 tenant I've assessed has been overspending on licences. Not by a little — by 15–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 reason this audit doesn't get done isn't that it's hard. It's that 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.

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:

Permission	Why
`Organization.Read.All`	Read subscribed SKUs (licence inventory)
`User.Read.All`	Read user profiles and licence assignments
`AuditLog.Read.All`	Read user sign-in activity (last sign-in dates)
`Reports.Read.All`	Access M365 usage reports
`Directory.Read.All`	Read group memberships for group-based licensing analysis

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","Directory.Read.All"

Permission propagation: 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.

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.

Code in this post vs. the companion repo: 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.

A note on report privacy: 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 disable the privacy setting in the M365 Admin Centre under Settings > Org Settings > 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"                = "Microsoft 365 E5"
    "ENTERPRISEPACK"                   = "Microsoft 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 Premium"
    "Microsoft_365_Copilot"            = "Microsoft 365 Copilot"
    "Microsoft_Teams_Exploratory_Dept" = "Microsoft Teams Exploratory"
    "FLOW_FREE"                        = "Power Automate Free"
    "POWER_BI_STANDARD"                = "Power BI Free"
    # Add your tenant's SKUs here
}

What to look for immediately: Any SKU below 70% utilisation is worth investigating. Free-tier SKUs (Power BI Free, Power Automate Free) with thousands of unassigned licences are noise — ignore them. Paid SKUs with significant headroom 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–80 disabled accounts still holding paid licences. At roughly £30/month for an E5, that's £10,800–£28,800 per year in pure waste. 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 typically needs an Exchange Online Plan 2 licence (or none at all if under 50GB), not a full E5.

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. Knowing who uses the features that licence provides is where the savings live.

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 — crucially — 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 roughly twice what E3 costs, 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 directly tell you which tier of feature a user accessed — Exchange usage is Exchange usage whether you're on E3 or E5. The E5-exclusive value comes from Defender, Purview, and Phone System, which have their own reporting endpoints or require checking service plan enablement at the user level.

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 roughly £24/user/month, an organisation that bought 200 licences is spending £57,600 per year. 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"
$copilotUsage = Import-Csv "$env:TEMP\CopilotUsage.csv"
 
# 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:N0}' -f ($copilotInactive.Count * 24))"

Note: The Copilot usage endpoints are beta at the time of writing. The schema may change, but the principle won't — cross-reference assignment against activity.

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.

# Estimated monthly costs per SKU (adjust to your EA/CSP pricing)
$skuMonthlyCost = @{
    "ENTERPRISEPREMIUM"     = 49.20  # E5 per user/month GBP
    "ENTERPRISEPACK"        = 28.40  # E3
    "SPE_E5"                = 49.20  # M365 E5 unified
    "SPE_E3"                = 28.40  # M365 E3 unified
    "EMSPREMIUM"            = 12.30  # EMS E5
    "Microsoft_365_Copilot" = 24.00  # Copilot
    "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 * 24
    }
)
 
$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:

Category	Users	Monthly (GBP)	Annual (GBP)
Disabled accounts with licences	62	£3,050	£36,600
Service accounts on premium licences	18	£886	£10,630
Guests with paid licences	7	£345	£4,135
Copilot with no activity	41	£984	£11,808
Total	128	£5,265	£63,173

And this doesn't even include the E5-to-E3 downgrade candidates, which in an estate of this size would typically add another £30,000–£50,000 in annual savings.

Step 5: The Structural Fixes

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

Automate Licence Reclamation on Account Disable

If your user lifecycle runs through Entra ID — either 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.

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:

# Weekly scheduled task: reclaim licences from disabled accounts
$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
    Set-MgUserLicense -UserId $user.Id `
        -AddLicenses @() `
        -RemoveLicenses $licencesToRemove
    Write-Host "Removed $($licencesToRemove.Count) licence(s) from $($user.UserPrincipalName)"
}

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 benefits for cost control are significant. When someone moves department, their group membership changes and their licence assignment follows automatically. When someone leaves, removing them from the group reclaims the licence. When you want 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. Running them automatically every week and surfacing the results in a dashboard that stakeholders can check themselves — that's governance. The next section builds exactly that.

Step 6: Building the Licence Waste Dashboard

Scripts that produce console output are useful for the person running them. They're useless for everyone else. 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.

┌─────────────────────┐     ┌──────────────────────┐     ┌─────────────────────┐
│  Azure Automation   │     │   SharePoint Online   │     │      Power BI       │
│                     │     │                       │     │                     │
│  Scheduled Runbook  ├────►│  Licence Audit List   ├────►│  Waste Dashboard    │
│  (weekly, Graph API)│     │  (structured output)  │     │  (scheduled refresh)│
│                     │     │                       │     │                     │
└─────────────────────┘     └──────────────────────┘     └─────────────────────┘

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).

List: LicenceAuditResults

Column	Type	Purpose
`AuditDate`	Date/Time	When this record was generated
`UserPrincipalName`	Single line of text	The user or account
`DisplayName`	Single line of text	Friendly name
`Department`	Single line of text	For departmental drill-down
`AccountEnabled`	Yes/No	Active or disabled
`UserType`	Choice (Member/Guest)	Internal vs. external
`AssignedSKU`	Single line of text	SKU part number
`SKUFriendlyName`	Single line of text	Human-readable licence name
`WasteCategory`	Choice	Disabled Account / Service Account / Guest / Inactive User / Copilot Unused / E5 Downgrade Candidate
`LastSignIn`	Date/Time	Last interactive sign-in
`LastWorkloadActivity`	Date/Time	Last M365 workload activity
`EstimatedMonthlyCost`	Currency	Per-user monthly cost of the wasted licence

Create a second list for the inventory summary:

List: LicenceInventorySummary

Column	Type	Purpose
`AuditDate`	Date/Time	Snapshot date
`SKUPartNumber`	Single line of text	Raw SKU identifier
`FriendlyName`	Single line of text	Human-readable name
`Purchased`	Number	Total purchased
`Assigned`	Number	Total assigned
`Utilisation`	Number	Percentage utilised
`EstimatedMonthlySpend`	Currency	Total monthly cost for this SKU

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 from Step 0, plus Sites.Manage.All for writing to SharePoint.

# Runbook: Invoke-LicenceAudit.ps1
# Schedule: Weekly (Sunday 02:00 UTC)
# Identity: System-Assigned Managed Identity
 
# 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"     = 49.20
    "ENTERPRISEPACK"        = 28.40
    "SPE_E5"                = 49.20
    "SPE_E3"                = 28.40
    "Microsoft_365_Copilot" = 24.00
    # Extend for your tenant
}
 
$skuFriendlyNames = @{
    "ENTERPRISEPREMIUM"     = "Microsoft 365 E5"
    "ENTERPRISEPACK"        = "Microsoft 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 -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 -Fields $fields
        }
    }
}
 
Write-Output "Licence audit complete. Records written to SharePoint."

A few important notes on this runbook:

Managed Identity permissions. 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.

Historical data. The runbook writes new rows each run rather than overwriting. This gives you trend data in Power BI — you can see 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.

List item limits. SharePoint lists support up to 30 million items, but performance degrades beyond a few hundred thousand. For most tenants, weekly waste records (not every user — only the waste) 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
Donut chart breaking waste down by WasteCategory
Bar chart showing waste by SKUFriendlyName (which licence types are being wasted most)
Line chart showing total monthly waste over time from the AuditDate dimension — this is the trend line that proves the audit is working
Slicer on AuditDate to compare snapshots

Page 2: Licence Inventory

Table visual from LicenceInventorySummary showing every SKU, purchased vs. assigned, utilisation percentage, monthly spend
Conditional formatting on utilisation — red below 50%, amber 50–70%, green above 70%
Stacked bar chart showing purchased vs. assigned vs. wasted per SKU

Page 3: Waste Detail (Drill-Through)

Table visual from LicenceAuditResults with all columns — this is 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

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
Total Monthly Waste =
CALCULATE(
    SUM(LicenceAuditResults[EstimatedMonthlyCost]),
    LicenceAuditResults[AuditDate] = MAX(LicenceAuditResults[AuditDate])
)
 
// 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: 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 — 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 just an audit.
Add-on rationalisation — evaluating whether standalone add-ons (Intune Suite, Entra ID Governance, Teams Premium) deliver enough value to justify their cost versus upgrading the base licence.
Seasonal and project-based licensing — organisations with contractors or seasonal workers need a different approach to licence lifecycle management.
The new Cloud Licensing API — Microsoft has released a beta API (/admin/cloudLicensing) that consolidates subscription and consumption data into a single endpoint. It's worth watching but not yet stable enough to build audit tooling around.

For now, run the scripts, build the dashboard, and let the numbers speak for themselves. 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.