KQL Search

Query Details

HomeAI ToolsDevice Query

Entra Roles Report

Query

# Summary Rules - Entra Assigned Roles Report

## Query Information

#### Description
This summary rule focusses on the assigned roles of users. The results of the summary rule can again be used to get insights into specific users, to for example see if their roles increase or decrease overtime. These results can also serve as input for reporting on role assignments.

**Recommended Schedule:** 24 hours.

**Recommended Delay:** 60 minutes.
#### References
- https://learn.microsoft.com/en-us/azure/sentinel/summary-rules

## Sentinel
```KQL
IdentityInfo
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand AssignedRoles
| where isnotempty(AssignedRoles)
| summarize TotalRoles = dcount(tostring(AssignedRoles)), Roles = make_set(tostring(AssignedRoles), 100) by AccountObjectId, AccountDisplayName, AccountUPN
| extend ReportDate = now()
```

Explanation

This query is designed to generate a report on the roles assigned to users in an Entra (Azure AD) environment. Here's a simplified summary:

  1. Data Source: The query pulls data from the IdentityInfo table.
  2. Latest Data: It selects the most recent entry for each user based on AccountObjectId.
  3. Role Expansion: It expands the list of roles assigned to each user.
  4. Filter: It filters out any users who do not have assigned roles.
  5. Role Summary: It calculates the total number of unique roles (TotalRoles) and compiles a list of these roles (Roles) for each user.
  6. User Information: It includes the user's ID (AccountObjectId), display name (AccountDisplayName), and user principal name (AccountUPN).
  7. Timestamp: It adds the current date and time as ReportDate.

Usage: This report can be used to track changes in user roles over time and for generating role assignment reports.

Recommended Execution: Run this query every 24 hours with a delay of 60 minutes to ensure data freshness.

Reference: For more details, you can refer to the Azure Sentinel summary rules documentation.

Details

Bert-Jan Pals profile picture

Bert-Jan Pals

Released: September 9, 2024

Tables

IdentityInfo

Keywords

UsersRolesAccount

Operators

summarizearg_maxmv-expandwhereisnotemptydcounttostringmake_setextendnow

Actions