Identify events based on the incremental change of a value

[punichannibal]

Hi,

I have have a list of events that contain a customer ID. I'm trying to detect when I have a sequence of events with incremental changes to the ID

Example:

- event A - ID0

- event B - ID1

- event C- ID2

- event D - ID3

I might have other events between these increments that could have unrelated IDs (i.e: event A ID0 - event H ID 22, event B ID1)

I've tried using | streamstats current=f last(CustomerID) as prev_CustomerID
| eval increment = CustomerID - prev_CustomerID but without any luck.

Do you guys know a way this could be achieved ?

[ITWhisperer]

Assuming ID is a numeric, your solution should work. You could also try range with window of 2. Here is a runanywhere example demonstrating both techniques

| makeresults format=csv data="event,id
A,1
B,2
C,4
D,5"
| streamstats range(id) as range window=2
| streamstats current=f last(id) as prev_id
| eval increment=id-prev_id

[punichannibal]

Hello, 

Thank you for the answer. Indeed trying a range with a windows of 2 spawns results. However, I'm not picking up on the first start of the sequence (ID 0 and ID 1)  but only the last 4 IDs ( 2/3/4/5)

Any ideas ?

[ITWhisperer]

What were you expecting for the first id if there is no previous row?

[punichannibal]

I see your logic, my bad. I'm trying to identify the start of the sequence as well even thought there is no increment based on the previous row.

[ITWhisperer]

Try something like this

| eval range=coalesce(range, id)

[punichannibal]

I'm not sure where this goes, can you please explain what it changes?

[ITWhisperer]

Perhaps if you provided some more realistic (but anonymised) sample events, and a representation of the output you are trying to achieve, we may be able to help you to a solution.

[ITWhisperer]

| makeresults format=csv data="event,id
A,1
B,2
C,4
D,5"
| streamstats range(id) as range window=2
| eval range=coalesce(range, id)