CODEWITHSUNDEEP
azurebotframeworkazure-application-insights
Stuart Brown
Stuart Brown

Reputation: 987

Azure Application Insights - values within objects

I'm trying to get my head around writing queries in Azure Application Insights which is capturing interactions with a bot built using Azure Bot Framework.

I have a table with headings such as timestamp, name, customDimensions, customDimensions and within customDimensions are objects such as

{ "conversationData": "{}", "privateConversationData": "{\"nameForm\":{\"NAME\":\"foo\",\"ICCID\":\"12121212121212121212\"}}", "userData": "{}", "conversationId": "878fhiee1k33j5ci", "userId": "default-user", "metrics": "92.25833" }

I can write queries easily to select items by name for example customEvents | where name contains "Activity"

but how do I select based on keys within objects such as those within privateConversationData above?

For example "privateConversationData": "{\"nameForm\":{\"NAME\":\"foo\",\"ICCID\":\"12121212121212121212\"}}", refers to one dialog called nameForm, how would I write a query to show the number of times the nameForm was used? Or a query that included the other kinds of dialog (e.g. not just nameForm, but fooForm, barForm) and a count of the times they were used?

Many thanks for any help!

Upvotes: 1

Views: 1195

Answers (1)

yonisha
yonisha

Reputation: 3096

The 'customDimensions' property is a dynamic type and therefore can be treated as a JSON document.

For example - to get the number of times nameForm was used in the last day:

customEvents
| extend conversationData = customDimensions["privateConversationData"]
| where timestamp > ago(1d) and isnotempty(conversationData) and conversationData contains "{\\\"nameForm\\\""
| count

Getting the different dialogs count will be trickier, but possible by parsing the customDimensions JSON document using the parse operator:

customEvents
| where timestamp > ago(1d) 
| parse customDimensions with * "privateConversationData\": \"{\\\"" dialogKind "\\\":{\\\"NAME\\\"" *
| where isnotempty(dialogKind) and isnotnull(dialogKind)
| summarize count() by dialogKind

You can read the Analytics Reference to learn more about the language.

Upvotes: 1

Related Questions