Query

EntraGroupMemberships
| where TimeGenerated > ago(3d)
| summarize arg_max(TimeGenerated, *) by sourceId, targetId
| join kind=leftouter (EntraGroups | where TimeGenerated > ago(3d) |  summarize arg_max(TimeGenerated, TimeGenerated, description, displayName, groupTypes, mailNickname) by id ) on $left.sourceId == $right.id
| project GroupId = sourceId, ObjectId = targetId, tenantId, organizationId, GroupDescription = description, GroupDisplayName = displayName, groupTypes, mailNickname

About this query

Detection Enrichment - Entra Group Membership Enriched

Query Information

Description

Sentinel Data Lake job to put an aggregated table of group memberships in LAW for filtering/enrichment in detections and automations.

References

https://learn.microsoft.com/en-us/azure/sentinel/datalake/enable-data-connectors

Sentinel

Explanation

This query is designed to create an enriched dataset of group memberships from Entra (Azure Active Directory) data, which can be used for filtering and enhancing security detections and automations in Microsoft Sentinel. Here's a simplified breakdown of what the query does:

Data Source: It starts by accessing the EntraGroupMemberships table, which contains records of group memberships.
Time Filter: It filters the data to include only records generated in the last 3 days.
Aggregation: It uses the summarize function to get the most recent record for each unique combination of sourceId (group) and targetId (member) based on the TimeGenerated timestamp.
Joining Data: The query performs a left outer join with the EntraGroups table, which contains details about the groups themselves, such as descriptions and display names. This join is also filtered to include only records from the last 3 days.
Projection: Finally, it selects and renames specific columns to create a more useful output. The resulting dataset includes:
- GroupId: The ID of the group.
- ObjectId: The ID of the member.
- tenantId and organizationId: Identifiers for the tenant and organization.
- GroupDescription: The description of the group.
- GroupDisplayName: The display name of the group.
- groupTypes: The types of the group.
- mailNickname: The mail nickname of the group.

This enriched dataset can then be used in security operations to better understand group memberships and enhance threat detection and response activities.

KQL Search