Forum Discussion

rsb47546's avatar
rsb47546
Icon for Microsoft rankMicrosoft
Aug 02, 2023

KQL on AuditLogs and SecurityIncident

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

KQL
siem

1 Reply

  • raphaelcustodiosoares's avatar
    raphaelcustodiosoares
    Iron Contributor
    Aug 03, 2023
    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/