Nice shortcut in KQL to get JSON data in a dynamic column.

While looking at the SigninLogs table in Azure Sentinel I noticed there are a lot of dynamic fields that hold JSON data. I was trying to use parse_json to get to the data but it was always returning empty fields.

I then realized that parse_json requires a string input, not a dynamic. After some messing around I found that that I don’t need to do anything, I can access the data direction.

If you look at the DeviceDetail column, you will see if contains JSON like:

{“operatingSystem”:”Windows 10″,”deviceId”:”3cdd6ee4-c874-437b-a9fc-1a3daxxxxxxx”,”browser”:”IE 9.0″,”displayName”:”MC04611xxx”,”trustType”:”Hybrid Azure AD joined”,”isCompliant”:true,”isManaged”:true}

To get the operatingSystem, I could just type in something like:

extend newVariable = DeviceDetail.operatingSystem

Nice and easy!

There is a caveat to this though, it only works on straight JSON. If the column is an array of JSON, like the AuthenticationDetails column, it won’t work.

One thought on “Nice shortcut in KQL to get JSON data in a dynamic column.

  • I have a few fun little work arounds to the array issue. Extend all the fields you want in the array. Let me know if you would like to know more. We are still working on our GitHub page and always looking help out in the Sentinel Community.

    | extend authenticationStepDateTime_ = tostring(parse_json(AuthenticationDetails)[0].authenticationStepDateTime)
    | extend authenticationStepRequirement_ = tostring(parse_json(AuthenticationDetails)[0].authenticationStepRequirement)
    | extend authenticationStepResultDetail_ = tostring(parse_json(AuthenticationDetails)[0].authenticationStepResultDetail)
    | extend succeeded_ = tostring(parse_json(AuthenticationDetails)[0].succeeded) – JBUB (email)

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.