Challenging discrete data display

Grafana Configuration
1

I have a series of data which is gathered by sending the list of “active demands” to InfluxDB every minute.
This query
SELECT “value” FROM “demands” WHERE $timeFilter GROUP BY “key”

produces:

Time	demands {key: groupLight1}	demands {key: heating}	demands {key: heating-office}
10/08/2020 16:43	OFF			
10/08/2020 16:44	OFF			
10/08/2020 16:45	OFF			
10/08/2020 16:46	OFF			
10/08/2020 16:47	OFF			
10/08/2020 16:48	OFF			
10/08/2020 16:49	OFF

Each column will reflect the status of the demand. The columns (tag.key) are not known in advance.

Not all “demands” have an corresponding “OFF” state as can be seen by the blank columns above.

When I put this query into the discrete plugin, it works, except it shows blank values as a repeat of the last non-null value for that column.

So in short if the “heating” demand is active for 5 minutes at 09:00 then a full day graph shows the heating as ON from 09:00 till the end of the graph. if I query for the range 09:06 then no values will show at all for “heating”.

I think the way to solve this is to switch blanks for “OFF” or “NONE”. I tried the fill(null) and various variants, but that doesn’t seem to work along side the “group by (key)” which groups it into the columns per demand key (the Influx metric tag called key)

SELECT “value” == null ? “value” : ‘NONE’

Is a thought, but I can’t even remember if that’s valid in full SQL let alone Grafana and InfluxDB

2

So if you found this via google and wonder if there is a solution…

The trick was to use an aggregation of “MODE” on the boolean and a time interval slightly larger than the window the items would “Active”/“True”.

This provides the discrete panel with values which change and correctly display the lack of a value as an indepenant state with a value of null or N/A which can be mapped.

1 Like
3 
SELECT mode("value") FROM "autogen"."demands" WHERE $timeFilter GROUP BY time(61s), "key"