I would like to use KQL to parse through the Security Incidents table in Azure Sentinel and add custom columns for things such as Category, Control etc which would be based on tags (unless there is a better way). This would all be then presented in a workbook providing a list of incidents along with additional metadata.
I have some KQL to add fields based on a key value pair I'm placing in tags, however I'm seeing one record per tag when displaying the data, rather than one entry per incident with the additional fields.
How could I join these entries up to see the most recent set of data for each incident with all relevant columns populated?
SecurityIncident
| summarize arg_max(LastModifiedTime, *) by IncidentNumber
| mv-expand Labels
| extend Category = bag_pack(tostring(split(Labels.labelName,':')[0]), tostring(split(Labels.labelName,':')[1]))['Category']
| extend ExpControl = bag_pack(tostring(split(Labels.labelName,':')[0]), tostring(split(Labels.labelName,':')[1]))['ExpControl']
| project IncidentNumber, Category, ExpControl
The output:
| Incident Number | Category | ExpControl |
|---|---|---|
| 123456 | SocialEngineering | |
| 123456 | Email Security |
UPDATE: I have something that fits the bill now, although it's highly likely not the most appropriate way to do this. I would certainly appreciate any advice on better ways to achieve the same thing:
SecurityIncident
| where Labels has "Category:"
| mv-expand Labels
| extend Category = bag_pack(tostring(split(Labels.labelName,':')[0]), tostring(split(Labels.labelName,':')[1]))['Category']
| summarize arg_max(LastModifiedTime, *) by IncidentNumber
| join kind=inner
( SecurityIncident
| where Labels has "ExpControl:"
| mv-expand Labels
| extend ["Expected Control"] = bag_pack(tostring(split(Labels.labelName,':')[0]), tostring(split(Labels.labelName,':')[1]))['ExpControl']
) on IncidentName
| summarize arg_max(LastModifiedTime, *) by IncidentNumber
| project IncidentNumber, Category, ["Expected Control"], CreatedTime, ClosedTime, Classification
Results: |IncidentNumber|Category|Expected Control|CreatedTime|ClosedTime|Classification| |---|---|---|---|---|---| |123456|Social Engineering|Email Protection|06/06/2023 01:23:45.678|06/06/2023 01:53:45.678|Undetermined|