InfluxQL count with subquery in Flux

InfluxDB 2
1

Hi! In InfluxQL I used this subquery structure a lot to count in Grafana.

For example, for count “devices” with “status” = 0 :

SELECT count("device") FROM (SELECT last("value") as "status" FROM "measurement" WHERE $timeFilter GROUP BY "device") WHERE "status" = 0

I’ve been trying to translate it in Flux but I can’t quite understand how it would be.

Thanks!

2

Try this:

from(bucket: "<your bucket>")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measurement")
  |> filter(fn: (r) => r["_field"] == "status")
  |> group(columns: ["devices"])
  // this gives the last status of each device
  |> last()
  // this gives the last status where value in 0
  |> filter(fn: (r) => r["_value"] == "0")
  // this collapses the results into one big table 
  |> group()
  |> count()

This assumes that “devices” is a tag and has a unique name per device, like a mac address or something.

2 Likes
3

I might have misread what “status” is.
The above should work if your input in line protocol looks something like:

insert measurement,device=xyz status=1 timestamp
insert measurement,device=xyz status=0 timestamp
insert measurement,device=zzz status=0 timestamp

The count should be 2 in this case.

4

You are a boss, it works.

I have changed:

|> filter(fn: (r) => r["_value"] == "0")

|> filter(fn: (r) => r["_value"] == 0)

because 0 is int not string.

Thanks a lot ! :clap: :clap: