Splunk Search

## Group by repeating value at value change

Explorer

Hi, I require a little help here as i having spent a lot of time researching for a solution without any luck

I have a vehicle idling data where the data is like below,

MACHINE    STATUS

A                      1

A                      1

A                      1

A                      0

A                      0

A                      1

A                      1

can i get a consolidated view from the above data in below consolidated way ?

MACHINE    STATUS    COUNT

A                      1                  3

A                      0                  2

A                      1                  2

I wanted the data in the above way, so that i know for long the machine was idling at various part of time rather than how long the machine was idling during a filtered time

any help will be much appreciated.

Thank you

Labels (5)

• ### timechart

1 Solution
SplunkTrust

Can you please try this?

``````YOUR_SEARCH | eval a=1| accum a
| streamstats window=2 first(STATUS) as F last(STATUS) as L | eval b=if(F!=L,a,null()) | filldown b |fillnull value=0 b | stats count by b,STATUS | table STATUS count``````

Sample Search:

``````| makeresults
| eval _raw="MACHINE    STATUS

A                      1

A                      1

A                      1

A                      0

A                      0

A                      1

A                      1"
| multikv
| table MACHINE STATUS | eval a=1| accum a
| streamstats window=2 first(STATUS) as F last(STATUS) as L | eval b=if(F!=L,a,null()) | filldown b |fillnull value=0 b | stats count by b,STATUS | table STATUS count``````

SplunkTrust

This will give you the exact result from your example, but I am pretty sure it's an inelegant solution that can be improved on...

``````| makeresults
| eval data="A:1,A:1,A:1,A:0,A:0,A:1,A:1"
| makemv delim="," data
| mvexpand data
| rex field=data "(?<machine>\w):(?<status>\w)"
| fields - data
| streamstats reset_on_change=t c by machine status
| reverse
| streamstats reset_on_change=t max(c) as count by machine status
| where c=count
| reverse
| table machine status count``````

SplunkTrust

Can you please try this?

``````YOUR_SEARCH | eval a=1| accum a
| streamstats window=2 first(STATUS) as F last(STATUS) as L | eval b=if(F!=L,a,null()) | filldown b |fillnull value=0 b | stats count by b,STATUS | table STATUS count``````

Sample Search:

``````| makeresults
| eval _raw="MACHINE    STATUS

A                      1

A                      1

A                      1

A                      0

A                      0

A                      1

A                      1"
| multikv
| table MACHINE STATUS | eval a=1| accum a
| streamstats window=2 first(STATUS) as F last(STATUS) as L | eval b=if(F!=L,a,null()) | filldown b |fillnull value=0 b | stats count by b,STATUS | table STATUS count``````

Explorer

@kamlesh_vaghela thanks kamlesh. thats what i was looking at. Just to add to that, if i need to get a max and min value of a field in this grouping say "fan_speed" or "cpu_load" values which i have, where would i add those evaluations ?

SplunkTrust

Can you pleas share some sample event or table same like you shared in question ? So we can help you on

Explorer

No worries, i found a way out extending from your query. The query you shared was what i required to start with (a solution i didn't find anywhere). Thanks once again.

Get Updates on the Splunk Community!

#### Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

#### There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

#### Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? &#x1f680; We invite you to join our elite squad ...