Two influx graphs in one

Grafana InfluxDB
1

Hi,
I try to display my data from influxDB. Finally I managed to design my query. What I did not achieve is to display two queries in one bar chart.

first query:

import "date"
from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "openknx.0._BC__Buero_Christian.Verbraucher._BC__Ver___PC_-_Wirkleistung" and r._field == "value")
  |> map(fn: (r) => ({r with hour: date.hour(t: r._time) } ))
  |> group(columns: ["hour"], mode:"by")
  |> mean()
  |> group()
  |> sort(columns:["hour"])
  |> map(fn: (r) => ({r with hour_txt: string(v: r.hour)}))
  |> drop(columns: ["hour"])
  |> yield(name:"PC CH")

second query:

import "date"
from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "openknx.0._BT__Buero_Tinka.Verbraucher._BT__Ver___PC_-_Wirkleistung" and r._field == "value")
  |> map(fn: (r) => ({r with hour: date.hour(t: r._time) } ))
  |> group(columns: ["hour"], mode:"by")
  |> mean()
  |> group()
  |> sort(columns:["hour"])
  |> map(fn: (r) => ({r with hour_txt: string(v: r.hour)}))
  |> drop(columns: ["hour"])
  |> yield(name: "PC TI")

As soon as I disable one of the queries the other one will be displayed.

What am I doing wrong?

Best regards,
Kevin

2

Hi @kevint

Looks like you have two different measurements, but the rest of the fields / tags are the same. Is that correct? If yes, simply separate the two measurements with or like this:

import "date"
from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "openknx.0._BC__Buero_Christian.Verbraucher._BC__Ver___PC_-_Wirkleistung" or r._measurement == "openknx.0._BT__Buero_Tinka.Verbraucher._BT__Ver___PC_-_Wirkleistung")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({r with hour: date.hour(t: r._time) } ))
  |> group(columns: ["hour"], mode:"by")
  |> mean()
  |> group()
  |> sort(columns:["hour"])
  |> map(fn: (r) => ({r with hour_txt: string(v: r.hour)}))
  |> drop(columns: ["hour"])
  |> yield(name: "PC TI")
3

Hi @grant2,
you are right. Those are different measurements with identical tags, but wouldn`t your solution result in one graph instead of two separate ones?

4

Hi @kevint Yes, you should get one graph, which I thought was your goal when you wrote “display two queries in one bar chart”.

Did you try this yet in Grafana, and if yes, what was the result?

5

Hi @grant2,
I did try it but couldn’t understand it at first as I had a mistake in my query. It should be sum() after the grouping, not mean().

Now it is as expected: The sum of both values:

grafik
grafik1610×377 28.1 KB

What I would like to have is bars in one bar chart:

grafik
grafik727×248 2.39 KB

6

Hi,
I solved my issue within flux.
Now my code looks like this:

import "date"

from(bucket: "iobroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "openknx.0._BC__Buero_Christian.Verbraucher._BC__Ver___PC_-_Wirkleistung" or r["_measurement"] == "openknx.0._BT__Buero_Tinka.Verbraucher._BT__Ver___PC_-_Wirkleistung")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({r with hour: date.hour(t: r._time) } ))
  |> group(columns: ["_measurement","hour"], mode:"by")

  |> sum()
  |> group()
  |> map(fn: (r) => ({r with hour_txt: string(v: r.hour)}))
  |> drop(columns: ["hour"])
  |> pivot(rowKey: ["hour_txt"], columnKey:["_measurement"], valueColumn:"_value")

I still dont understand why using two queries did not work.