Get data only at points where value changes from previous timestamp

Trying to see if there’s a flux query to do the following operation.

My data looks like (sorry for bad formatting):

_time _value
0 1
1 1
2 0
3 0
4 0
5 3
6 1
7 1

and I want to pull out data only where the _value changes, so my output table would be:

_time _value
0 1
2 0
5 3
6 1

Is this possible? I kind of see it like a moving “unique” window that resets at each value transition.

Hello @Grant_Bridges,
Sorry for the delay. Sometimes questions get lost.
Yes you can use the monitor package. monitor.stateChangesOnly() function | InfluxDB OSS 2.0 Documentation
Please note you’ll have to either rename your column to level or duplicate it.
duplicate() function | InfluxDB OSS 2.0 Documentation
rename() function | InfluxDB OSS 2.0 Documentation

Thanks for the reply - I’d seen that functionality, but thought it was specific to pre-defined “_level” values (like “ok”, “warn”, “crit”, as shown in the examples). But good to know, I’ll definitely give it a shot and see if it works for our purposes.

I gave it a quick test - renaming the column works fine, but it seems using the “monitor” package requires you to be referencing the “_monitoring” bucket, so I don’t think this does quite what I want. I need to access a specific bucket and do this functionality for specific values in a given column.

Hello @Grant_Bridges,
You don’t need to reference the “_monitoring” bucket. I’ve used it for several other buckets. Here’s an example TL;DR InfluxDB Tech Tips – How to Monitor States with InfluxDB | InfluxData
Let me know if that helps, or if I can help any further.
Thank you

Thanks for the help, sorry it took a bit for me to get back. Your blog post was especially helpful in this - I am so close to having it work, but it seems the “stateChangesOnly” call isn’t quite working for me. I’m able to detect specific changes “from”, “to”, but every time I do stateChangesOnly, I get no results back. After trying many combinations, casts, etc, it seems like there may just be some bug in the functionality?

This is my exact query- “Channel” is a tag and _value is an int, numbers 0-7, that I’m trying to catch changes on (hence the “toString()” cast).

import "influxdata/influxdb/monitor"

from(bucket: "ProcessedData")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "567a0306-7cd8-4dad-b97a-c2777d33d7f4")
  |> filter(fn: (r) => r["Channel"] == "Bin")
  |> toString()
  |> rename(columns: {"_value": "_level"})
  |> monitor.stateChangesOnly() // <- doesn't work
  // |> monitor.stateChanges(fromLevel: "any", toLevel: "any") // <- doesn't work
  // |> monitor.stateChanges(fromLevel: "0", toLevel: "4") // <- DOES work

@Grant_Bridges here’s a Flux issue related to state changes: Add stateChanges function to the standard library · Issue #3582 · influxdata/flux · GitHub

1 Like

Thanks Scott, I subscribed to the ticket. I’ll look forward to seeing if this gets implemented.