Home

How to extract Json in AppInsights Analytics Query

%3CLINGO-SUB%20id%3D%22lingo-sub-192437%22%20slang%3D%22en-US%22%3EHow%20to%20extract%20Json%20in%20AppInsights%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-192437%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20following%20query%20in%20AppInsights%20Analytics%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%3CFONT%20face%3D%22Consolas%22%3Etraces%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20operation_Id%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'f3cd894d-5b92-45d4-12bd-08d5b6695afc'%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20itemType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'trace'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20timestamp%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E24%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%3CFONT%20face%3D%22Consolas%22%3E%7C%20top%20101%20by%20timestamp%20desc%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3EThis%20gives%20me%20data.%20Now%20I%20would%20like%20to%20parse%20the%20customDimensions%20field%20to%20extract%20one%20value.%20My%20custom%20dimension%20data%20looks%20like%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%7B%22%7BOriginalFormat%7D%22%3A%22Time%20taken%20to%20process%20validation%20message%20with%20operation%20Id%20-%20%7B0%7D%20is%20%3A%20%7B1%7D%20seconds%22%2C%22CategoryName%22%3A%22WebJob.Sample%22%2C%22ActivityID%22%3A%22f3cd894d-5b92-45d4-12bd-08d5b6695afc%22%2C%220%22%3A%22f3cd894d-5b92-45d4-12bd-08d5b6695afc%22%2C%221%22%3A%226.2814211%22%7D%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EI%20am%20writing%20a%20query%20to%20create%20an%20extra%20column%26nbsp%3Band%20populate%20the%20time(which%20is%20in%20%7B1%7D)%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%3CFONT%20face%3D%22Consolas%22%3Etraces%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20operation_Id%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'f3cd894d-5b92-45d4-12bd-08d5b6695afc'%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20itemType%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'trace'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20timestamp%20%26gt%3B%20ago(%3C%2FSPAN%3E%3CSPAN%3E24%3C%2FSPAN%3E%3CSPAN%3Eh)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20((((((((*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'Time'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'taken'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'to'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'process'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'validation'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'message'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'with'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'operation'%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20*%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'Id'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20duration%20%3D%20extractjson(%3C%2FSPAN%3E%3CSPAN%3E'%24.1'%3C%2FSPAN%3E%3CSPAN%3E%2C%20customDimensions)%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Etop%3C%2FSPAN%3E%20%3CSPAN%3E101%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20timestamp%20desc%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%20face%3D%22Consolas%22%3EBut%20it%20doesn't%20work...%20Any%20help%20will%20be%20really%20appreciated.%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-192437%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-193431%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Json%20in%20AppInsights%20Analytics%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-193431%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThis%20seems%20to%20be%20an%20issue%20of%20data%20types.%3C%2FP%3E%0A%3CP%3EIf%20your%20customDimensions%20field%20had%20been%20a%20String%2C%20the%20extractjson%20method%20you%20used%20would%20have%20worked%20well.%26nbsp%3BFor%20example%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fanalytics.applicationinsights.io%2FDemo%3Fq%3DH4sIAAAAAAAAA%252BWTTUvDQBCG74H8h2ER0kISsvlok4IHUYR60IOCB%252FGwyU7jarIbdrfVEvPf3eIHevNe5jTvfLzDA8OZdVF3OJOsx5WxWsg2hGej5I51229lDr734HtEY6M0pySEYCTjjRatkKy7VLpndiIrcid6BMteUIJVMGjVoDHgNgnOrFASepezFuFV2CdQA%252BpPec0hgjGZQBhYwUgnMM5JckNCcs4stkrvr92BzuIe6ytVx7esHzp05bPGip2w%252B%252FWFK26yhpdVzqOirtIoL3ge0bTmUVLyol4sqoJtGjeT%252FL%252BVutZFnJY0TyklUxD%252BUEiPjkJSlWmW%252F4GQHRmEZewILJPKQfC9R997B3yzKDnw7df9pwdFs8YefmgWnMQ0%252BPVPc9%252F7ACjRFmZzAwAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esee%20this%20query%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EHowever%2C%20your%20customDimensions%20field%20is%20of%20type%20dynamic%2C%20so%20you%20should%20instead%20use%20the%20parsejson%20method%20%3CA%20href%3D%22https%3A%2F%2Fanalytics.applicationinsights.io%2FDemo%3Fq%3DH4sIAAAAAAAAAyspSkxOLeblqlEoz0gtSlVILi0uyc91ycxNzSvOzM8rVlC0VVBSAsmXJGanKhgagJgFRflZqcklGIp1FBzTU%252FNKwlKLQFxbdOloJWRppVgAU6vAqnwAAAA%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eas%20shown%20here%3C%2FA%3E.%20In%20your%20case%20it%20should%20be%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CPRE%3E...%20%7C%20extend%20duration%20%3D%20customDimensions%5B%221%22%5D%3C%2FPRE%3E%0A%3C%2FDIV%3E%0A%3CP%3EAs%20for%20the%20text%20search%2C%20a%20more%20efficient%20solution%20would%20be%20something%20like%20this%3A%3C%2FP%3E%0A%3CPRE%3E...%20%7C%20where%20customDimensions%5B%22%7BOriginalFormat%7D%22%5D%20contains%20%22Time%20taken%20to%20process%20validation%20message%20with%20operation%20Id%22%3C%2FPRE%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E
Subhendu De
Occasional Visitor

I am using following query in AppInsights Analytics

traces
| where operation_Id == 'f3cd894d-5b92-45d4-12bd-08d5b6695afc'
| where itemType == 'trace' and timestamp > ago(24h)
| top 101 by timestamp desc
 
This gives me data. Now I would like to parse the customDimensions field to extract one value. My custom dimension data looks like

{"{OriginalFormat}":"Time taken to process validation message with operation Id - {0} is : {1} seconds","CategoryName":"WebJob.Sample","ActivityID":"f3cd894d-5b92-45d4-12bd-08d5b6695afc","0":"f3cd894d-5b92-45d4-12bd-08d5b6695afc","1":"6.2814211"}

 
I am writing a query to create an extra column and populate the time(which is in {1})
 
traces
| where operation_Id == 'f3cd894d-5b92-45d4-12bd-08d5b6695afc'
| where itemType == 'trace' and timestamp > ago(24h)
| where ((((((((* has 'Time' and * has 'taken') and * has 'to') and * has 'process') and * has 'validation') and * has 'message') and * has 'with') and * has 'operation') and * has 'Id')
| extend duration = extractjson('$.1', customDimensions)
| top 101 by timestamp desc
 
But it doesn't work... Any help will be really appreciated.
1 Reply

Hi,

This seems to be an issue of data types.

If your customDimensions field had been a String, the extractjson method you used would have worked well. For example, see this query.

However, your customDimensions field is of type dynamic, so you should instead use the parsejson method as shown here. In your case it should be:

... | extend duration = customDimensions["1"]

As for the text search, a more efficient solution would be something like this:

... | where customDimensions["{OriginalFormat}"] contains "Time taken to process validation message with operation Id"

HTH,

Noa