Kusto KQL reference first object in an JSON array

Question

I need to grab the value of the first entry in a json array with Kusto KQL in Microsoft Defender ATP.

The data format looks like this (anonymized), and I want the value of "UserName":

[{"UserName":"xyz","DomainName":"xyz","Sid":"xyz"}]

How do I split or in any other way get the "UserName" value?

Answer 1

In WDATP/MSTAP, for the "LoggedOnUsers" type of arrays, you want "mv-expand" (multi-value expand) in conjunction with "parsejson".

"parsejson" will turn the string into JSON, and mv-expand will expand it into LoggedOnUsers.Username, LoggedOnUsers.DomainName, and LoggedOnUsers.Sid:

DeviceInfo 
| mv-expand parsejson(LoggedOnUsers)
| project DeviceName, LoggedOnUsers.UserName, LoggedOnUsers.DomainName

Keep in mind that if the packed field has multiple entries (like DeviceNetworkInfo's IPAddresses field often does), the entire row will be expanded once per entry - so a row for a machine with 3 entries in "IPAddresses" will be duplicated 3 times, with each different expansion of IpAddresses:

DeviceNetworkInfo 
| where Timestamp > ago(1h)
| mv-expand parsejson(IPAddresses)
| project DeviceName, IPAddresses.IPAddress