How to retrieve field values dropped by the range() function? (filter by time or add back field values)

InfluxDB 2
1

Hey there,
hope there is an easy and elegant solution to this.

In this simplified example:

from(bucket: "test")
  |> range(start: -24h)
  |> group(columns: ["device_name"])
  |> count()

the query successfully counts the number of measurements per “device” over the course of 24 hours. Devices without any event in that time range will be omitted by the range() function and not show up in the result. I would like to show ALL devices (or devices with events anytime the last x days) with a count = 0. How is this possible?

2

Just a guess, but maybe this will get you started?

t1 = from(bucket: "test")
  |> range(start: -24h)
  |> group(columns: ["device_name"])
  |> count()
  |> yield(name: "CountOver24Hours")

t2 = from(bucket: "test")
  |> range(start: -7d)
  |> group(columns: ["device_name"])
  |> filter(fn: (r) => r._value = 0)
  |> count()
  |> yield(name: "CountZeroOver7Days")

t3 = join(tables:{CountOver24Hours:t1, CountZeroOver7Days:t2}, on:["device_name"])
  |> range(start: -24h)
  |> yield(name: "CombinedCounts")
1 Like
3

Hey @grant1,
This looks like a solution that will do the job. Thanks!

I was hoping for a more streamlined, less repetitive solution. I need to apply this logic to dozens of graphs…

I was hoping for something like this:

from(bucket: "test")
  |> range(start: -7d)
  |> filter(fn: (r) => r._time > now-24h, onEmpty: "keep")   // not real code
  |> group(columns: ["device_name"])
  |> count()

I didn’t get it to work. Any help would be appreciated!

4

Hi @ThomDietrich

I agree that the 3-table design is kinda messy, and that the onEmpty: "keep" will probably help somehow. Let’s wait for the Masters to chime in and see what they say.

5

Any idea which master we could @ mention to chime in? :slight_smile: This must be a use case quite common to many users.

6

@Anaisdg or @scott

Are either of you able to give @ThomDietrich a hint? Is my 3-table proposal the only way to do this?

1 Like