👥 Recipe · User & Group Management
Generate a Department Headcount Report via Microsoft Graph
Query Entra ID to produce a sorted breakdown of users per department across your tenant
Complexity
Beginner
Impact
read-only + reporting + low-risk
Context
Why This Matters
Department headcount reports are a foundational HR and IT operations artifact. They're useful for license planning, org chart validation, onboarding capacity planning, identifying users with missing or malformed department attributes, and reconciling Entra ID data with HRIS systems like Workday or BambooHR.
Because the department property is a free-text field on the Entra user object, this report is also a quick way to surface data hygiene issues — typos, inconsistent casing, or users whose department was never populated at all.
When to run it
- Monthly, as part of an identity hygiene review
- Before a reorg or cost-center realignment
- Whenever HR asks "how many people are in X?" and you want a definitive answer from the source of truth
- Before bulk license assignments scoped by department
Expected Outcomes
After running this recipe you will have:
- A sorted list of every distinct
departmentvalue in Entra ID and the count of users in each - A count of users with no department assigned (reported as (Unassigned))
- A total user count for the tenant
- Optional CSV output suitable for sharing with HR or importing into Excel/Power BI
Risks & Considerations
Things to watch for
- Free-text inconsistency. "Engineering", "engineering", and "Eng" will be counted as three separate departments. Consider normalizing before reporting.
- Guest and service accounts. The default query returns all users including guests and non-human accounts. Filter by
userType eq 'Member'andaccountEnabled eq trueif you only want active employees. - Pagination. Tenants with more than ~100 users will paginate. Always follow
@odata.nextLinkto avoid under-reporting. - Privacy. While headcount totals are generally non-sensitive, some orgs treat department-level staffing data as confidential. Handle the output accordingly.
- Do not use this report as authoritative headcount for finance or legal purposes without reconciling against HRIS — Entra is only as accurate as the provisioning process that populates it.
Required Permissions
| Permission | Why It's Needed |
|---|---|
| User.Read.All | Required to enumerate all users in the tenant and read the department property |
| Directory.Read.All | Alternative broader permission that also grants read access to user attributes |
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
Generate a headcount report from the Microsoft 365 admin center
The admin center does not offer a one-click "group by department" report, but you can approximate it by exporting and pivoting in Excel.
Step 1 — Open the user list
Microsoft 365 Admin Center → Users → Active users
Step 2 — Export all users
- Click Export users at the top of the Active users list.
- Choose Export all users and confirm. A CSV will be downloaded (or emailed for large tenants).
Step 3 — Pivot in Excel
- Open the exported CSV and locate the
Departmentcolumn. - Select the data range and insert a PivotTable.
- Drag
Departmentinto both Rows and Values (as Count). - Sort the pivot table descending by count.
Step 4 — Clean up
Scan for typos, case inconsistencies, and blank departments. Flag these for remediation with HR or your provisioning system.
Note: For tenants over a few hundred users, the API or scripted approaches below are significantly faster and produce cleaner output.
Query Microsoft Graph directly
The report is built from a single Graph endpoint with $select to minimize payload size, followed by client-side aggregation.
Endpoint
GET https://graph.microsoft.com/v1.0/users?$select=department&$top=999
Example request
GET /v1.0/users?$select=department&$top=999 Authorization: Bearer <token> ConsistencyLevel: eventual
Example response (truncated)
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users(department)",
"@odata.nextLink": "https://graph.microsoft.com/v1.0/users?$select=department&$skiptoken=...",
"value": [
{ "department": "Engineering" },
{ "department": "Sales" },
{ "department": null }
]
}Pagination
Follow the @odata.nextLink URL until it is no longer returned. Each page returns up to 999 users when $top=999 is specified (the maximum allowed).
Filtering to active members only (optional)
GET /v1.0/users?$select=department&$filter=userType eq 'Member' and accountEnabled eq true&$count=true&$top=999 ConsistencyLevel: eventual
The ConsistencyLevel: eventual header and $count=true parameter are required when filtering on accountEnabled.
Aggregation
Graph does not support server-side GROUP BY. Aggregate client-side by iterating the combined result set and incrementing a dictionary keyed on department (treat null/empty as (Unassigned)).
Run the report with PowerShell
The Microsoft Graph PowerShell SDK handles pagination automatically with -All, making this a tight one-liner plus a Group-Object.
Prerequisites
- PowerShell 7+ recommended
Microsoft.Graphmodule installed:Install-Module Microsoft.Graph -Scope CurrentUser- An account with at least
User.Read.All
Connect
Connect-MgGraph -Scopes "User.Read.All"
Pull users and group by department
# Fetch only the department property for speed $users = Get-MgUser -All -Property Department -Select Department # Group and sort $report = $users | ForEach-Object { if ([string]::IsNullOrWhiteSpace($_.Department)) { '(Unassigned)' } else { $_.Department } } | Group-Object | Select-Object @{N='Department';E={$_.Name}}, Count | Sort-Object Count -Descending $report | Format-Table -AutoSize
Export to CSV
$report | Export-Csv -Path ".\department-headcount.csv" -NoTypeInformation
The downloadable script below wraps this up with parameters, optional filters for active members only, and CSV export.
Next steps