Application Insights Extract Nested CustomDimensions

Question

I have some data in Application Insights Analytics that has a dynamic object as a property of custom dimensions. For example:

|        timestamp        |  name   | customDimensions                 | etc |
|-------------------------|---------|----------------------------------|-----|
| 2017-09-11T19:56:20.000 | Spinner | {                                | ... |
                                         MyCustomDimension: "hi"
                                         Properties:
                                             context: "ABC"
                                             userMessage: "Some other"
                                      }

Does that make sense? So a key/value pair inside of customDimensions.

I'm trying to bring up the context property to be a proper column in the results. So expected would be :

|        timestamp        |  name   | customDimensions                 | context| etc |
|-------------------------|---------|----------------------------------|--------|-----|
| 2017-09-11T19:56:20.000 | Spinner | {                                | ABC    | ...
                                         MyCustomDimension: "hi"
                                         Properties:
                                             context: "ABC"
                                             userMessage: "Some other"
                                      }

I've tried this:

customEvents | where name == "Spinner" | extend Context = customDimensions.Properties["context"]

and this:

customEvents | where name == "Spinner"  | extend Context = customDimensions.Properties.context

but neither seem to work. They give me a column at the end named "Context" but the column is empty - no values.

Any ideas?

EDIT:

Added a picture for clarifying the format of the data:

score 50 · Accepted Answer · edited Jun 20 '20 at 09:12

edited to working answer:

customEvents
 | where name == "Spinner"
 | extend Properties = todynamic(tostring(customDimensions.Properties))
 | extend Context = Properties.context

you need an extra tostring and todynamic in here to get what you expect (and what i expected!)

the explanation i was given:

Dynamic field "promises" you the upper/outer level of key / value access (this is how you access customDimensions.Properties).

Accessing internal structure of that json depends on the exact format of customDimensions.Properties content. It doesn’t have to be json by itself. Even if it looks like a well structured json, it still may be just a string that is not exactly well formatted json.

So basically, it by default won't attempt to parse strings inside of a dynamic/json block because they don't want to spend a lot of time possibly trying and failing to convert nested content to json infinitely.

I still think that extra tostring shouldn't be required inside there, since todynamic should already be allowing both string and dynamic in validly, so i'm checking to see if the team that owns the query stuff can make that step better.

Spot on - Properties does not contain a well-formatted JSON, so it can't be parsed to a dynamic value — EranG, Sep 12 '17 at 03:27
John and @EranG - my bad on the representation of the data in Stack Overflow. It is indeed well-formatted JSON. I've attached a picture in the original question so you can better see what I'm seeing in the Analytics portal. — Seafish, Sep 12 '17 at 15:55

score 5 · Answer 2 · answered Jan 18 '18 at 01:11

5

Thanks sooo much.. just to expand on the answer from John. We needed to graph duration of end-points using custom events. This query made it so we could specify the duration as our Y-axis in the chart:

customEvents
 | extend Properties = todynamic(tostring(customDimensions.Properties))
 | extend duration = todouble(todecimal(Properties.duration))
 | project timestamp, name, duration

answered Jan 18 '18 at 01:11

Richard Lyle

325
3
10

1

aside: if you're storing numeric values, you should put them into customMeasurements (metrics) instead of properties (you can send them the same way you do custom properties, you don't need to use `trackMetric`). then you can use them in other tools like Metrics Explorer. – John Gardner Apr 16 '18 at 17:26

Application Insights Extract Nested CustomDimensions

2 Answers2

Linked