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.

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.