Key Points
- PowerShell enables centralized collection of Microsoft 365 user activity reports across Exchange Online, Teams, SharePoint, and OneDrive.
- Using the Microsoft Graph PowerShell module and Get-MgUser provides richer, unified data and simplifies inventory and usage tracking.
- Incorporating Graph Reports and Exchange Online mailbox metrics within user inventories supports Microsoft user activity reporting for governance, audits, and license optimization.
- Normalizing and automating exports ensures standardized, audit-ready Microsoft 365 user reports across all tenants.
- Secure automation with app-only authentication, retry logic, and logging enables reliable, scalable, and unattended PowerShell workflows.
- NinjaOne enhances Microsoft 365 user reporting through remote script deployment, scheduling, secure credential storage, and centralized automation.
Microsoft 365 (formerly Office 365) user activity reports are crucial when tracking service usage, ensuring client productivity, and maintaining security and compliance. Leveraging PowerShell offers a unified approach to gathering actionable insights across Exchange Online, Teams, SharePoint, and OneDrive.
Collect Microsoft User Activity Reports through PowerShell
Microsoft Graph PowerShell offers rich data quality and strong filtering capabilities. Through PowerShell, you can combine user inventory, usage, and mailbox metrics in a centralized pipeline, supporting license optimization, access governance enforcement, and incident investigations.
📌 Prerequisites:
- PowerShell 7 or later with Microsoft Graph and Exchange Online modules installed
- Azure app registration with least-privilege Graph scopes, such as Group.Read.All and Reports.Read.All, plus consent
- Optional Exchange Online permissions for mailbox statistics
- Secure credential storage and a central output path for CSV and JSON
Step #1: Inventory users with the Microsoft Graph PowerShell module
Generating a comprehensive user inventory establishes the foundation for monitoring and managing Microsoft 365 clients. The script below captures only the most relevant user details, reducing runtime and maintaining process efficiency at scale.
By maintaining a consistent CSV export format across tenants, each report follows the same structure. This makes them easy to compare, merge, and enrich later with data from sources, such as Microsoft Graph Reports or Exchange Online.
Sample user inventory PowerShell script
Install-Module Microsoft.Graph -Scope CurrentUserImport-Module Microsoft.GraphConnect-MgGraph -Scopes "User.Read.All","Reports.Read.All"Get-MgUser -All -Property "id,displayName,userPrincipalName,mail,accountEnabled,createdDateTime,assignedLicenses" |Select-Object id,displayName,userPrincipalName,mail,accountEnabled,createdDateTime, @{n='LicenseCount';e={$_.assignedLicenses.Count}} |Export-Csv "C:\Reports\M365_Users.csv" -NoTypeInformation |
💡 Tip: You can omit some of the properties inside the Get-MGUser -All -Propertyline to control the scope of your query.
Step #2: Add usage metrics using the Graph Reports API
Graph Reports provide insights into user activity details across various Microsoft 365 services. This surfaces dormant accounts and areas of concentrated usage, helping you determine where to deprovision and upgrade licenses.
Sample Graph Reports API PowerShell scripts
The following script retrieves user-level activity across different workloads over a specified time period.
Get-MgReportOffice365ActiveUserDetail -Date (Get-Date).AddDays(-1) -OutFile "C:\Reports\ActiveUserDetail.csv" |
Note that the script above requests yesterday’s data, denoted by .AddDays (-1). Alternatively, you can opt to use rolling periods like the ones below:
# Last 7 daysGet-MgReportOffice365ActiveUserDetail -Period D7 -OutFile "C:\Reports\ActiveUserDetail_D7.csv"# Last 30 daysGet-MgReportOffice365ActiveUserDetail -Period D30 -OutFile "C:\Reports\ActiveUserDetail_D30.csv" |
Afterward, you can join the generated CSV file with your pre-existing inventory list from step #1 to identify inactive or lightly active accounts by workload:
$inv = Import-Csv "C:\Reports\M365_Users.csv" # From step 1$usage = Import-Csv "C:\Reports\ActiveUserDetail.csv" # From step 2$joined = $inv | Join-Object -On userPrincipalName -Right $usage -Kind Left # or use a custom hash join# Alternative without a module: build a hash on UPN for the usage file and add the fields you need$joined | Export-Csv "C:\Reports\M365_Users_Usage.csv" -NoTypeInformation |
Step #3: Enrich user inventory with Exchange Online mailbox metrics
Incorporating mailbox metrics in your inventory list identifies oversized or rapidly growing mailboxes before they impact client productivity. Additionally, it ensures that client accounts and policies are properly maintained while helping admins smoothly migrate Exchange Online mailboxes.
Sample PowerShell script to query Exchange Online metrics
The following script connects you to Microsoft 365’s Exchange Online service and retrieves key mailbox metrics per user. This helps administrators understand mailbox usage by pulling key metrics like mailbox size, number of items, deleted items, last logon time, and storage limits.
Connect-ExchangeOnline -UserPrincipalName [email protected]Get-MailboxStatistics -Identity [email protected] |
Run the script for users who meet the following criteria:
- Rapid mailbox growth
- High-storage or near-quota mailboxes
- VIP, shared, or resource mailboxes
- Migration candidates
- Compliance-sensitive users
Step #4: Standardize, export, and store Microsoft 365 user activity reports
After pulling metrics using Get-MgUser, Graph Reports, and Exchange Online, they don’t line up by default. Normalization provides standardized exports, offering a consistent data format that streamlines automation and fosters repeatability across tenants.
Recommended action plan:
- Normalize property names and add calculated fields, such as LicenseCount, IsEnabled, and AsOf, so datasets join cleanly.
- Export CSV outputs for operators and JSON for dashboards and automation.
- Name each file with its tenant ID, time window, and script version for better auditability. For example:
- users_{TenantId}_{Window}_{ScriptVersion}.csv
- users_{TenantId}_{Window}_{ScriptVersion}.json
Step #5: Automate workflows with secure authentication and logging
Combining standardization and automation enables you to replicate workflows across tenants. Incorporating secure sign-ins allows your scripts to run smoothly unattended and on schedule.
Automating retry and backoff workflows ensures jobs remain stable, even when Microsoft 365 limits requests. Structured logging, on the other hand, demonstrates the effectiveness of your script, as it captures what ran and the metrics it generated.
Recommended action plan:
- Use app-only authentication to allow unattended scripts to run uninterrupted.
- Add retry and back-off configurations to preserve collection stability despite API throttling.
- Maintain a detailed run log, including start/end times, filters, and record counts, to maintain traceability during audits or reviews.
- Schedule frequent inventory checks and weekly activity updates to ensure report freshness without incurring unnecessary costs.
- For MSPs, use PowerShell loops to run across clients and generate separate reports for each tenant.
Step #6: Operationalize Microsoft 365 user reports
After collecting all necessary reporting data, operationalizing your findings is what transforms raw metrics into actionable insights. By integrating exports with compliance, governance, and service review practices, you get visibility into your client’s operations.
This enables you to help clients make informed decisions and mitigate risk. Additionally, through this, you can provide them with evidence-backed plans that return measurable service improvements.
Recommended action plan:
- Attach user and activity extracts to compliance packets to prove you’re maintaining accurate access records and monitoring data usage.
- Cross-reference reports with HR or directory notifications to detect inactive users, stale access, or orphaned licenses.
- Show highlights in QBRs to surface adoption trends, usage growth, or risk reduction.
- Archive reports to build a factual baseline for post-incident analysis and faster resolution.
Automate Microsoft 365 user report collection
Manual processes during collection workflows can slow down analysis due to repetitive workflows, which can also increase operational costs. Through automation, you transform data collection practice into a consistent and standardized process that minimizes human error.
Sample automation touchpoints:
- Create a nightly script job that exports user inventories using the Microsoft Graph PowerShell module.
- Schedule weekly scripts that collect active user details and a targeted job that collects mailbox statistics for flagged users.
- Automate the creation of timestamped exports and their subsequent handoff to your evidence repository.
NinjaOne services for Microsoft 365 user activity report collection
NinjaOne enhances how you collect and manage Microsoft Office 365 user activity reports through automation, security, and scalability. Its remote scripting and scheduling capabilities make PowerShell-based workflows streamlined, enabling centralized and multi-tenant deployments.
- Remote script deployment: Instantly run Graph Report, Exchange Online, and Microsoft Graph PowerShell scripts centrally and at scale through NinjaOne’s remote scripting feature.
- Scheduled Automations: Utilize NinjaOne’s scheduled automations feature to execute collection scripts at predetermined intervals, such as during system startup or user login.
- Secure custom fields: Store sensitive credentials safely within secure custom fields and access stored information centrally using PowerShell scripts.
- Comprehensive scripting capabilities: Create custom PowerShell scripts to automate user inventories, identify inactive users by workload, or generate license optimization candidates, to name a few.
Optimize Microsoft 365 user report collection using PowerShell
With the deprecation of the MSOnline and AzureAD modules, the shift to Microsoft Graph PowerShell offered a modern, unified approach for collecting data across Microsoft 365 services.
By combining secure automation, normalized exports, and streamlined evidence packaging, teams transform raw metrics into actionable operational visibility that supports audits, optimizes license usage, and accelerates incident investigations.
Related topics:
- How to Use Microsoft Graph API to Pull Compliance Reports Across Multiple Tenants
- How to Automate Microsoft 365 Role Change Notifications for Admin Oversight
- How to Run Scheduled Graph API Checks for Expired Licenses and Inactive Users
- Using Microsoft Graph API with Windows PowerShell to Monitor and Manage Microsoft 365 Clients
