I am fairly early on in my KQL journey and I have set myself a task.
I want to create a query that detects when a new group is created and then a new user is added to said group.
I can get the info I want for each operation easily but how to combine these? Is it even possible?
I have been playinh about with Unions and Joins etc but this one is beyond me.
If anyone could take the time ot asssit I would really appreciate it
AuditLogs | where OperationName in ('Add member to group', 'Add owner to group') | extend GroupName = tostring(TargetResources[0].displayName) | extend RoleId = tostring(AdditionalDetails[0].value) | extend UserAdded = tostring(TargetResources[2].displayName) | extend ObjectId = tostring(TargetResources[2].id) | extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName) | extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress) | where Identity != "Microsoft Teams Services"`
AuditLogs
| where OperationName has "Add Group"
| extend GroupName = tostring(TargetResources[0].displayName)
| extend RoleId = tostring(AdditionalDetails[0].value)
| extend UserAdded = tostring(TargetResources[2].displayName)
| extend ObjectId = tostring(TargetResources[2].id)
| extend UserPrincipalName = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend IPAddress = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
| union isfuzzy = AuditLogs
| where OperationName in ('Add member to group', 'Add owner to group') ) on Identity
