Can't use heatmap with categorical variable as an axis

InfluxDB 2 Dashboards
, ,
1

Hi,
I am trying to create a heatmap where x axis is time and y axis is a categorical variable (unique ids).
Here is the flux query I use to create the “pivot table”:

// heatmap where 
//  x axis is datetime (in minute granularity)
//  y axis is machine_uid
//  values are # of heartbeats received
import "date"
from(bucket: "status")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "status")
  |> filter(fn: (r) => r["_value"] == "heartbeat")
  |> truncateTimeColumn(unit: 1m)
  |> group(columns: ["_time", "machine_uid"], mode:"by")
  |> count()
  |> group()
  |> pivot(rowKey:["_time"], columnKey: ["machine_uid"], valueColumn: "_value")

and this returns a table where columns are machine_uid and the index is _time and values are the count of "heartbeat"s.

table_RESULT _timeNO GROUPDATETIME:RFC3339 22JRANSUGGF6GK38NO GROUPLONG LYYVEXLF4DQM1TO4NO GROUPLONG XDSJ1OHKF4X122UUNO GROUPLONG
0 2022-06-06T18:43:00.000Z 1
0 2022-06-06T18:44:00.000Z 116
0 2022-06-06T21:52:00.000Z 2
0 2022-06-06T21:53:00.000Z 115
0 2022-06-06T22:37:00.000Z 117

However when I want to view this as a heatmap, I can’t seem to choose machine_uid as one of the axis, instead it let’s me choose each single machine.
image

Is there any way to choose machine_uid as one of the axis?

Thanks in advance!

2

Hello @nottakumasato,
If you don’t add the pivot function does that give you what you’re looking for?

3

Thank you for the suggestion. I commented out the pivot line:

from(bucket: "status")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "status")
  |> filter(fn: (r) => r["_value"] == "heartbeat")
   |> truncateTimeColumn(unit: 1h)
   |> group(columns: ["_time", "machine_uid"], mode:"by")
   |> count()
   |> group()
//    |> pivot(rowKey:["_time"], columnKey: ["machine_uid"], valueColumn: "_value")

this is the raw data:

table_RESULT _valueNO GROUPLONG _timeNO GROUPDATETIME:RFC3339 machine_uidNO GROUPSTRING
0 117 2022-06-06T18:00:00.000Z 22JRANSUGGF6GK38
0 117 2022-06-06T21:00:00.000Z LYYVEXLF4DQM1TO4
0 117 2022-06-06T22:00:00.000Z XDSJ1OHKF4X122UU
0 117 2022-06-06T23:00:00.000Z OEGBKS9WS50ITOQX
0 1973 2022-06-09T21:00:00.000Z O67HCF0ESXGZDAYA
0 2253 2022-06-09T21:00:00.000Z QMAP165K0YO34DL9
0 110 2022-06-09T21:00:00.000Z YZL1J1X7XVIPMYKH
0 2661 2022-06-09T21:00:00.000Z ZVKCC7H4LZCOJP1U
0 8475 2022-06-09T22:00:00.000Z 87F8XXMCR6E88ORR

and now heatmap looks more like what I want to achieve however the y-axis selection still doesn’t let me choose machine_uid.

image
image776×228 14.7 KB

  • Is it possible that axes can only be specific formats (e.g. LONG or DATETIME)?
  • Or maybe the column names have to start with an underscore to be able to be chosen as an axis?
4

Hello @nottakumasato,
Yes it looks like it has to be a specific format, you’re correct.
I made a feature request:

You couldddd use some conditional mapping to hack together a solution. How many machine_uid’s do you have?

5

Thank you @Anaisdg ! Hopefully it will get implemented soon. In the meantime, I was thinking of mapping all machine_uids to some Long value and use it like that.

1 Like