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

``````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

``````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!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...