How to do a basic summation?

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?
    Running a Docker container from the grafana/grafana image, launched 6/28/23 so likely the most recent version.
  • What are you trying to achieve?
    I’m trying to sum several metrics into one total number. I have 5 power meters reporting their total energy usage for the month and I would like to sum those into one total power usage number.
  • How are you trying to achieve it?
    I can grab the usage numbers individually using the following query and a stat visualization, filtered by last* value:
from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

I would like a single stat that contains the sum of all 5 numbers.
I have tried using a math expression with Sum($A) (or sum or SUM), but it simply says ‘no data’.
I have tried using an ‘add field from calculation’ transform with reduce row, total, and selecting the 5 power usage fields, but it just displays the value of one of them.

  • What happened?
    Described above.
  • What did you expect to happen?
    I would have expected either method to provide a summation of the last values of all 5 fields, or at least some form of a sum of something.
  • Can you copy/paste the configuration(s) that you are having problems with?
    The query is above, as is the math expression, I don’t know how to copy paste the transform, since it’s a gui configuration.
  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    The transform gives no errors, just nonsensical results. The math expression gives ‘no data’ with this error:
Query data error
Object
status:500
statusText:""
data:Object
message:"Query data error"
traceID:""
config:Object
url:"api/ds/query?ds_type=__expr__&expression=true&requestId=Q279"
method:"POST"
data:Object
requestId:"Q279"
hideFromInspector:false
headers:Object
retry:0
traceId:undefined
message:"Query error: 500 "
  • Did you follow any online instructions? If so, what is the URL?
    No, just general googling around and experimenting with the interface.

If there’s anything else I can add please let me know, I’m very new to grafana.

Edit: Basically I have this setup:

image
image2102×1087 186 KB

and what I’m looking for is a single stat that is the sum of all 5 of those values (2.05+5.4+1.72+0.165+0.863 = 10.198)

2

Welcome @joshuaeblackburn to the Grafana forum.

My guess is that the last value of each of the 5 series all have different timestamps, and that is causing the Transformation to not work.

Maybe try using two Transformations, like this?

image
image1218×745 74.8 KB

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

Does the above query just return a time series of data? For the other 4 numbers, does only the measurement change (e.g. “s-32”)?

3

They do indeed have different timestamps, the 5 power meters report on their own time, every 5 minutes.
Using two transforms doesn’t seem to change anything, the Total field is still just one of the measurements, and the Last * field seems to just be the same as the Total field.

image
image1404×1493 224 KB

The query

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

gives this result

image
image1410×1015 62 KB

The measurements are differentiated by the r.topic filter, which I’m not using since I want every topic. So if I just wanted one of the measurements I would add the line r.topic == "tele/server-s31/SENSOR" and to the filter section.

4

OK, how about using the same query…

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r._field == "totalwh"
  )

without any transformations and then using these options on the right side of Grafana?
image

5

I do not appear to have an option for Last * in the Fields section.

image
image683×816 50.3 KB

scrolling down the list just shows the Time and totalwh options for the other two power meters.

6

What happens if select All Fields? Does it give the right number in the Stat panel?

or if you select Numeric Fields? Does that give the right number?

7

Numeric Fields is what I’ve been on this whole time, along with Last * as the reducer function.

image
image2106×1196 260 KB

Switching to All Fields simply adds Time to the visualization:
image
image2104×1209 224 KB

I’ve noticed the value in the Total section always matches the most recent data point from any of the 5 meters, so when one reports, the Total field updates to reflect that latest measurement.

Edit: forgot to mention, selecting Total in the reducer function simply adds up all the reported values, so I end up with numbers for each meter that are gigantic. Since they report every 5 minutes, the server-s31 stat, for example, simply adds 5.4 to its total every 5 minutes, resulting in a huge number.

image
image2098×1184 248 KB

8

Switching to table view shows what I assume is the issue:

image
image2111×1207 224 KB

It appears that the Total transform only considers data points with the exact same timestamp, and since the meters report asynchronously, it grabs the latest timestamp and assumes the other data points at that time are zero. So it seems that I need to tell it to total the last data point of each series, rather than the data points at the latest timestamp. How to do that, I have no idea…

9

Start here: date.truncate() function | Flux Documentation

10

Ok, so I now have the ability to grab only the data from the last 5 minutes, which leaves me with a single value for each meter, instead of a series of data points. They still have asynchronous timestamps though and so the problem remains. I assume what you’re suggesting is to round each timestamp to the nearest 5 minutes so they all match, but I’m not seeing how to do that.

11

does the last data point of each series have the exact time stamp? otherwise back to square one

Please post sample data from your bucket in csv format

bucket,measurement,_field,_value,timestamp
energy,dfd,s-31,totalwh,33,293829839283
12

No, the timestamps of the last data points for each power meter are within 5 minutes of each other, but they are not the same, which is exactly what’s causing the issue. However, if I could round the timestamps in grafana to the nearest 5 minutes, then they would match.

Here’s a section of the csv data:

,result,table,_start,_stop,_time,_value,_field,_measurement,host,topic
,,0,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:13:10Z,2.218,totalwh,s-31,telegraf,tele/growlight-s31/SENSOR
,,1,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:12:50Z,5.646,totalwh,s-31,telegraf,tele/server-s31/SENSOR
,,2,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:13:20Z,1.796,totalwh,s-31,telegraf,tele/switch-s31/SENSOR
,,3,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:14:20Z,0.169,totalwh,s-31,telegraf,tele/v0-s31/SENSOR
,,4,2023-06-30T19:10:54.965976852Z,2023-06-30T20:10:54.965976852Z,2023-06-30T19:12:50Z,0.866,totalwh,s-31,telegraf,tele/workstation-s31/SENSOR

As you can see, the timestamps are at 19:13/12/13/14/12, all within five minutes, but not synchronized at all.

1 Like
13

there is a way to strip off the timestamp and get it “synched”. not sure if that will help. let me find the method unless @grant2 remembers

14

I figured out a messy way to do it.
First create a separate query for each data series:

from(bucket: "energy")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "s-31" and
    r.topic == "tele/growlight-s31/SENSOR" and
    r._field == "totalwh"
  )

This by itself yields a single data series, like this:
image
Then create a reduction expression for each data series query to grab only the last value:
image
Then finally create a math expression and add all the reduction expression values together:
image
I say this is messy because for n number of data series being summed, you need 2n+1 queries/expressions, so the query page gets real cluttered. There’s also the drawback that, while it does return the sum of the latest value in each data series:
image
it returns exactly one value, so the usual trendline in the stat visualization is missing.
image
If anyone knows a better way to do this, please let me know, but I’m marking this as the solution for now.

1 Like