Microsoft 365 reporting via PowerShell

Most Microsoft 365 reporting questions can be answered with PowerShell — Microsoft Graph PowerShell SDK plus the service-specific modules (Exchange Online, Teams, SharePoint Online, Microsoft 365 admin). Knowing the right patterns saves hours of manual admin centre clicking.

The modules you'll use

• Microsoft.Graph — the modern unified module. Most user, group, and licensing data.
• ExchangeOnlineManagement — mailbox stats, mail flow, transport rules, EOP / Defender for Office.
• MicrosoftTeams — Teams-specific configuration and policies.
• PnP.PowerShell — SharePoint deep work.
• AzureAD (deprecated) — being replaced by Microsoft.Graph. Avoid for new scripts.

Install as needed; Microsoft.Graph has sub-modules so you don't have to install the whole thing.

Common reporting patterns

Licence assignments

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

# Active licences in the tenant
Get-MgSubscribedSku | 
  Select-Object SkuPartNumber, PrepaidUnits, ConsumedUnits

# Users with a specific licence
$skuId = (Get-MgSubscribedSku -Filter "SkuPartNumber eq 'SPE_E5'").SkuId
Get-MgUser -All -Filter "assignedLicenses/any(x:x/skuId eq $skuId)" |
  Select-Object DisplayName, UserPrincipalName

Inactive users (no sign-in in 90 days)

$cutoff = (Get-Date).AddDays(-90).ToString("o")
Get-MgUser -All -Property UserPrincipalName,SignInActivity |
  Where-Object { $_.SignInActivity.LastSignInDateTime -lt $cutoff } |
  Select-Object UserPrincipalName, @{N="LastSignIn";E={$_.SignInActivity.LastSignInDateTime}}

Useful for licence reclamation — inactive licensed users cost money.

Mailboxes over quota

Connect-ExchangeOnline
Get-EXOMailbox -ResultSize Unlimited |
  Get-EXOMailboxStatistics |
  Where-Object { $_.TotalItemSize -gt 49GB } |
  Select-Object DisplayName, TotalItemSize

Identify mailboxes approaching the 50 GB limit (or 100 GB depending on plan) so you can enable archive mailboxes before users hit the wall.

Teams meeting recording locations

For tenants that need to audit where meeting recordings live:

# Channel meeting recordings - in the team's SharePoint site
# Non-channel meeting recordings - in the organiser's OneDrive
# Query via Graph for files matching meeting-recording pattern

SharePoint site storage by site

Connect-SPOService -Url https://yourtenant-admin.sharepoint.com
Get-SPOSite -Limit All |
  Sort-Object StorageUsageCurrent -Descending |
  Select-Object Url, @{N="StorageMB";E={$_.StorageUsageCurrent}}, Owner |
  Export-Csv site-storage.csv -NoTypeInformation

Useful for identifying which sites are consuming the tenant's SharePoint storage pool.

Conditional Access policy assignments

Connect-MgGraph -Scopes "Policy.Read.All"
Get-MgIdentityConditionalAccessPolicy |
  Select-Object DisplayName, State, 
    @{N="UsersIncluded";E={($_.Conditions.Users.IncludeUsers + $_.Conditions.Users.IncludeGroups) -join ","}},
    @{N="UsersExcluded";E={($_.Conditions.Users.ExcludeUsers + $_.Conditions.Users.ExcludeGroups) -join ","}},
    @{N="Apps";E={$_.Conditions.Applications.IncludeApplications -join ","}}

Inventory all CA policies in one report.

Defender for Endpoint device inventory

Connect-MgGraph -Scopes "DeviceManagementManagedDevices.Read.All"
Get-MgDeviceManagementManagedDevice -All |
  Select-Object DeviceName, OperatingSystem, ComplianceState, LastSyncDateTime

Devices and their compliance state.

Authentication patterns for production scripts

For one-off scripts, interactive sign-in works:

Connect-MgGraph -Scopes "User.Read.All"

For scheduled scripts, use certificate-based authentication:

Connect-MgGraph -ClientId "app-id" -TenantId "tenant-id" -CertificateThumbprint "thumbprint"

This requires an Entra ID app registration with the right Graph permissions and a certificate uploaded. Better than password-based auth for unattended scripts.

For Azure-hosted automation, managed identities are ideal — no credentials to manage.

Output destinations

Reports are most useful when delivered automatically:

• Email to relevant stakeholders.
• SharePoint list for ongoing dashboards.
• Power BI for richer visualisation.
• Teams channel for visibility to the team.
• Slack / external chat for cross-platform distribution.

A simple pattern: scheduled Azure Function or Azure Automation runbook executes the PowerShell, writes results to a SharePoint list, Power BI refreshes from the list.

For organisations with ongoing Microsoft 365 reporting needs, this kind of structured PowerShell reporting is dramatically more efficient than manual admin centre work. Build a library; share with the team.