Query help - how to get non reporting devices over a period of time

I’ve a bucket where I’m storing temperature data from a number of sensors.
I want to know if a sensor is not reporting data for a set period.
I’m running the query below that gives me a table with all the sensors seen in the last X period (1 month in the example) and then calls for each record a helper function that runs a query, for each of the devices, identified by the “wtg” field and returns the number of records logged in the last 24h.
While it seems to work, the function it always returns the same value (first record).
Couple of questions here:

  1. I do not really understand the relation between the parameter passed by the map function with the local “(r)” in the “get_points_count” function used by the query. I tried to modify it but I’m receiving errors. I’ve a feeling that the line " |> filter(fn: (r) => r[“wtg”] == r.wtg)" is at play here as I expected the r.wtg would reference the wtg called by the map function and it should be different from the “r[“wtg”]” in the filter statements.

  2. Is there a better way to achieve my objective ?

get_points_count = (r) => {
points = from(bucket: “wtg_data”)
|> range(start: -1d)
|> filter(fn: (r) => r[“_measurement”] == “wtg_data”)
|> filter(fn: (r) => r[“_field”] == “batt”)
|> filter(fn: (r) => r[“wtg”] == r.wtg)
|> count()
|> findRecord(fn: (key) => true, idx: 0)
return points._value
}

from(bucket:“wtg_data”)
|> range(start: -1m)
|> filter(fn: (r) => r[“_measurement”] == “wtg_data”)
|> filter(fn: (r) => r[“_field”] == “batt”)
|> group(columns: [“wtg”])
|> distinct(column: “wtg”)
|> map(fn: (r) => ({r with points_count: get_points_count(r) }))

@Sal Welcome! First let me say sorry for the delay I was out of office.
Second that’s cool! What’s the context of this project? That sounds really cool.

2 Things:

  1. In Flux, as in many programming languages, variables have scopes in which they are defined and accessible. The r in your get_points_count function and the r used in your main query are not the same. Each function call creates its own local scope, meaning that the r inside get_points_count is different from the r in the map function.
  2. The line |> filter(fn: (r) => r["wtg"] == r.wtg) within get_points_count seems to intend to filter records where a certain condition is met. However, this condition effectively checks if a field equals itself, which will always be true and is likely not what you intended.

It seems you want to calculate the count of records for each distinct “wtg” value based on certain criteria, and then augment each “wtg” record with this count. The problematic filter condition is likely intended to compare the “wtg” field of each record in the get_points_count function against the “wtg” value passed from the map function. However, as written, it doesn’t achieve that because it compares a field to itself within the same record.

Instead try:

get_points_count = (wtg_value) => {
  points = from(bucket: "wtg_data")
    |> range(start: -1d)
    |> filter(fn: (r) => r["_measurement"] == "wtg_data")
    |> filter(fn: (r) => r["_field"] == "batt")
    |> filter(fn: (r) => r["wtg"] == wtg_value) // Use the passed wtg_value for comparison
    |> count()
    |> findRecord(fn: (key) => true, idx: 0)
  return points._value
}

from(bucket:"wtg_data")
  |> range(start: -1m)
  |> filter(fn: (r) => r["_measurement"] == "wtg_data")
  |> filter(fn: (r) => r["_field"] == "batt")
  |> group(columns: ["wtg"])
  |> distinct(column: "wtg")
  |> map(fn: (r) => ({r with points_count: get_points_count(r.wtg) })) // Pass r.wtg to get_points_count

Does that help?

It does thanks, got to the same result just before your answer :grinning: