Filter InfluxDB by topics in a bucket

Grafana InfluxDB
1

Grafana v9.4.7

I query from an InfluxDB via Flux and want to get a single value.

But the bucket has 6 topics coming from MQTT to InfluxDB, and I didn’t manage to filter them out. I want only one of them to be in this query.

from(bucket: "solar")
  |> range(start: -3h)

Can someone help me to add a filter to the code?
I tried a lot with google, but nothing worked.

The desired value is shown in the graph as following:

value {host="InfluxDB", topic="solar/12345/0/power"}

The data type is a float (Power in Watt of my solar panel inverter)

Thanks in advance

2

@nes3Ah
Welcome to the Grafana forum.

Can you show the full Flux query and a table of the results, as viewed in Influx Data Explorer)?

Then in that screenshot, show us exactly what you want to extract / filter?

1 Like
3

Hi grant2,

thanks for your reply.

My full query so far is

from(bucket: "solar")
  |> range(start: -3h)

The result looks the following:

Bildschirmfoto vom 2023-03-28 14-30-14
Bildschirmfoto vom 2023-03-28 14-30-141241×340 35.2 KB

table
table1273×248 29.5 KB

As said, I’d like to query only the “…/0/power” value for this pattern.

Edit:
To say it, I just got in touch with MQTT, Telegraf, InfluxDB and Grafana a few days ago and spent some hours to get them working in LXCs on my ProxMox VE Homeserver

4

Have you played around with the Data Explorer in Influx to build the query you desire?

For example, this:

image
image1862×859 254 KB

is the same as this (viewable after clicking the “Script Editor” button) on the right:

from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "PercentTotalCapacity")
  |> filter(fn: (r) => r["EquipZone"] == "CircuitA")
  |> filter(fn: (r) => r["EquipNumber"] == "32")
  |> filter(fn: (r) => r["_measurement"] == "ChillerData")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
5

Many thanks, now I understand how the filter in Grafana works!

I checked the data explorer in InfluxDB, adjusted your code and added it in Grafana:

from(bucket: "solar")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["host"] == "InfluxDB")
  |> filter(fn: (r) => r["topic"] == "solar/12345/0/power")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Works exactly as I wanted, now I will adapt this for the other values.

Thanks a lot and have a nice day!

1 Like
6

Great, glad it’s good now. In case you want to create alerts in Grafana using Flux, here is a tutorial from a few months ago.

1 Like