Azure Query Analytics average all values in column

user_10140546 0
Viewed 1272

I am using application insights to record custom measurements about our application. I have a customEvent that has data stored in the customMeasurements object. The object ontains 4 key-value pairs. I have many of these customEvents and I am trying to average the key-value pairs from all the events and display the results in a 2 column table.

I want to have one table that has 2 columns. First column is the key name, and the second column in the key-value of all the events averaged.

For example, event1 has key1's value set to 2. event2 has key1's value set to 6. If those are the only two events I received in the last 7 days, I want my table to show the number 4 in the row containing data for key1.

I can only average 1 key per query since I cannot put multiple summarizes inside of 1 query... Here is what I have for averaging the first key in the customMeasurements object:

customEvents
| where name == "PerformanceMeasurements"
| where timestamp > ago(7d) 
| summarize key1average=avg(toint(customMeasurements.key1))
| project key1average

But I need to average all the keys inside of this object and build 1 table as described above.

For reference, I have attached a screenshot of the layout of a customEvent customMeasurements object: Here is one event's customMeasurement data

azure-application-insights azure
1 Answers

If amount of Keys is limited and is known beforehand, then I'd recommend using multiple aggregations within | summarize operator by separating them with comma:

| summarize key1average=avg(toint(customMeasurements.key1)), key2average=avg(toint(customMeasurements.key2)), key3average=avg(toint(customMeasurements.key3))

If Keys may vary, then you'd to flatten out custom dimensions first with |mvexpand operator:

customEvents
| where timestamp > ago(1h)
| where name == "EventName" 
| project customDimensions 
| mvexpand bagexpansion=array customDimensions
| extend Key = customDimensions[0], Value = customDimensions[1]
| summarize avg(toint(Value)) by tostring(Key)

In this case, each Key-Value pair from customDimensions will become its own row and you will be able to operate on those with the standard query language constructs.

user_48127470
Related