Filter InfluxDB by topics in a bucket

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

@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

Hi grant2,

thanks for your reply.

My full query so far is

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

The result looks the following:

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

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

For example, this:

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")

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

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

how can i filter all the topics list. I want a quert that output all the topic in the topic filter. I just want list of topics

Currently i am using this query but its not working fine

from(bucket: “kimono-influxdb”)
|> range(start: -30d, stop: now())
|> filter(fn: (r) => r[“_measurement”] == “mqtt_consumer” or r[“_measurement”] == “mqtt_float” or r[“_measurement”] == “mqtt_json” or r[“_measurement”] == “mqtt_string”)
|> filter(fn: (r) => r[“host”] == “tcf-kimono-mqtt.th-deg.de”)
|> filter(fn: (r) => r[“topic”] =~ /kimono/)
|> aggregateWindow(every: 1h, fn: (column, tables=<-) => tables |> last(column: column), createEmpty: false)
|> distinct(column: “topic”)
|> yield(name: “last”)