Calculate kWh

[livio.zanol (Livio Zanol Puppim)]

Good day/night everyone! I'm new to ELK stack, and was able to make a lot of visualizations that I needed, but I'm having some trouble creating this specific Metric vusualization. Can anybody help me?

---

TL;DR:
Having the measure in Watts every 5 seconds from 3 different sensors pushed to Kibana, I need to create these 3 Metrics visualization:

1. Current selected period kWh (kilo watts per hour);
2. Last month kWh;
3. Current mont KWh.

---

Below are the values pushed to Elastic Search by Logstash every ~ 5 seconds, which are from 3 different sensors:

val.ap_pw is a number in Watts that I want to make the calculation.

||Time|@timestamp|sensor|val.raw|val.ap_pw|val.irms|

||Jul 1, 2019 @ 19:31:36.445|Jul 1, 2019 @ 19:31:36.445|en_sensor2|508|129,11|1,02|
||Jul 1, 2019 @ 19:31:35.426|Jul 1, 2019 @ 19:31:35.426|en_sensor1|521|345,15|2,72|
||Jul 1, 2019 @ 19:31:30.359|Jul 1, 2019 @ 19:31:30.359|en_sensor3|506|84,37|0,66|
||Jul 1, 2019 @ 19:31:29.341|Jul 1, 2019 @ 19:31:29.341|en_sensor2|501|122,36|0,96|
||Jul 1, 2019 @ 19:31:28.323|Jul 1, 2019 @ 19:31:28.323|en_sensor1|522|344,54|2,71|

So, using pure math, to calculate kWh for any given time period I need to do the following:

• Calculate arithmetic mean (Average) for the individual sensors for the selected period (Let's call them M1 to M3)
• Sum the means (Let's call it Msum = M1 + M2 + M3)
• Subtract initial date from final date in the selected period (Tdifference = Tfinal - Tinitial (presuming the result will be timestamp in seconds))
• Calculate the hours in the current period (Thours = Tdifference * 60 * 60)
• Multiply the hours in the currently by the Sum of the means (Wh = Msum * Thours)
• Divide by 1000 to have kWh (kWh = Wh / 1.000)

Or: kWh = (M1 + M2 + M3) * (Tfinal - Tinitial) /60 /60 / 1000

Reading other questions related (like 'calculate bytes per second'), I've created this Metric below using Visual Builder, but is not showing the right value:

Actual usage:

As can be see, the result should have been something around 0,55 kWh

Can somebody help me on this? Thanks in advance.

[livio.zanol (Livio Zanol Puppim)]

Managed to do similar calculations using this math function:

params.ap_pw * (last(params._all.ap_pw.timestamps)-first(params._all.ap_pw.timestamps)) / 60 / 60 / 1000 / 1000, but I still have 2 problems:

1. The Avarage calculation excludes missing values, but the initial/final timestamp doesn't, which will give wrong kWh results when you have missing values on the dataset.
2. I couldn't agreggate the 3 values to generate only one kWh value.