Flux Assistance - Divide Two Integrals

Fluxlang
,
1

Hi, I’m trying to create a single stat grafana panel that shows a percentage; the backend is InfluxDB 2.1 and I’m using Flux. This percentage can be computed by diving these two queries’ results:

from(bucket: "metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy_meters" and r["_field"] == "site_instant_power")
  |> integral(unit: 1h)
  |> group(columns: ["_field"])

and

from(bucket: "metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy_meters" and r["_field"] == "load_instant_power")
  |> integral(unit: 1h)
  |> group(columns: ["_field"])

and then multiplying the quotient by 100.

My question is how can I weave these together into a single Flux query for this panel? Thanks.

Also, for reference, this is the InfluxQL query I used to use on v1.8:

SELECT (1-integral("site_instant_power", 1h)/integral("load_instant_power", 1h))*100 FROM "energy_meters" WHERE $timeFilter fill(null)
2

Make each call separately and assign the ensuing tables to variables. Join the tables, then map (if necessary) over the joined tables. Psuecode …

site_instant_power = from(bucket: "metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> ...

load_instant_power = from(bucket: "metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> ...

experimental.join(left: site_instant_power, right: load_instant_power, fn: (left, right) => ({ <whatever calculations here> }))
|> map(fn: (r) => ({ < whatever calculations here> }))
|> yield()

This content may be helpful: Querying and Data Transformations | Time to Awesome

3

Thanks, following your suggestions I produced this solution which works in my panel:

energy = from(bucket: "metrics")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "energy_meters")

p_site = energy
  |> filter(fn: (r) => r["_field"] == "site_instant_power")
  |> integral(unit: 1h)

p_load = energy
  |> filter(fn: (r) => r["_field"] == "load_instant_power")
  |> integral(unit: 1h)

join(tables: {p_load: p_load, p_site: p_site}, on: ["_measurement"])
  |> map(fn: (r) => ({
    r with
    self_powered: (1.0 - r._value_p_site / r._value_p_load) * 100.0
    })
  )
  |> keep(columns: ["self_powered"])
  |> yield(name: "self_powered")
4

Is usage of

really the proper solution for arithmetic with fields of a single measurement ?

My understanding is that pivot() is best practices method, see

With schema.fieldsAsCols() doing field arithmetic becomes really easy and compact, as explained by @Anaisdg .

Beyond that, join() seems to have a performance problem, see
Poor performance for join(): cpu and memory grows quadratically with row count
and experimental.join() seems to have a stability problem, see
Tried experimental.join(): good performance, but also "panic: unknown type invalid"

5

In my experience, if you have multiple series with the same timestamps (typically by writing multiple fields in the same line of line protocol), then pivoting is, indeed much easier. But if the fields are written separately with different timestamps, then you need to normalize the times and join.

@wfjm perhaps you could provide a code sample to demonstrate the pivoting approach?

6

True, some form of aggregation is almost always required, either for the reason you mention, so simply to reduce the amount of data to what can be shown in the visualization (usually the number of pixel in the pane displayl).
A simple pattern I use is

from(bucket: "dca")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TfcMonitor")
  |> filter(fn: (r) => r["_field"] == "dtq_min" or r["_field"] == "dtq_50")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: true)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r.dtq_50 - r.dtq_min }))
  |> keep(columns: ["_time", "_value", "host", "oid"])
  |> yield()

That script

  • selects two fields dtq_min and dtq_50
  • aggregates with mean
  • calculates the difference of the two fields
  • and keeps results organized by two tag keys host and oid

Here mean is used as aggregator, but any other should work too.

The Flux above is equivalent to the InfluxQL (using Grafana macros)

SELECT mean("dtq_50")-mean("dtq_min")
  FROM "TfcMonitor"
  WHERE $timeFilter
  GROUP BY time($__interval), "host", "oid" fill(null)

So even with InfluxQL that was easily possible.