Mail Licensed Users Report to M365 Administrator Using PowerShell

Managing Microsoft 365 license assignments is a critical task for administrators. Knowing which users are licensed, and what license SKUs they hold, helps with compliance, audits, and cost control. With Microsoft Graph PowerShell, you can automate this process and deliver the results directly to the administrator’s inbox.

Below is a script that fetches all licensed users (Members), maps their assigned licenses to SKU part numbers, exports the data to CSV, and emails the report to the administrator.

i) The Script

    # Connect to Microsoft Graph
    # Note: Directory.Read.All is required to read subscribed SKUs for mapping license GUIDs -> SKU part numbers.
    Connect-MgGraph -Scopes "User.Read.All","Mail.Send","Directory.Read.All"

    # ---------------------------------------------
    # 1) Build a map of SkuId (GUID) -> SkuPartNumber
    # ---------------------------------------------
    $skuMap = @{}
    $subscribedSkus = Get-MgSubscribedSku
    foreach ($sku in $subscribedSkus) {
    # Ensure the key is a string form of the GUID
    $skuMap[[string]$sku.SkuId] = $sku.SkuPartNumber
    }

    # ---------------------------------------------
    # 2) Fetch all licensed users (Members only)  -- NOTE: escaped `$` in the filter
    # ---------------------------------------------
    $LicensedUsers = Get-MgUser -All `
    -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" `
    -ConsistencyLevel eventual `
    -CountVariable Records `
    -Property Id, DisplayName, UserPrincipalName, Mail, AssignedLicenses

    # ---------------------------------------------
    # 3) Shape the data for export
    # ---------------------------------------------
    $ReportRows = foreach ($user in $LicensedUsers) {
    $skuIds = @()
    if ($user.AssignedLicenses) {
    # assignedLicenses is a collection; grab their SkuId GUIDs
    $skuIds = $user.AssignedLicenses | ForEach-Object { $_.SkuId }
    }

    # Map GUIDs -> SKU part numbers (e.g., 'SPE_E5', 'ENTERPRISEPREMIUM')
    $skuPartNumbers = $skuIds | ForEach-Object {
    $skuMap[[string]$_]
    } | Where-Object { $_ } | Sort-Object -Unique

    [pscustomobject]@{
    DisplayName             = $user.DisplayName
    UserPrincipalName       = $user.UserPrincipalName
    Mail                    = $user.Mail
    LicenseSkuIds           = ($skuIds -join ';')              # Raw GUIDs
    LicenseSkuPartNumbers   = ($skuPartNumbers -join ';')      # Friendly SKU codes
    }
    }

    # ---------------------------------------------
    # 4) Export to CSV
    # ---------------------------------------------
    $ReportPath = "$env:TEMP\LicensedUsers.csv"
    $ReportRows | Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8

    # ---------------------------------------------
    # 5) Email the report to the administrator
    # ---------------------------------------------
    $AdminUPN = "admin@yourtenant.onmicrosoft.com"   # Replace with your admin mailbox
    $Subject="Licensed Users Report - $(Get-Date -Format 'yyyy-MM-dd')"
    $Body=@"
    Hello Admin,
    Please find attached the latest list of licensed users (Members) in the tenant, including their license GUIDs and SKU part numbers.
    Regards,
    Graph PowerShell Script
"@

# Read and attach the CSV as a fileAttachment
$AttachmentContent = [System.Convert]::ToBase64String([System.IO.File]::ReadAllBytes($ReportPath))
$Attachments = @(
    @{
        "@odata.type" = "#microsoft.graph.fileAttachment"
        Name          = "LicensedUsers.csv"
        ContentBytes  = $AttachmentContent
    }
)

# Build the message payload (BodyParameter)
$Message = @{
    Message = @{
        Subject = $Subject
        Body = @{
            ContentType = "HTML"
            Content     = $Body
        }
        ToRecipients = @(
            @{
                EmailAddress = @{ Address = $AdminUPN }
            }
        )
        Attachments = $Attachments
    }
    SaveToSentItems = "true"
}

# Send the email from the admin's mailbox
Send-MgUserMail -UserId $AdminUPN -BodyParameter $Message

Write-Host "Licensed users report emailed successfully to $AdminUPN"

ii) How the Script Works

1. Connects to Microsoft Graph – The script authenticates with the scopes User.Read.All, Directory.Read.All, and Mail.Send. These are required to read users, query subscribed SKUs, and send emails.
2. Builds a SKU map – It fetches all subscribed SKUs and builds a dictionary mapping SkuId GUIDs to their SkuPartNumbers (e.g., ENTERPRISEPREMIUM, M365_E3).
3. Fetches licensed users – Using the filter assignedLicenses/$count ne 0 and userType eq 'Member', the script retrieves only licensed Members, excluding unlicensed accounts and guests.
4. Shapes the output – For each user, the script extracts the raw SkuId GUIDs and resolves them into SKU part numbers using the map.
5. Exports the report – The data is exported to a CSV file in the system’s temp folder.
6. Emails the CSV – The CSV file is attached to an email and sent to the administrator’s mailbox using Send-MgUserMail.

iii) Further Enhancements

• Include Guests – Modify the filter to include licensed guest users if needed.
• Schedule Automation – Run this script daily/weekly via Task Scheduler or Azure Automation for continuous monitoring.
• Add More Properties – Include properties like AccountEnabled, CreatedDateTime, or Department for richer reporting.
• Filter by Specific License – Adapt the filter to return only users with a specific SKU.
• HTML Report – Replace the CSV attachment with a nicely formatted HTML table in the email body.

iv) Use Cases

• License Cost Tracking – Ensure only active users consume licenses.
• Compliance Reporting – Generate reports for auditors showing license assignments.
• License Optimization – Identify underutilized or duplicate license assignments.
• Delegated Administration – Share reports with business owners or IT managers without requiring portal access.

v) Possible Errors & Solutions

Error	Cause	Solution
Invalid filter clause	$count was not escaped in the filter string.	Escape $ as ` (backtick) or use single quotes.
Insufficient privileges to complete the operation	Missing Directory.Read.All or User.Read.All permissions.	Re-run Connect-MgGraph with proper scopes and grant consent.
Send-MgUserMail : Resource not found	$AdminUPN is not a valid mailbox.	Update $AdminUPN to a valid admin email in your tenant.
Email Sent but CSV Empty	The filter did not return any licensed users.	Verify users are assigned licenses, and confirm the filter syntax.

vi) Conclusion

This Graph PowerShell script gives administrators a clear and automated view of licensed users in their Microsoft 365 tenant. By exporting and emailing the report, it removes manual steps, ensures visibility into license usage, and strengthens compliance efforts.

With small enhancements such as scheduling or richer reporting, this script can become a key component of your license management and governance strategy.