Expand TargetResources in AuditLogs

Question

hi, i want to expand the TargetResources to display the values of displayName and UserPrincipalName in a separate row.

Or is there another way to monitor the group assignments with the TableAuditLos? We need a KQL when a user was added or removed from a group.

this is the KQL at the moment:

AuditLogs
| where OperationName contains "Add member to group" 
or OperationName  == "Add user"
or OperationName  == "Remove member from group"
| extend prop = parse_json(InitiatedBy)
| extend InitialedBy=prop.user.userPrincipalName
|project InitialedBy , TimeGenerated,OperationName,Category,InitiatedBy,Result, TargetResources, ActivityDisplayName

Answer 1

@Markus Schädlich Sorry for the delayed response.

To expand the TargetResources field in your KQL query to display the values of displayName and UserPrincipalName in separate rows, you can use the mv-expand operator to turn the array of JSON objects in TargetResources into separate rows. Here's an updated version of your KQL query:

AuditLogs
| where OperationName contains "Add member to group" or OperationName == "Add user" or OperationName == "Remove member from group"
| extend TargetResources = parse_json(TargetResources)
| mv-expand TargetResources
| extend DisplayName = tostring(TargetResources.displayName), UserPrincipalName = tostring(TargetResources.userPrincipalName)
| project TimeGenerated, OperationName, Category, Result, DisplayName, UserPrincipalName, ActivityDisplayName

This query will give you a row for each displayName and UserPrincipalName in the TargetResources array, along with the other fields you've selected.

If you need to monitor specific group assignments and track changes more comprehensively, consider including additional details and filters based on your requirements, such as specific groups or users. The KQL query above provides a starting point, and you can adjust it based on the specific structure of your TargetResources and the information you need to track.

Hope this helps!

---

If the response helped, do "Accept Answer" and up-vote it