Enrich table with entities from security incident

%3CLINGO-SUB%20id%3D%22lingo-sub-3351626%22%20slang%3D%22en-US%22%3EEnrich%20table%20with%20entities%20from%20security%20incident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3351626%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20extra%20colum%20for%20entity%20data%20from%20the%20incident%2C%20like%20host%2C%20ip%20and%20account.%20But%20i%20can't%20seem%20to%20get%20it%20working.%20What%20do%20i%20need%20to%20add%20to%20this%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESecurityIncident%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BSeverity%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3ESeverity%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22*%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3ESeverity%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTactics%26nbsp%3B%3D%26nbsp%3Btodynamic(AdditionalData.tactics)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTactics%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3ETactics%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22*%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3ETactics%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BOwner%26nbsp%3B%3D%26nbsp%3Btodynamic(Owner.assignedTo)%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BOwner%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3EOwner%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22*%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3EOwner%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BProduct%26nbsp%3B%3D%26nbsp%3Btodynamic((parse_json(tostring(AdditionalData.alertProductNames))%5B%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%5D))%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BProduct%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3EProduct%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22*%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ein%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B(%3C%2FSPAN%3E%3CSPAN%3E%7B%3C%2FSPAN%3E%3CSPAN%3EProduct%3C%2FSPAN%3E%3CSPAN%3E%7D%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eorder%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BLastModifiedTime%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BLastModifiedTime%2CIncidentNumber%2C%26nbsp%3BTitle%2C%26nbsp%3BStatus%2C%26nbsp%3BSeverity%2C%20Tactics%2C%20Classification%2C%26nbsp%3BClassificationReason%2CClassificationComment%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etake%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E250%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThanks%20in%20advance%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3351626%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAlerts%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Collection%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3352109%22%20slang%3D%22en-US%22%3ERe%3A%20Enrich%20table%20with%20entities%20from%20security%20incident%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3352109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385669%22%20target%3D%22_blank%22%3E%40MartijnZwart%3C%2FA%3E%26nbsp%3Bexample%20for%20IP%20entity%2C%20which%20may%20give%20you%20a%20clue.%26nbsp%3B%20As%20you%20are%20doing%20this%20for%20a%20Workbook%2C%20take%20a%20look%20at%20the%20%22Investigation%20Insights%22%20built-in%20template%20which%20has%20this.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESecurityIncident%0A%7C%20summarize%20arg_max(TimeGenerated%2C*)%20by%20IncidentNumber%0A%7C%20extend%20Alerts%20%3D%20extract(%22%5C%5C%5B(.*%3F)%5C%5C%5D%22%2C%201%2C%20tostring(AlertIds))%0A%7C%20mv-expand%20AlertIds%20to%20typeof(string)%0A%7C%20join%20%0A(%0A%20%20%20%20SecurityAlert%0A%20%20%20%20%7C%20extend%20AlertEntities%20%3D%20parse_json(Entities)%0A%20%20%20%20%7C%20mv-expand%20AlertEntities%0A%20%20%20%20%7C%20where%20isnotempty(AlertEntities)%0A%20%20%20%20%7C%20where%20AlertEntities.Type%20%3D%3D%20%22ip%22%0A%20%20%20%20%7C%20extend%20EntityIP%20%3D%20tostring(AlertEntities.Address)%0A%20%20%20%20%7C%20extend%20EntityType%20%3D%20tostring(AlertEntities.Type)%0A)%20on%20%24left.AlertIds%20%3D%3D%20%24right.SystemAlertId%0A%7C%20project%20IncidntName%20%3D%20Title%2C%20IncidentNumber%3DIncidentNumber%2C%20%20EntityIP%2C%20EntityType%2C%20AlertId%20%3D%20AlertIds%2C%20AlertName%20%3D%20AlertName%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

Hi,

 

I want to create a extra colum for entity data from the incident, like host, ip and account. But i can't seem to get it working. What do i need to add to this query?

 

SecurityIncident
where Severity in ({Severity}or "*" in ({Severity})
extend Tactics = todynamic(AdditionalData.tactics)
where Tactics in ({Tactics}or "*" in ({Tactics})
extend Owner = todynamic(Owner.assignedTo) 
where Owner in ({Owner}or "*" in ({Owner})
extend Product = todynamic((parse_json(tostring(AdditionalData.alertProductNames))[0])) 
where Product in ({Product}or "*" in ({Product})
order by LastModifiedTime 
project LastModifiedTime,IncidentNumber, Title, Status, Severity, Tactics, Classification, ClassificationReason,ClassificationComment
take 250
 
Thanks in advance
1 Reply

@MartijnZwart example for IP entity, which may give you a clue.  As you are doing this for a Workbook, take a look at the "Investigation Insights" built-in template which has this. 

 

SecurityIncident
| summarize arg_max(TimeGenerated,*) by IncidentNumber
| extend Alerts = extract("\\[(.*?)\\]", 1, tostring(AlertIds))
| mv-expand AlertIds to typeof(string)
| join 
(
    SecurityAlert
    | extend AlertEntities = parse_json(Entities)
    | mv-expand AlertEntities
    | where isnotempty(AlertEntities)
    | where AlertEntities.Type == "ip"
    | extend EntityIP = tostring(AlertEntities.Address)
    | extend EntityType = tostring(AlertEntities.Type)
) on $left.AlertIds == $right.SystemAlertId
| project IncidntName = Title, IncidentNumber=IncidentNumber,  EntityIP, EntityType, AlertId = AlertIds, AlertName = AlertName