KQL on AuditLogs and SecurityIncident

Microsoft

Hi, I was wondering if I can correlate an event in AuditLogs to an incident in SecurityIncidents table. I am trying to write a KQL query to join these 2 tables but I don't see any common columns between them. For example, my analytic query is creating an incident for a "User Assigned Privileged Role" event. After this incident is created, I want to join the 2 tables

1 Reply
Hello

maybe you can extract the username in the TargetResources column
in the audit log and relate to SecurityAlerts with the entities field in the Displayname field

or use the query below to find out when you activated the pim

AuditLogs
| where TimeGenerated between(Aug(14d)..Aug(1d))
| where OperationName =~ "Add member to role completed (PIM activation)"
| where Result =~ "success"
| extend ElevatedUser = tostring(TargetResources[2].userPrincipalName)
| extend displayName = tostring(TargetResources[0].displayName)
| extend displayName2 = tostring(TargetResources[3].displayName)
| extend ElevatedRole = iif(displayName =~ "Member", displayName2, displayName)
| join kind = rightanti(AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName =~ "Add member to role completed (PIM activation)"
| where Result =~ "success"
| extend ElevatedUser = tostring(TargetResources[2].userPrincipalName)
| extend displayName = tostring(TargetResources[0].displayName)
| extend displayName2 = tostring(TargetResources[3].displayName)
| extend ElevatedRole = iif(displayName =~ "Member", displayName2, displayName)
| extend ElevatedBy = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)) on ElevatedRole, ElevatedUser
| project-reorder ElevatedUser, ElevatedRole, ResultReason, ElevatedBy



if you liked it mark the answer with a like.
if you thought this answer helped in any way please mark it as best answer

Follow me: https://www.linkedin.com/in/raphael-custodio-soares/