-
What Grafana version and what operating system are you using?
Grafana 10 with Linux OS -
What are you trying to achieve?
the deviation of each device power consumption per day compared to the average power consumption per device per day. -
How are you trying to achieve it?
Can’t achieve this this. -
description of what I am trying to achieve:
Is it possible to combine Transformed data with the Queried data in Grafana? For example: We have 5 devices measuring and storing actual power consumption in InfluxDB. In a Grafana graph we do a single Query to get data for power consumption per device per day. Then we do a few Tranformations: 1) count the devices, 2) count the total power consumption per day, 3) divide Transformation 2 with Transformation 1 to get the average power consumption per device per day. QUESTION: How can we divide the Transformation 3 (average power consumptino per device per day) with a selected device power consumption per day? MOTIVATION: We need to know the deviation of a single device power consumption per day compared to the average power consumption per device per day. Thanks a lot!
Hi,
We can combine the query with tranformation in grafana. It depends upon the type of transformation you have selected. You can select organize_fields to rename the column headings etc
Hi @ushi
Instead of doing Transformations, I created expressions to do the COUNT of A and COUNT of B, then did an expression to Divide the values. It works in that there are no errors, but it’s now a single value that must be displayed via a stat panel (instead of the original time series chart). If that approach works for you, you could probably continue on with this approach.
However, what you inquired about (getting average power consumption per device per day) is quite easy to obtain using Flux functions. You did not specify which flavor of InfluxDB you are using and which query language.
Hi @grant2
thanks a lot for your effort. Unfortunately your approach does not work for me. Expression (Reduce A Count Strict) does not return any value:
Could you help me/direct me how to accomplish this task with InfluxDB? The actual query is:
SELECT derivative(max("value"), 24h) FROM "teplo_kWh" WHERE time >= 1697290174337ms and time <= 1697894974338ms GROUP BY time(1d,-1h), "unit"::tag fill(null) ORDER BY time ASC tz('Europe/Prague')
We use influxdb version 1.8.3
Thanks a lot
I am not really sure you can do this with InfluxQL, which is why I mentioned using Flux instead. However, be sure to read up on that before trying to make the switch.
I managed to do this with InfluxQL nested query! (finally figured it out)
Thanks a lot for your help!
Could you post your query here so others can find it in the future?
My pleasure. Here is the original Query A:
Based on Query A working further by adding Query B to get “sum_power_of_all_devices” (units) for each day.:
SELECT SUM("derivative") from (SELECT derivative(max("value"), 24h) FROM "teplo_kWh" WHERE $timeFilter GROUP BY time(1d,-1h), "unit"::tag fill(null) tz('Europe/Prague')) GROUP BY time(1d,-1h) fill(none) tz('Europe/Prague')
Based on Query A working further by adding Query C to get “number_of_devices” (units) for each day:
SELECT COUNT("derivative") from (SELECT derivative(max("value"), 24h) FROM "teplo_kWh" WHERE $timeFilter GROUP BY time(1d,-1h), "unit"::tag fill(null) tz('Europe/Prague')) GROUP BY time(1d,-1h) fill(none) tz('Europe/Prague')
At this point adding Expression D to divide “sum_power_of_all_devices” by “number_of_devices” to get the average power consumption per device per day:
And it works so nicely!