KQL Search

Query Details

HomeAI ToolsDevice Query

Entra Group Membership Report

Query

# Summary Rules - Entra Group Membership Report

## Query Information

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

**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 GroupMembership
| summarize TotalMemberships = dcount(tostring(GroupMembership)), MemberOf = make_set(tostring(GroupMembership), 1000) by AccountObjectId, AccountDisplayName, AccountUPN
| extend ReportDate = now()
```

Explanation

This query is designed to generate a report on the group memberships of users in an Entra (Azure AD) environment. Here's a simplified breakdown:

  1. Source Data: It starts by pulling user identity information from the IdentityInfo table.
  2. Latest Records: It keeps only the most recent record for each user based on the TimeGenerated field.
  3. Expand Memberships: It then expands the group memberships for each user, so each membership is treated as a separate entry.
  4. Summarize Memberships: For each user, it counts the total number of unique group memberships and creates a list of these memberships.
  5. Add Report Date: Finally, it adds the current date and time as the report date.

Usage: This report can be used to track changes in group memberships over time, helping to identify trends such as increases or decreases in memberships for specific users. It is recommended to run this query every 24 hours with a 60-minute delay to ensure up-to-date information.

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

Details

Bert-Jan Pals profile picture

Bert-Jan Pals

Released: September 4, 2024

Tables

IdentityInfo

Keywords

UsersGroupMemberships

Operators

summarizearg_maxbymv-expanddcounttostringmake_setextendnow

Actions