Extracting Nested Fields in Kusto

by

PSST: you could read this post. Orrrr you could check out the new 2.0 version, which has way more examples. Extracting Nested Fields in Kusto 2.0 – Cloud, Systems Management and Automation (cloudsma.com)

 

 

Pretty much every blog I’ve produced is a real world solution that I have used in production. Sometimes they are solutions I have worked on recently, sometimes months or even a year has gone by before I finally wrote it down. Sometimes blogging and a real world solution occur at the exact same time. I was already working on the examples of extracting nested fields with Kusto when a coworker had asked about extracting fields out of a custom log that was being sent for an application.

Sometimes in Log Analytics, Azure Resource Graph, Azure Sentinel, pretty much anything that uses Kusto, you will have nested fields. These are XML, sometimes they are JSON. Either way you may want the data contained within this nested field. There are a few ways of extracting these nested fields with Kusto, depending on which product you are using.

Quick and Dirty Method

This first method works best for nested JSON fields. Its also useful if you only need to extract a few fields, or in the examples I’ll show below, when you are using Azure Resource Graph. Azure Resource Graph doesn’t support the evaluate operator that I’ll show below.

Lets grab all our IaaS disks with this simple query.

Resources | where type contains "microsoft.compute/disks"
Looking at the right hand side if you click on “see details,” we can see there are a number of nested fields, that are JSON formatted.
Extracting Nested Fields Kusto
Under ‘properties’ are a number of fields that we can grab. In this case I’ll get the OS Type, by using Extend to create a new Os field.

Resources | where type contains "microsoft.compute/disks"
| extend Os=properties.osType
Much like Powershell we can get objects with “object dot objectname”. In this case properties.osType.
Even more interesting, we don’t have to use extend, we can use project.
Resources | where type contains "microsoft.compute/disks"
| project Os=properties.osType, 
		  DiskSku=sku.name, 
		  DiskSizeGB=properties.diskSizeGB, 
		  id = managedBy

Extracting Nested Fields Kusto

This will work in Log Analytics as well, but since Azure Resource Graph doesn’t have all the available operators and scalar functions, this method is how you do it in Resource Graph.

Evaluate Bag_Unpack

Next up is bag_unpack(), this might be my favorite method. This method works in Log Analytics and ostensibly Azure Sentinel. I haven’t tried it in Application Insights, but I would bet it works there as well. Bag_unpack works with JSON and will extract all fields in the nested object and make them they’re own fields. This is what I ended up recommending my coworker to use on their custom logs.

Looking at our Azure AD Sign In logs there are a few nested fields. The one I’ll be tarting is DeviceDetail.


SigninLogs
| project DeviceDetail
| evaluate bag_unpack(DeviceDetail)

Granted there’s only 3 key value pairs in here, but rest assured in our clients application logs the amount of fields it needed to extract was a lot. And each record had different fields, so using the first method would be rather time consuming.

It would be great if evaluate bag_unpack became available in Azure Resource Graph. I have provided the feedback through the Azure portal. If you could spare a minute it would be great if you could too.

Parse_XML

This is my least favorite method, my recommendation would be to not put nested XML into Kusto. I don’t even know if you can send to the public API but I’m not going to try.

That said there are logs that contain XML, and they’re from the Windows Event Log. Yes the logs come in already parsed but the XML field is still there.

Projecting just the EventData field we can see the XML that we’re working with.


Event
| project EventData
| extend NewField=parse_xml(EventData)

You can see this becomes quite messy. I couldn’t get the parse_xml example at the bottom of the page to work with these logs.

But, what I did find was you can call the element of the array.


Event
| project EventData
| extend NewField=parse_xml(EventData)
| extend Extract=NewField.DataItem.EventData.Data[5]

you can then use evaluate bag_unpack to extract the JSON values.

 


Event
| project EventData
| extend NewField=parse_xml(EventData)
| extend Extract=NewField.DataItem.EventData.Data[5]
| evaluate bag_unpack(Extract)


You can see after using bag_unpack, the Extract field goes away and only its contents remain in new fields.

Honorable Mentions

You may be asking why didn’t I demo mv-expand. Because its limited to two fields only. Bag_unpack or the quick and dirty method work better in my opinion. Another scalar function is bag_keys, which gets all the keys in a dynamic object.