Forum Discussion
hoangn5
Jun 06, 2019Copper Contributor
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": "MyEventLogsTable",
"level": "Informational"
}]"
So how can i get only 'time' field for each cell after query so my result will be like
Time
2010-07-30T04:22:25.8051701Z
2012-09-30T04:22:25.8051701Z
....
- CliveWatson
Microsoft
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
- hoangn5Copper Contributor
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
Microsoft
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?