Trying to get a single value from the difference of 2 timeseries values, same dimensions, different values...

[calogajac]

I've been working to create a single value block on a notebook where I'm fetching two different timeseries values that use the same metric. The dimensions on this metric are by environment and region (region meaning the region of an EKS Kubernetes cluster). The trick is that for each value, they both have a distinct region. Here's my query so far:

timeseries east_count = sum(`custom-metric`, default:0), by:{env, region}
| filter env == "dev"
| filter region == "region-1"
| append [
  timeseries west_count = sum(`custom-metric`, default:0), by:{env, region}
  | filter env == "dev"
  | filter region == "region-2"
]
| fields east=arraySum(east_count), west=arraySum(west_count)

I am not currently running the difference between the two because I want to see what both values equate to. I would expect by running this query to get a single record with 2 fields, but I appear to be getting two records, each of which have both fields. The records end up coming back like this:

record1: east: 6000, west: null

record2: east: null, west: 6500

Where have I gone wrong here in returning two records rather than a single record?

[krzysztof_hoja · Accepted solution]

This is exactly what append does: adds new records. First part of the query added 1st record, then with append you added 2nd.

But it is possible to merge both into single one (i am using metrics available on my tenant):

timeseries east=avg(dt.cloud.aws.alb.bytes), by:{aws.region}, filter:aws.region=="us-east-1"
| append [
timeseries west=avg(dt.cloud.aws.alb.bytes), by:{aws.region}, filter:aws.region=="us-west-2"
]
| fields east=arraySum(east), west=arraySum(west)
| summarize {east=takeAny(east), west=takeAny(west)}

Kris

[calogajac]

Thanks Kris! There is definitely a better way to do this, I'm sure, but your suggestion got me to the desired solution with a couple small tweaks. Appreciate the help!