Durations of states | group consecutive records in a given state

#1

Example input data with two states (1/2 or A/B) :

_time _value _tag
1 1 B
2 0 A
3 0 A
4 0 A
5 1 B
6 1 B
7 0 A

Target output:

_time _value _tag duration
1 1 B 1
4 0 A 3
6 1 B 2
7 0 A 1

I tried really hard to achieve this output with Flux without succeeding.
My initial intuition is to try to group consecutive records in a given state.
But I can’t find a way. Maybe my approch is wrong.
Is it possible to achieve this output with Flux.

0 Likes

#2

Hi @fatnat,

I’m trying to implement exact the same thing and almost got it.

First of all take a look at the changeDetect function: https://docs.influxdata.com/kapacitor/v1.5/nodes/change_detect_node/

I think something like changeDetect('_tag') should work to group consecutive records.

Afterwards you could calculate the duration between two timestamps using the elapsed function: https://docs.influxdata.com/kapacitor/v1.5/nodes/change_detect_node/#elapsed

The calculated duration records would reside in another measurement which could be joined on the time with the original measurement records. But that would result into:

_time _value _tag duration
1 1 B 0
4 0 A 1
6 1 B 3
7 0 A 2

As you can see the duration is added to the next node.

Using the function stateDuration would be another option (sorry I’m not allowed to post more links).

But this node requires that you have at least two consecutive records for every point to calculate the duration, otherwise the duration is -1.

Maybe someone has an idea how to add the duration calculated using the elapsed function and the merge to the first of the compared records?

Cheers

0 Likes

#3

Thank you @sjahreis,
The main difficulty is grouping consecutive records. And yours seems to be a very nice solution for this. I’ll will give a try.
I’m still curious if this is possible with only using Flux.

0 Likes

#4

I believe I found an important logic flaw in my question.:frowning_face:

Example input data with two states (1/2 or A/B) :

_time _value _tag
1 1 B
2 0 A
3 0 A
4 0 A
5 1 B
6 1 B
7 0 A

I realized that the target output I mentioned in my initial post doesn’t represent the information I have been looking after. In fact this output itself doesn’t represent any accurate and meaningful data.
Looking at more carefully to the input data, it can be observed that the last time point of a state(_tag) before it changes to another state is not the timestamp of the last row/record with this state(_tag).
For example; the first “A” state in this time range starts at timestamp 2 but doesn’t end at the row with timestamp 4. This “A” state in time extends up to the timestamp 5. Timestamp 5 is the time point where state “A” ends and state “B” gets started.
So within an inclusive time range of 1 to 7, I believe a target output like this would be more meaningful.
Target output:

_time _value _tag duration
2 1 B 1
5 0 A 3
7 1 B 2

What do you think?
Is this approach a more accurate way of expressing state duration?
Any ideas?

0 Likes