KQL Pattern - Pull out value from Array

JSON Key and Value in Array never in same index

A quick pattern I want to discuss in todays article is around retrieving a value in an array, when the value may not be in the same index. It happens very often you have data in an array [ ] and that data does not stay in the same index spot [1] in each row of data. This can certainly be common when retrieving a array of tags on a Azure or AWS resource. These tags may not be applied in a consistent manner across the resources in your cloud.

an example from AWS tags being pulled in CloudTrail Logs

[{
	"key": "titan app Server",
	"value": "titan app Server 01"
}, {
	"key": "resourceowner",
	"value": "amangee"
}, {
	"key": "deployed_by",
	"value": "cloudops"
}, {
	"key": "owner",
	"value": "srini"
}, {
	"key": "Name",
	"value": "app01server"
}, {
	"key": "group_name",
	"value": "titan app Server"
}]

To help aid in this you can use the following KQL pattern to dynamic array and then search in the array for a key and project the keys value

GuardDutyTest1_CL
| where TimeGenerated >=ago(30d)
| extend AWSTags = iff(isempty(tostring(parse_json(tostring(parse_json(tostring(parse_json(ResourceDetails_s).instanceDetails)).tags)))), todynamic('[{"dummy" : ""}]'), todynamic(tostring(parse_json(tostring(parse_json(tostring(parse_json(ResourceDetails_s).instanceDetails)).tags)))))
| mv-apply AWSTags on (
    where AWSTags.key == "owner"
    | extend owner_ = AWSTags.value
)

Lets break down each line in the KQL statement

In line 3 we are extending a new column AWSTags to be created and parsing our nested jsons to the Tags data array | ResourceDetails_s { } → instanceDetails { } → tags [ ]

In line 4 we are using mv-apply on the newly extended column data array AWSTags on where key equals owner and then extending a new column owner_ on the value of that key. Mv-apply KQL command applies a subquery to each record, and returns the union of the results of all subqueries.

The end result looks something like this.

With this KQL pattern you can now extract out a value from an array of keys and pairs typically found in a data array for Resource Tags in AWS or Azure, especially when that pair key:value is not in the same index spot in the data array.

Now with the owner tag in a unique column this can be passed as an entity in Microsoft Sentinel or passed into further automation, like a AAD Lookup of details of the owner account found in the resource tag.