Count values changes only when value in a column changes

[nabeel652]

Hello wonderful Splunk community,

I have some data where I want count to change only when status changes:

Status   Count
-------------------
Online      1
Online      1
Online     1
Break      2
Break       2
Online       3
Online       3
Lunch       4
Lunch        4
Lunch       4
Offline     5
Offline    5

Any help appreciated. 

[gcusello]

Hi @nabeel652 ,

if you already extracted the status field, you could try something like this:

<your_search>
| autoregress status as status_old p=1
| table _time status status_old
| where NOT status=status_old

Ciao.

Giuseppe

[nabeel652]

Thank you for the reply 🙂

I was able to achieve the same with

| streamstats reset_on_change=true count by Activity
| where count==1

But I want a count field that just increments when it senses a change in status.
so I can do my 
| stats earliest(_time) as startTime, latest(_time) as endTime by status, count
or something like that...

[gcusello]

Hi @nabeel652 ,

did you tried with accum?

<your_search>
| autoregress status as status_old p=1
| table _time status status_old
| where NOT status=status_old
| eval NO=1 
| accum NO

Ciao.

Giuseppe

[nabeel652]

Thank you

I don't want to omit any records. This sort of gives me the required results but records are missing which I don't want. I want same number of rows after the solution is applied. 

[gcusello · Accepted solution]

Hi @nabeel652 ,

ok, please try this:

<your_search>
| autoregress status as status_old p=1
| table _time status status_old
| eval NO=0
| foreach NO
     [ eval NO=if(status=status_old,NO,NO+1)]
| accum NO

that I tested (and runs) in this way:

| makeresults | eval _raw= "Online      1"
| append [ | makeresults | eval _raw= "Online      1"]
| append [ | makeresults | eval _raw= "Online     1"]
| append [ | makeresults | eval _raw= "Break      2"]
| append [ | makeresults | eval _raw= "Break       2"]
| append [ | makeresults | eval _raw= "Online       3"]
| append [ | makeresults | eval _raw= "Online       3"]
| append [ | makeresults | eval _raw= "Lunch       4"]
| append [ | makeresults | eval _raw= "Lunch        4"]
| append [ | makeresults | eval _raw= "Lunch       4"]
| append [ | makeresults | eval _raw= "Offline     5"]
| append [ | makeresults | eval _raw= "Offline    5"]
| rex "^(?<status>\w+)"
| autoregress status as status_old p=1
| table _time status status_old
| eval NO=0
| foreach NO
     [ eval NO=if(status=status_old,NO,NO+1)]
| accum NO

Ciao.

Giuseppe

[nabeel652]

I was able to do it by some other way but your solution is cleaner and elegant. Thanks for the help