Expand TargetResources in AuditLogs

Question

Expand TargetResources in AuditLogs

Markus Schädlich 70

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. 2024-08-02 11_59_34-Benutzer wurde erstellt oder die Gruppenzugehörigkeit entfernt _ letzte Woche -

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

Ajay Kumar N 28,261 Reputation points Microsoft Employee Moderator

2024-08-05T10:49:05.4366667+00:00

Apologies for the delayed response. While we are investigating, could you please share more details about your requirement?
Markus Schädlich 70 Reputation points

2024-08-05T11:24:02.32+00:00

i want to expand the informations in the TargetResources column to display the values of displayName and UserPrincipalName in a separate row like "Activity Display Name"
Markus Schädlich 70 Reputation points

2024-08-09T12:00:45.4933333+00:00

thank you, it works.
Sadiqh Ahmed 49,566 Reputation points Microsoft External Staff Moderator

2024-08-09T18:36:45.76+00:00

@Markus Schädlich Glad to know it worked. I would request you to "Accept Answer and Upvote" so that the relevancy of this post will improve when anyone in the community search for a similar query.

Answer accepted by question author

0 additional answers

Your answer

Ajay Kumar N 28,261 Reputation points Microsoft Employee Moderator

2024-08-05T10:49:05.4366667+00:00

Apologies for the delayed response. While we are investigating, could you please share more details about your requirement?
Markus Schädlich 70 Reputation points

2024-08-05T11:24:02.32+00:00

i want to expand the informations in the TargetResources column to display the values of displayName and UserPrincipalName in a separate row like "Activity Display Name"
Markus Schädlich 70 Reputation points

2024-08-09T12:00:45.4933333+00:00

thank you, it works.
Sadiqh Ahmed 49,566 Reputation points Microsoft External Staff Moderator

2024-08-09T18:36:45.76+00:00

@Markus Schädlich Glad to know it worked. I would request you to "Accept Answer and Upvote" so that the relevancy of this post will improve when anyone in the community search for a similar query.

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

Share via

Expand TargetResources in AuditLogs

0 additional answers

Your answer