Get daily max and min value

1

I would like to get daily max and min cpu utilization for the day for each host, and telegraf is sending data every 5 min.
I am using below query and output data is not correct, also time remains same for all the output.

SELECT min(“usage_idle”) FROM “cpu” WHERE (“ip” =~ /^$ip$/) AND time >= now() - 15d
GROUP BY time(1d), “host”

and output is like below.

image
image1241×526 84.8 KB

Please suggest

2

Hi,
this can be done quite easily. Just take a look at the picture below:

image
image1510×767 45.5 KB

The query will look like this:

SELECT max("value"), min("value"), mean("value") FROM "[[from_value]]"."i2c_value" 
WHERE ("host" =~ /^$node$/ AND "type_instance" = 'temperature2') AND $timeFilter 
GROUP BY time(1d,-2h) fill(none)

Or in your case, since you only have idle time and require usage:

SELECT 100-max("usage_idle") as min, 100-min("usage_idle") as max FROM "cpu"."i2c_value" 
 WHERE (“ip” =~ /^ip/) AND time >= now() - 15d
GROUP BY time(1d), "host" fill(none)

The time in your table remains the same, since you group by 1 day. So only the date varies from row to row. Because of the grouping, there will only be one value per day. You may be able to shift the time to midnight by using GROUP BY time(1d,-330m). The “-330” will shift the time 5 hours and 30 mins back…