Group by repeating value at value change

[aseadmin]

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

[bowesmana]

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

[kamlesh_vaghela · Accepted solution]

@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

[aseadmin]

@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 ?

[kamlesh_vaghela]

@aseadmin 

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

[aseadmin]

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.