Average of 2 arrays by a field

[elenaperez]

Hi team,

Having the following query:

timeseries value = avg(dt.cloud.azure.vm.cpu_usage), by: {azure.location, azure.management_group, azure.resource.name, azure.subscription, azure.vm_scale_set.name, dt.entity.azure_region, dt.entity.azure_vm, dt.source_entity, dt.source_entity.type}
| filter contains(azure.resource.name, "primary00") or contains(azure.resource.name, "primary01")
| parse azure.resource.name, "LD 'primary0' INT:number"

I'd like to get in a timeseries the average of the arrays by number. Meaning, for all arrays with number==0, an average of the values of the arrays and the same for number==1.

How could I achieve this? arrayAvg doesn't seem to work for this use case.

Thanks in advance!

[krzysztof_hoja · Accepted solution]

You need to use integrative expressions in summary command.

timeseries value = avg(dt.cloud.azure.vm.cpu_usage), 
  by: {azure.location, azure.management_group, azure.resource.name, azure.subscription, azure.vm_scale_set.name, 
  dt.entity.azure_region, dt.entity.azure_vm, dt.source_entity, dt.source_entity.type}
| filter contains(azure.resource.name, "primary00") or contains(azure.resource.name, "primary01")
| parse azure.resource.name, "LD 'primary0' INT:number"
| summarize {value=avg(value[])}, by: {interval, timeframe, number}

 This query will give 2 timeseries for 2 possible values of "number". Of course you can split data as you with by adding more of available dimensions retrieved by timeseries command. 

Please also take a look a other examples in our documentation: https://docs.dynatrace.com/docs/analyze-explore-automate/metrics/dql-examples#example-17-split-cpu-u...