How to query a table that shows field value changes using streamstat?

[BorisT]

I am trying to get my query to work correctly and display it in a table format for easy analysis.

The fields I am using are:

Host

device_active

Device_enabled

_time

I am trying to track changes from device_active being enabled ("2") to becoming disabled ("1").  I want to display a table that shows which hostnames, within the last 2-4hrs, have changed from enabled to disabled.  If possible add traceability. 

Device_active="1" ----->disabled

Device_active="2" ------>enabled

I tried following some tutorials but could not get it work correctly:

https://splunkonbigdata.com/find-out-the-errors-occurring-2-or-more-times-consecutively/

https://community.splunk.com/t5/Splunk-Search/How-to-count-how-many-times-a-field-value-has-changed-...

_______________________________________

Currently, I have the following query:

index="log-main" sourcetype=monitoring device_active earliest=-4h latest=-2h

| table host, device_active, device_enabled, _time | dedup host

| streamstats current=f window=1 max(device_active) as prev_status

| eval isConsecutive = if (device_active == Previous_error, 1, 0)

| streamstats count as count by device_active reset_before=(isConsecutive==0)
| streamstats count(eval(isConsecutive==0)) as #ofdisconnects

Which is producing the following:

Host

device_enabled

device_active

time

#ofdisconnects

count

isconsecutive

prev_status

This is currently showing "all" hostnames and not filtering out "just" the ones that have changed statuses.  I'd like to display the following information, but filtered down to just the hosts that have "device_active" disabled, but recently were enabled.

[bowesmana]

You are using dedup to remove all duplicate hosts - that seems fundamentally wrong in that if you want to find two events where device_active has different values, it will be impossible, as you've removed all but one event for every host.

You also need to run streamstats using global=f flag and also split "by host", so that it will collect the previous values per host, not for any event.

See what that gives you and let's go from that point.