Creating a table of metrics above a given threshold

Grafana InfluxDB
1

Hi folks,

I’m working with a dataset in InfluxDB 1.8 which has a significant amount of temperature data feeding in to it from IoT devices.

The aim is to create a table on a dashboard that only shows device which are reporting a temperature of below a particular value

My generic “return all the rows” query is an attempt at an equivalent of this pseudo-SQL:

SELECT last(value), device_name 
FROM temperature 
WHERE time > now() - 6h
GROUP BY device_name

which should return

time value device_name
$seconds 17.20 room 001
$seconds 18.00 room 002
$seconds 16.10 room 003
$seconds 8.6 room 004

I then want to take that data and only show the data in the table when the value is less than 10, so something like:

SELECT last(value), device_name 
FROM temperature 
WHERE time > now() - 6h
    AND value < 10
GROUP BY device_name

So I can reformat the table as follows:

ROOM NAME TEMPERATURE
Room 001 8.6

I’ve been battling the query syntax and formatting for a while, but I can’t work out how it should all hang together.

2

You have to use a subquery to filter the first result set.

SELECT time, value, device_name
FROM (
    SELECT last(value) AS value, device_name 
    FROM temperature 
    WHERE time > now() - 6h
    GROUP BY device_name
) WHERE 
  value < 10

You might want to also use the group by in the outer query, but that’s up to the tool you use, if you are using a grafana table, and the format of the query is “table” you don’t need it. for time series you will need to group by time and tags (and also use a function on the fields)