Query

IdentityInfo
| where TimeGenerated > ago(14d)
| summarize arg_max(TimeGenerated, *) by AccountObjectId
| mv-expand GroupMembership
| extend GroupName = tostring(GroupMembership)
| where GroupName startswith "AAD-SG-Role-"
| summarize GroupMembers = make_set(AccountUPN), TotalUsers = dcount(AccountUPN) by GroupName
| project GroupName, TotalUsers, GroupMembers

About this query

Entra ID - PIM Group Members

Query Information

Description

Use the below query to list all the Entra ID Group Members.

References

Microsoft Sentinel

List all the members of a Group, in this case the Group Name pattern relates to the groups that are used in combination with Entra ID PIM

consider adjusting the GroupName pattern.

Explanation

This query is designed to list all the members of specific groups in Entra ID (formerly known as Azure Active Directory) that are associated with Privileged Identity Management (PIM). Here's a simple breakdown of what the query does:

Data Source: It starts by looking at the IdentityInfo data, which contains information about user identities.
Time Filter: It filters the data to only include records from the last 14 days.
Latest Record Selection: For each user (identified by AccountObjectId), it selects the most recent record based on the TimeGenerated timestamp.
Expand Group Memberships: It expands the list of groups each user is a member of, so each group membership is on a separate row.
Filter Groups: It filters the groups to only include those whose names start with "AAD-SG-Role-", which are likely groups used with Entra ID PIM.
Summarize Members: For each group, it creates a list of unique user principal names (AccountUPN) of the members and counts the total number of unique users in each group.
Output: Finally, it outputs the group name, the total number of users in the group, and the list of group members.

In summary, this query helps identify and list members of specific Entra ID groups used with PIM, focusing on groups with names starting with "AAD-SG-Role-".

KQL Search