Splunk Search
Highlighted

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)
0 Karma
Highlighted

Re: Group by repeating value at value change

SplunkTrust
SplunkTrust

@aseadmin 

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

 

View solution in original post

Highlighted

Re: Group by repeating value at value change

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 ?

0 Karma
Highlighted

Re: Group by repeating value at value change

SplunkTrust
SplunkTrust

@aseadmin 

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

0 Karma
Highlighted

Re: Group by repeating value at value change

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.

0 Karma
Highlighted

Re: Group by repeating value at value change

Motivator

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

 

0 Karma