Group Membership Report
Query
let TimeFrame = 30d;
IdentityInfo
| where Timestamp > ago(TimeFrame)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand GroupMembership
| where isnotempty(GroupMembership)
| summarize TotalMemberships = dcount(tostring(GroupMembership)), MemberOf = make_set(tostring(GroupMembership), 1000) by AccountObjectId, AccountDisplayName, AccountUPN
| extend ReportDate = now()About this query
Group Membership Report
Query Information
Description
This query can be used to draw an report of the Entra ID group memberships of all users.
Note: if a users has more than 1000 memberships remove the 1000 limitation in the make_set to display all groupnames.
Defender XDR
Sentinel
let TimeFrame = 30d;
IdentityInfo
| where TimeGenerated > ago(TimeFrame)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand GroupMembership
| where isnotempty(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 Entra ID (formerly Azure AD) over the past 30 days. Here's a simple breakdown of what the query does:
-
Time Frame: It looks at data from the last 30 days.
-
Data Source: It uses the
IdentityInfotable, which contains information about user identities. -
Latest Record: For each user, it selects the most recent record based on the
TimeGeneratedtimestamp. -
Expand Group Memberships: It expands the list of group memberships for each user, so each membership is treated as a separate entry.
-
Filter Non-Empty Memberships: It filters out any entries where the group membership information is empty.
-
Summarize Memberships:
- It counts the total number of unique group memberships for each user.
- It creates a list of up to 1000 group names that each user is a member of. If a user has more than 1000 memberships, the limit can be removed to display all group names.
-
Add Report Date: It adds a column with the current date to indicate when the report was generated.
The query is essentially the same for both Defender XDR and Sentinel, with minor differences in the field names (Timestamp vs. TimeGenerated).
Details
Bert-Jan Pals
Released: October 20, 2024
Tables
Keywords
Operators