Azure App Insights KQL - How can I perform an aggregate sum of nested data?

Question

Azure Application Insights uses the Kusto Query Language (KQL) that is clearly quite powerful, but I cannot seem to get it to aggregate nested data.

The best way to explain this is through an example. My actual situation uses different data, but has the same problem that I will explain here. Using the Azure Data Explorer there is a StormEvents table that has a State property, a StormSummary property, and many more. The StormSummary property is JSON that looks like:

{"TotalDamages":0,"StartTime":"2007-09-18T20:00:00.0000000Z",...}

I can do a query such as:

StormEvents 
| project State, StormSummary.TotalDamages

That gives me a nice tabular result. However, what I really want is to aggregate the total damages for each state, so I want something like:

StormEvents 
| project State, sum(StormSummary.TotalDamages)

Unfortunately, the above query fails with:

Function 'sum' cannot be invoked in current context.

My end goal is to render this in a pie chart to show total damages for each state, but I can't get the sum of the damages. I'm using App Insights to create data with the same problem as this. Maybe if I structure my data differently it would help. I am using Track Event and providing a number as a property on the event. I could use a Metric instead, but the documentation indicates I should use an Event since I am not aggregating the metric myself.

As a point of reference, the following works if I do a count of the records by state, but I want a sum of the total damages by state.

StormEvents 
| summarize Count=count() by State
| render piechart

Yoni L. Yoni L. · Accepted Answer · 2020-07-14T21:22:47

instead of this:

StormEvents 
| project State, sum(StormSummary.TotalDamages)

you could try this:

StormEvents 
| summarize sum(tolong(StormSummary.TotalDamages)) by State

Azure App Insights KQL - How can I perform an aggregate sum of nested data?

1 Answers