📧 Recipe · Exchange & Email
Generate an Email Activity and Inactive Mailbox Report with Microsoft Graph
Identify inactive mailboxes, usage drops, and licensed-but-unused accounts across your M365 tenant
Complexity
Intermediate
Impact
cost-savings + license-optimization + security-monitoring + compliance + reporting
Context
Why This Matters
Email activity reporting is a cornerstone of license right-sizing, offboarding hygiene, and security monitoring in Microsoft 365. Over time, mailboxes fall dormant — employees leave, roles shift, service accounts get forgotten — yet their Exchange Online licenses continue to bill month after month.
When to run this recipe
- Quarterly license reviews — identify Exchange/E3/E5 seats that can be reclaimed or downgraded.
- Offboarding audits — confirm departed users' mailboxes are actually inactive before removal.
- Security investigations — a sudden drop in send/receive volume for a normally active user can indicate account compromise, forwarding rules, or delegation abuse.
- Adoption tracking — surface licensed users who never adopted Outlook, suggesting training gaps.
The Microsoft Graph reports/getEmailActivityUserDetail endpoint returns send, receive, and read counts per user over a rolling period (7, 30, 90, or 180 days). Joining this with the user/license directory in Entra ID produces a single spreadsheet that answers: who has a mailbox license but isn't using it?
Expected Outcomes
After running this recipe, you will have:
- A CSV report listing every user with columns for display name, UPN, last activity date, 30-day send count, 30-day receive count, license status, and account-enabled state.
- A Status column classifying each user as Active, Low Activity / Potential Drop, Licensed - No Activity, or Inactive / No Data.
- A count of licensed users with zero email activity — the primary candidates for license reclamation.
- A reusable artifact suitable for sharing with finance, HR, or security for follow-up decisions.
Risks & Considerations
Privacy & compliance
- Concealed names setting: If your tenant has "Display concealed user, group, and site names" enabled in the M365 Admin Center, the report will return hashed UPNs (e.g.
A1B2C3...) instead of real email addresses, making the join with Entra ID impossible. Disable it temporarily (admin only) or accept anonymized output. - Reports.Read.All is powerful — it grants visibility into usage across all Microsoft 365 services. Scope app registrations carefully and rotate client secrets.
Data interpretation gotchas
- The report has a ~48 hour lag. A user active this morning may still appear inactive.
- Users with only receive activity (e.g. distribution-list members) may look dormant but are still receiving business mail — don't blindly remove their license.
- Shared mailboxes, resource mailboxes, and room mailboxes show up with inflated or unusual patterns. Filter
userTypeor recipient type separately. - New users in their first week typically have low counts — exclude users created within the last 30 days before flagging.
Do not
- Do not auto-remove licenses based on this report alone — always get manager or user confirmation.
- Do not share the CSV externally without redacting UPNs; it is personally identifiable data under GDPR/CCPA.
Required Permissions
| Permission | Why It's Needed |
|---|---|
| Reports.Read.All | Read usage reports including email activity detail via the Microsoft Graph reports endpoint. |
| User.Read.All | Enumerate users and retrieve assignedLicenses and accountEnabled properties to cross-reference with activity data. |
| Directory.Read.All | Optional — alternative to User.Read.All for broader directory read access. |
The fastest way to get this done: just ask Dex. Copy the prompt below and paste it into your Dex conversation.
For IT Admins
Paste into Dex Pro
Pull the report from the Microsoft 365 Admin Center
The Admin Center exposes the same data the API returns, but with built-in charting and no need for app registration. This path is ideal for one-off audits.
Step 1 — Open the Email activity report
- Sign in to the Microsoft 365 Admin Center as a Global Reader, Reports Reader, or Global Administrator.
- Navigate: .
- Set the date range selector to Last 30 days (or 7/90/180 as needed).
Step 2 — Confirm real names are visible
- If UPNs appear as hashes (e.g.
3F2504E0-...), navigate: . - Uncheck Display concealed user, group, and site names in all reports.
- Click Save, then return to the report and refresh.
Step 3 — Export and analyze
- On the Email activity report page, click the Export icon (down-arrow) in the top right.
- Open the downloaded CSV in Excel.
- Add a filter to the Last activity date column and sort ascending — users with blank or oldest dates are your inactivity candidates.
- Cross-reference with to identify who is licensed but absent from the active list.
Step 4 — Document findings
Tag each candidate with a disposition (reclaim license, convert to shared mailbox, investigate, leave alone) and circulate to the relevant business owners before making changes.
Fetch email activity via Microsoft Graph
The Graph reports endpoint is the scriptable foundation of this recipe. It requires Reports.Read.All application or delegated permission.
Endpoint 1 — Email activity detail (30 days)
GET https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D30')Valid period values: D7, D30, D90, D180. The default response is CSV. To request JSON, append ?$format=application/json.
Sample CSV response (truncated)
Report Refresh Date,User Principal Name,Display Name,Is Deleted,Deleted Date,Last Activity Date,Send Count,Receive Count,Read Count,Meeting Created Count,Meeting Interacted Count,Assigned Products,Report Period
2025-01-15,alice@contoso.com,Alice Chen,False,,2025-01-15,42,187,203,3,11,MICROSOFT 365 E3,30
2025-01-15,bob@contoso.com,Bob Singh,False,,,,,,,MICROSOFT 365 E3,30Endpoint 2 — User directory with licenses
GET https://graph.microsoft.com/v1.0/users?$select=id,displayName,userPrincipalName,assignedLicenses,accountEnabled&$top=999For tenants with more than 999 users, follow the @odata.nextLink cursor until exhausted.
Join logic
- Build a map keyed on lowercase UPN from the activity CSV.
- Iterate the user list; for each user, look up their activity row.
- Classify:
- Active — has
Last Activity Datewithin period and sendCount + receiveCount ≥ 5. - Low Activity — licensed, has activity row, but total < 5.
- Licensed - No Activity — licensed, no activity row or blank last activity date.
- Inactive / No Data — unlicensed and no activity.
- Active — has
Throttling
Graph throttles reports endpoints aggressively. If you receive 429 Too Many Requests, honor the Retry-After header. For tenants > 10,000 users, cache the activity CSV locally and re-use it across runs rather than re-fetching.
Run the PowerShell script
The downloadable script uses the Microsoft Graph PowerShell SDK to authenticate, fetch both datasets, join them in memory, and emit a CSV.
Prerequisites
- PowerShell 7.2 or later (Windows PowerShell 5.1 works but is slower).
- Microsoft Graph PowerShell SDK:
Install-Module Microsoft.Graph -Scope CurrentUser. - An account with Reports Reader + Directory Reader roles (or higher).
Usage
# Default: 30-day window, output to current directory
.\Get-EmailActivityReport.ps1
# Custom period and output path
.\Get-EmailActivityReport.ps1 -Period D90 -OutputPath C:\Reports\email-audit.csv
# Only flag licensed users with zero activity
.\Get-EmailActivityReport.ps1 -LicensedOnlyWhat the script does
- Connects to Graph with
Reports.Read.AllandUser.Read.Allscopes (interactive device-code sign-in on first run). - Downloads the email activity CSV for the requested period into a temp file.
- Pages through
/users, collecting UPN, licenses, and enabled state. - Joins on lowercase UPN and calculates a Status column.
- Writes the merged result to the output CSV and prints a summary table to the console.
Output columns
DisplayName, UserPrincipalName, Status, LastActivityDate, SendCount, ReceiveCount, ReadCount, IsLicensed, AccountEnabled, AssignedProducts.
Next steps