InfluxDB sum of all values

Grafana InfluxDB
,
11

Through trial and error I finally got something working consistently well.

First it synchonizes the times by getting the mean values for all devices across the same time periods and only then it starts adding them together.
It can cause issues where the window period gets small enough, that’s why I put the 2m limit there (also I doubled the default one, to make the query slightly faster).

option minWindowPeriod = 2m

from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Cisco-IOS-XR-sysadmin-fretta-envmon-ui:environment/oper/power/location/pem_attributes")
  |> filter(fn: (r) => r["_field"] == "system_power_input" and r["_value"] < 1000)
  |> group(columns: ["source"])
  |> window(every: if float(v: int(v: v.windowPeriod) * 2) < float(v: int(v: minWindowPeriod)) then minWindowPeriod else duration(v: int(v: v.windowPeriod) * 2))
  |> mean()
  |> duplicate(column: "_stop", as: "_time")
  |> window(every: inf)
  |> group(columns: ["_time"])
  |> sum(column: "_value")
  |> duplicate(column: "_time", as: "_stop")
  |> duplicate(column: "_time", as: "_start")  
  |> group()
  |> yield(name: "power")

There’s a problem with the first and last time windows possibly not having all the individual device measurements making the sums not accurate.
I think the best solution would be to simply drop the first and last rows from the table, but I felt it’s a different enough issue to deserve it’s own topic: Influx - drop the first and last rows/values from the table - #2 by ebabeshko

Thanks for the help.

1 Like