How to count how many times a field value has changed from one to another?

[anphan1992]

Hi,
In my data I have a "Status" field. The status can be in one of 3 states: Connected, Connecting, Disconnected. I want to calculate how many times the connection has been dropped. In other words, I want to count the number of times the status goes from "Connected" to "Disconnected".

Any ideas?

Thanks.

[aholzer · Accepted solution]

Using streamstats you can achieve see what the previous value of status was:

... | streamstats current=f window=1 last(status) as prev_status

This will give you on every event (except the first one) the status of your previous event. You can then use a search to look for the case where current status is "disconnected" and previous status is "connected":

... | streamstats current=f window=1 last(status) as prev_status | search status="disconnected" AND prev_status="connected"

If you simply need the count then add a stats count:

... | streamstats current=f window=1 last(status) as prev_status | search status="disconnected" AND prev_status="connected" | stats count

Hope this helps