Forum Discussion

hoangn5's avatar
hoangn5
Copper Contributor
Jun 06, 2019

How to get data from json column

For example i exec bellow query:   "MyVMLog | project ContentData"   And the result in each cell is json data like    "[{ "time": "2010-07-30T04:22:25.8051701Z", "category": "MyEventLogsTa...
azure monitor
Query Language
CliveWatson's avatar
CliveWatson
Former Employee
Jun 07, 2019

hoangn5 

 

Something like this should work: Go to Log Analytics and Run Query

 

It uses parse_json, in your case to read ContentData 

AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, getTxt.resourceLocation
hoangn5's avatar
hoangn5
Copper Contributor
Jun 10, 2019

thank you CliveWatson ,

in my case i want to take value from 'time' attribute in json as you can see in my example.
but when i try using parse_json path as '.time', the 'time' word will turn blue in my query as it is a keyword and when i exec i will got error.
Do you know how to solve it?

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jun 10, 2019

    hoangn5 

     

    You might need to put quotes around the keyword.  See amended example

     

    AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, getTxt.['resourceLocation']

    or

     

    AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, tostring(getTxt.resourceLocation)

    However time is a reserved keyword, so I suspect the above might not work.  Is the original data source changeable, "Time" with an upper case "T" would work?

Resources