Parse Json Array in KQL

Question

Json text isn't parsing in KQL correctly. I tried using parse_json as well but that didn't work either. I did confirm the extend AllProperties is holding the correct data.

DeviceInfo
| where RegistryDeviceTag == "Standard"
| extend AllProperties = todynamic(LoggedOnUsers)
| project DeviceName, Users = AllProperties["Username"]

Output gives me the correct DeviceName but doesn't give any data in the Username field.

It may help it you would provide a sample value or 2 that aren't being parsed according to your expectations — Yoni L.
– Yoni L., Commented Jul 23, 2021 at 15:32
Yes. So in LoggedOnUsers you're getting this output: [{"UserName":"TheUserName","DomainName":"TheDomainName","Sid":"TheSID#"}]. I'm only looking to split Username into it's own column. — John418
– John418, Commented Jul 27, 2021 at 20:25

Yoni L. · Accepted Answer · 2021-07-28 15:10:28Z

2

(based on the sample input you provided in the comment)

if the array that is "LoggedOnUsers" includes exactly one entry, you can do this:

print input = '[{"UserName":"TheUserName","DomainName":"TheDomainName","Sid":"TheSID#"}]'
| project UserName = parse_json(input)[0].UserName

otherwise, you can use mv-expand or mv-apply:

print input = '[{"UserName":"TheUserName","DomainName":"TheDomainName","Sid":"TheSID#"}]'
| project parse_json(input)
| mv-apply input on (
    project UserName = input.UserName
)

answered Jul 28, 2021 at 15:10

Yoni L.

26.4k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse Json Array in KQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related