Applied KQL

Helping others get things done using Kusto Query Language (KQL).

KQL query: Get Active Directory group membership changes

by

A digital art image of an artistic interpretation of a KQL query result which listed Active Directory group membership changes. Generated by Copilot Designer.
Active Directory group membership changes. Generated by Copilot Designer.

Get users and the groups they were added or removed from, including who made the change.

Prerequisites for using this KQL query

The Microsoft Defender for Identity Domain controller Sensor needs to be installed on your domain controllers. Perform query using Microsoft Defender > Hunting > Advanced hunting.

KQL query to get Active Directory group membership changes

IdentityDirectoryEvents
| where Application == "Active Directory"
| where ActionType == "Group Membership changed"
| where DestinationDeviceName != "" // Exclude activites coming AD Sync changes. 
| extend ToGroup = tostring(parse_json(AdditionalFields).["TO.GROUP"]) // Extracts the group name if action is add enity to a group.
| extend FromGroup = tostring(parse_json(AdditionalFields).["FROM.GROUP"]) // extracts the group name if action is remove entity from a group.
| extend Action = iff(isempty(ToGroup), "Remove", "Add") // calculates if the action is Remove or Add
| extend GroupModified = iff(isempty(ToGroup), FromGroup, ToGroup) // group name that the action was taken on 
| extend Target_Group = tostring(parse_json(AdditionalFields).["TARGET_OBJECT.GROUP"])
| project Timestamp, Action, GroupModified,  Target_Account = TargetAccountDisplayName, Target_UPN = TargetAccountUpn, Target_Group,  DC=DestinationDeviceName, Actor=AccountName, ActorDomain=AccountDomain, AdditionalFields
| sort by Timestamp desc 

// credit: Gerson Levitz (techcommunity.microsoft.com)

This will return a result set with columns Timestamp, Action, GroupModified, Target_Account, Target_UPN, Target Group, DC, Actor, ActorDomain, and an array of AdditionalFields.

Deconstructing the KQL query

Read on to understand HOW the query above works. 

The intent of this query is to retrieve and display a list of group membership changes within Active Directory, specifically focusing on the actions of adding or removing entities from groups.

IdentityDirectoryEvents

This is the data table that contains events related to identity directories.

| where Application == "Active Directory"

Filters the events to include only those related to “Active Directory”.

| where ActionType == "Group Membership changed"

Further narrows down the events to those where the action type indicates a change in group membership.

| where DestinationDeviceName != ""

Excludes events that originate from AD Sync changes by ensuring the DestinationDeviceName field is not empty.

| extend ToGroup = tostring(parse_json(AdditionalFields).["TO.GROUP"])

Creates a new field called ToGroup by extracting the group name to which an entity was added, if applicable.

| extend FromGroup = tostring(parse_json(AdditionalFields).["FROM.GROUP"])

Similarly, creates a new field called FromGroup by extracting the group name from which an entity was removed, if applicable.

| extend Action = iff(isempty(ToGroup), "Remove", "Add")

Determines the action taken—either “Remove” or “Add”—based on whether the ToGroup field is empty.

| extend GroupModified = iff(isempty(ToGroup), FromGroup, ToGroup)

Identifies the group that was modified by the action, using FromGroup if ToGroup is empty, and vice versa.

| extend Target_Group = tostring(parse_json(AdditionalFields).["TARGET_OBJECT.GROUP"])

Extracts the target group affected by the action.

| project Timestamp, Action, GroupModified, Target_Account = TargetAccountDisplayName, Target_UPN = TargetAccountUpn, Target_Group, DC=DestinationDeviceName, Actor=AccountName, ActorDomain=AccountDomain, AdditionalFields

Selects and renames the fields to be displayed in the final output.

| sort by Timestamp desc

Sorts the results in descending order by the Timestamp, showing the most recent events first.

Reference