KQL Search

Query Details

HomeAI ToolsDevice Query

Role Report

Query

# Role Report

## Query Information

#### Description
This query can be used to draw an report of the Entra ID role memberships for all users.

## Defender XDR
```KQL
let TimeFrame = 30d;
IdentityInfo
| where Timestamp > ago(TimeFrame)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand AssignedRoles
| where isnotempty(AssignedRoles)
| summarize TotalRoles = dcount(tostring(AssignedRoles)), MemberOf = make_set(tostring(AssignedRoles), 1000) by AccountObjectId, AccountDisplayName, AccountUPN
| extend ReportDate = now()
| sort by TotalRoles desc  
```
## Sentinel
```KQL
let TimeFrame = 30d;
IdentityInfo
| where TimeGenerated > ago(TimeFrame)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand AssignedRoles
| where isnotempty(AssignedRoles)
| summarize TotalRoles = dcount(tostring(AssignedRoles)), MemberOf = make_set(tostring(AssignedRoles), 1000) by AccountObjectId, AccountDisplayName, AccountUPN
| extend ReportDate = now()
| sort by TotalRoles desc
```

Explanation

This query generates a report of Entra ID role memberships for all users over the past 30 days. Here's a simplified breakdown of what the query does:

  1. Set Time Frame: It looks at data from the last 30 days.
  2. Filter Data: It retrieves identity information records that have been updated within this time frame.
  3. Get Latest Record: For each user, it keeps only the most recent record.
  4. Expand Roles: It breaks out the list of roles assigned to each user into individual entries.
  5. Filter Non-Empty Roles: It removes any users who don't have any roles assigned.
  6. Summarize Roles: It counts the total number of unique roles each user has and creates a set of these roles.
  7. Add Report Date: It adds the current date to the report.
  8. Sort Users: Finally, it sorts the users by the total number of roles they have in descending order.

The result is a list of users with their display names, user principal names (UPNs), the total number of roles they have, the specific roles they are members of, and the date the report was generated.

Details

Bert-Jan Pals profile picture

Bert-Jan Pals

Released: August 26, 2024

Tables

IdentityInfo

Keywords

EntraIDRolesUsers

Operators

letwheresummarizearg_maxbymv-expandisnotemptydcounttostringmake_setextendnowsort

Actions