Export Microsoft 365 License Cost Report Using PowerShell – The Ultimate Guide

Managing Microsoft 365 licensing efficiently is crucial for optimizing costs and ensuring compliance. In this guide, we provide a comprehensive PowerShell-based solution to export detailed Microsoft 365 license cost reports, enabling IT administrators to gain full visibility into licensing usage and expenditure.

🔍 Why Export a Microsoft 365 License Cost Report?

Exporting a Microsoft 365 license cost report helps in:

• Identifying unused or underutilized licenses
• Tracking license assignment trends
• Forecasting licensing expenses
• Complying with auditing requirements

🛠 Prerequisites for Running PowerShell Scripts

Before running PowerShell scripts for Microsoft 365 license reporting, ensure the following:

• Install the Microsoft Graph PowerShell SDK:

Install-Module Microsoft.Graph -Scope CurrentUser

• Connect to Microsoft Graph:

Connect-MgGraph -Scopes "User.Read.All", "Organization.Read.All", "Directory.Read.All"

• Admin privileges with the necessary Graph API permissions.

📊 Gather Microsoft 365 License Information Using PowerShell

Use the following command to retrieve all SKUs (Stock Keeping Units) in the tenant:

Get-MgSubscribedSku | Select-Object SkuPartNumber, SkuId, ConsumedUnits, PrepaidUnits

To make this data meaningful for cost reporting, map SKUs to their friendly names and costs.

💸 Assign Costs to Licenses Manually

Microsoft does not expose license pricing via API. To calculate cost, create a mapping file:

SkuPartNumber,UnitCostUSD
ENTERPRISEPACK,35
BUSINESS_PREMIUM,22
E5_SECURITY,12

📁 Export Microsoft 365 License Cost Report

Here’s a complete PowerShell script to extract and export the license usage and cost report:

# Load Cost Mapping
$costMap = Import-Csv -Path "./LicenseCostMap.csv" | Group-Object -Property SkuPartNumber -AsHashTable -AsString

# Get license details
$licenseData = Get-MgSubscribedSku | Select-Object SkuPartNumber, SkuId, ConsumedUnits, @{Name="AvailableUnits";Expression={$_.PrepaidUnits.Enabled}}

# Prepare report
$report = foreach ($item in $licenseData) {
    $sku = $item.SkuPartNumber
    $unitCost = $costMap[$sku].UnitCostUSD
    [PSCustomObject]@{
        SkuPartNumber    = $sku
        AssignedLicenses = $item.ConsumedUnits
        AvailableLicenses = $item.AvailableUnits
        TotalLicenses    = $item.ConsumedUnits + $item.AvailableUnits
        UnitCostUSD      = $unitCost
        TotalCostUSD     = [math]::Round($unitCost * $item.ConsumedUnits, 2)
    }
}

# Export to CSV
$report | Export-Csv -Path "./Microsoft365LicenseCostReport.csv" -NoTypeInformation

Write-Host "License cost report exported successfully."

📈 Sample Output: Microsoft 365 License Cost Report

📚 Best Practices for License Management

• Review licenses quarterly to detect inactive accounts.
• Automate reports using scheduled tasks in Windows.
• Align license types with employee roles to avoid overprovisioning.
• Maintain an internal cost reference file for SKU pricing.

🧠 Automate with Azure Automation or Scheduled Task

To keep reports up to date:

• Use Azure Automation Runbooks to schedule the script execution in the cloud.
• Or, use Windows Task Scheduler for on-prem automation.

✅ Conclusion

Generating a Microsoft 365 license cost report using PowerShell equips IT admins with actionable insights. It streamlines budget forecasting, identifies optimization opportunities, and ensures you only pay for what you use. By implementing this method, organizations can proactively manage licenses and control spending effectively.

❓ FAQs

1. How can I check Microsoft 365 license usage using PowerShell?

You can use the Get-MgSubscribedSku cmdlet from the Microsoft Graph PowerShell SDK to check license usage. It provides details like ConsumedUnits (assigned licenses) and PrepaidUnits (total purchased licenses).

2. How do I calculate Microsoft 365 license cost using PowerShell?

To calculate license costs, assign a unit price to each SKU manually using a CSV mapping file. Then, multiply the assigned license count by the unit cost to get the total expense per SKU.

3. Can I schedule this license cost report to run automatically?

Yes, you can automate the report using Windows Task Scheduler or Azure Automation. This ensures regular reporting and helps maintain up-to-date cost visibility without manual effort.

4. Why is Microsoft 365 license cost not directly available via API?

Microsoft does not expose real-time pricing via Graph API due to regional pricing variations and licensing agreements. Administrators need to map SKUs to pricing manually based on their contract or published list prices.

5. What are the most common SKUs in Microsoft 365 cost reports?

Common SKUs include ENTERPRISEPACK (Microsoft 365 E3), E5 (Microsoft 365 E5), BUSINESS_PREMIUM, and add-ons like EMS or E5_SECURITY. Accurate SKU mapping is essential for proper cost analysis.

Stay updated on the latest in Microsoft 365, SharePoint, OneDrive, Teams, Intune, and more! Subscribe to our newsletter for exclusive insights and updates. Follow me on LinkedIn to stay updated with all the latest Microsoft 365 news and updates: Pankaj Kumar