Remove data when value exced a certain value

InfluxDB 2
1

Hi All,

my rain fall meter log bad value in my influxdb
I can select all of them using this query :

from(bucket: “rtl_433”)
|> range(start: 2022-11-26T02:57:34.400630731Z, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “Bresser-6in1”)
|> filter(fn: (r) => r[“_field”] == “rain_mm”)
|> filter(fn: (r) => r[“_value”] >= 4000)
|> filter(fn: (r) => r[“channel”] == “0”)

but how can I remove all of those bad value and keep the right ones ?

Thanks,
Vincent

2

@vgeannin You can’t delete data based on a value threshold. You can only delete data based on time, measurement, or tag. You have a few options to dealing with these errant points.

Query and write data with errant values filtered out to a new measurement

If you really need to delete these points, you can query all the data of our your current measurement, filter out the errant values, and write the filtered data to a new measurement. It would look something like this:

from(bucket: "rtl_433")
    |> range(start: 2022-11-26T02:57:34.400630731Z, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Bresser-6in1")
    |> filter(fn: (r) => r["channel"] == "0")
    |> filter(fn: (r) => r["_field"] == "rain_mm")
    |> filter(fn: (r) => r["_value"] <= 4000)
    |> set(key: "_measurement", value: "new-measurement-name")
    |> to(bucket: "rtl_433")

Update the errant values

If all the errant points are off by a specific scale, you can query just those values, update them to account for the errant scale, and then write the updated points back to the same measurement. Points are uniquely identified by their timestamp, measurement, and tag set, so if those are the same, InfluxDB will update the existing points on write with the new field values. It would look something like this:

from(bucket: "rtl_433")
    |> range(start: 2022-11-26T02:57:34.400630731Z, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "Bresser-6in1")
    |> filter(fn: (r) => r["channel"] == "0")
    |> filter(fn: (r) => r["_field"] == "rain_mm")
    |> filter(fn: (r) => r["_value"] >= 4000)
    |> map(fn: (r) => ({r with _value: r._value / 100}))
    |> to(bucket: "rtl_433")
3

So If I understand the first query will move outside the measurement the wrong values to a new measurement

so after I can delete this new measurement right ? and then all the bad value will be removed ?

Vincent

4

@vgeannin

No, I changed the comparison operator in the filter from >= 4000 to <= 4000 so it filters out all the bad values. So you’re writing all the good data to the new measurement. Note, this doesn’t account for the other good data in that measurement with different tags/fields. Those would need to be moved to the new measurement as well. Once all the good data is moved to the new measurement, you would then delete the old measurement with the bad data.

5

OK, but instead of moving the good value to the new measurement, can I only move the bad value to the new measurement ?

Vincent

6

@vgeannin No, querying data and writing it to a new measurement doesn’t remove it from the old measurement.