How to subtract the minimum value from maximum value for the past 24hours and show it in Grafana?

InfluxDB 2
,
1

Hi,

I am new to InfluxDB. Using influx 2.0.
Usually, I work with SQL.

I am working with Grafana and I am trying to show on a graph the value of max() minus the min() value for the past 24h on a certain field.
For example, I have a service called “WhoIs” and I would like to see for the past 24h what was the maximum value of the measurement and substruct from it the minimum value from the past 24h.

So basically I am expecting to see in Grafana a number every time there is a change for example:
If the maximum value for the past 24hour is 200 and the current minimum is 180 then I would like to see on the graph a dot on the current time which states 20 (200-180).

I searched the community for an answer and saw one that maybe could fit, but it shows on the graph all the values the minimum and maximum, and not the sum of subtracting minimum from the maximum.

This what was suggested:

data = from(bucket: "my-bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cpu")
  |> filter(fn: (r) => r["_field"] == "usage_system")
  |> filter(fn: (r) => r["cpu"] == "cpu-total")

min = data
  |> min()

max = data
  |> max()

join(tables: {min: min, max: max}, on: ["_start", "_field", "_start", "host", "_measurement"], method: "inner")
  |> map(fn: (r) => ({ r with _value: r._value_max - r._value_min }))

Also, what is the equivalent of SQLs “group by” function in Flux?

Any suggestion will be much appreciated.

2

Okay, I managed to resolve it, took a few days but here is the answer, hopefully it will help someone who works with Grafana and needs to show some points on the graph.

This will show on the graph points (or bars) the result of subtracting the current minimum value for the past day from the maximum value:

data = from(bucket: "my-bucket")

  |> range(start: -7d)

  |> window(every: 1d)

  |> filter(fn: (r) => r["_measurement"] == "svc_usage")

  |> filter(fn: (r) => r["_field"] == "usage")

  |> filter(fn: (r) => r["svc_name"] == "WhoIs")

min = data

  |> min()

max = data

  |> max()

join(tables: {min: min, max: max}, on: ["_start", "_stop", "svc_name"], method: "inner")

  |> map(fn: (r) => ({ 

    r with 

    _value: r._value_max - r._value_min 

    })

  )

|> group(columns: ["_field"], mode:"by")
3

I know you’ve found a solution to your issue already, but, you may be able to simplify your query via use of the built-in spread function, which calculates the difference between the minimum and maximum value of a column.

4

+1 to @AdamGardner’s suggestion. spread() will give you what you need and be much more performant:

data = from(bucket: "my-bucket")
  |> range(start: -7d)
  |> filter(fn: (r) => r["_measurement"] == "svc_usage")
  |> filter(fn: (r) => r["_field"] == "usage")
  |> filter(fn: (r) => r["svc_name"] == "WhoIs")
  |> window(every: 1d)
  |> spread()
5

Thanks for the reply, I will try to play with the spread function to see how it works.
Thanks again!