Using a key:value variable to rename columns or change values in flux query

Grafana Dashboards
1

Hi there!

I’m trying to visualize how much of queue we have in our different Azure DevOps agent pools. For this I’m using the following query for my datasource and a “State timeline” which works great.

from(bucket: "ci/ado-metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "azure_devops_agentpool_queue_length")
  |> filter(fn: (r) => r["agentPoolID"] =~ /^${agent_pools}$/)
  |> keep(columns: ["_time", "_value", "agentPoolID"])
  |> group()
  |> pivot(rowKey:["_time"], columnKey: ["agentPoolID"], valueColumn: "_value")

This gives a nice graph like so

image
image745×184 5.42 KB

Since however I do not get the pool name, instead I get the pool id.

I wanted to map the ID:s to names using a built in key value variable, but I cannot get it to work… Instead I had to resort to mapping the values “manually” using the following map()

from(bucket: "ci/ado-metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "azure_devops_agentpool_queue_length")
  |> filter(fn: (r) => r["agentPoolID"] =~ /^${agent_pools}$/)
  |> keep(columns: ["_time", "_value", "agentPoolID"])
  |> group()
  |> map(fn: (r) => ({
      _time: r._time,
      _value: r._value,
      agentPoolID: if r.agentPoolID == "12" then "oe-build"
                   else if r.agentPoolID == "16" then "cicd"
                   else if r.agentPoolID == "20" then "signing"
                   else r.agentPoolID
  }))
  |> pivot(rowKey:["_time"], columnKey: ["agentPoolID"], valueColumn: "_value")

Which result in

image
image757×185 6.45 KB

Anyone know if it would be possible to use these variables instead so that I only need to change my variables when there are updates?

image

Maybe even if I could use only one variable for both the filtering and mapping.

I’ve tried both map before pivot and rename columns after pivot…

Here are some example data from after the group() and before the pivot.

#group,false,false,false,false,false
#datatype,string,long,dateTime:RFC3339,double,string
#default,_result,,,,
,result,table,_time,_value,agentPoolID
,,0,2024-09-17T11:55:09.571444143Z,1,12
,,0,2024-09-17T11:55:19.57328423Z,1,12
,,0,2024-09-17T11:55:29.568568038Z,1,12
,,0,2024-09-17T11:55:39.592233824Z,1,12
,,0,2024-09-17T11:55:49.568004884Z,1,12
,,0,2024-09-17T11:55:59.561587649Z,1,12
,,0,2024-09-17T11:56:09.575937373Z,0,16
,,0,2024-09-17T11:56:19.56414817Z,0,16
,,0,2024-09-17T11:55:09.571444143Z,0,16
,,0,2024-09-17T11:55:19.57328423Z,0,16
,,0,2024-09-17T11:55:29.568568038Z,0,16
,,0,2024-09-17T11:55:39.592233824Z,0,16
,,0,2024-09-17T11:55:49.568004884Z,0,16
,,0,2024-09-17T11:55:59.561587649Z,0,16
,,0,2024-09-17T11:56:09.575937373Z,0,16
,,0,2024-09-17T11:56:19.56414817Z,0,16

Edit: What I have tried is the following

from(bucket: "ci/ado-metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "azure_devops_agentpool_queue_length")
  |> filter(fn: (r) => r["agentPoolID"] =~ /^${agent_pools}$/)
  |> keep(columns: ["_time", "_value", "agentPoolID"])
  |> group()
  |> map(fn: (r) => ({r with agentPoolID: ${pool_id_to_name:r.agentPoolID}}))
  |> pivot(rowKey:["_time"], columnKey: ["agentPoolID"], valueColumn: "_value")

But I only get
invalid: compilation failed: error @7:22-7:55: expected comma in property list, got COLON error @7:45-7:54: missing property key

Cheers
Carl

2

this is really not best practice nor scalable. imagine for a minute you inherit 100 new pools and lose 57. Yes it might not be realistic but still, create a bucket or a measurement with the key value pairs and join to that.

also for key: value pair they should be referred with

$foo:value
$foo:text