Summary Rules - Entra Group Membership Report
Entra Group Membership Report
Query
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()About this 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
- https://kqlquery.com/posts/sentinel-summary-rules/
Sentinel
Explanation
This KQL query is designed to generate a report on user group memberships within an organization. Here's a simplified breakdown of what the query does:
-
Data Source: It starts by accessing the
IdentityInfotable, which contains information about user identities. -
Latest Record Selection: It uses the
arg_maxfunction to select the most recent record for each user, identified byAccountObjectId. This ensures that the latest information is used for each user. -
Group Membership Expansion: The
mv-expandfunction is used to break out the list of group memberships for each user into individual records. This allows the query to analyze each group membership separately. -
Summary of Memberships:
- It calculates the total number of unique group memberships (
TotalMemberships) for each user. - It also compiles a list of all groups a user is a member of (
MemberOf), with a limit of 1000 groups per user.
- It calculates the total number of unique group memberships (
-
Additional Information: The query includes the user's display name (
AccountDisplayName) and user principal name (AccountUPN) for reference. -
Report Date: It adds a
ReportDatefield with the current date and time, indicating when the report was generated.
Purpose: This report helps track changes in user group memberships over time, providing insights into whether a user's memberships are increasing or decreasing. It can also be used for generating reports on group memberships.
Recommended Execution: The query is suggested to run every 24 hours with a 60-minute delay to ensure data is up-to-date and accurate.
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators