Splunk Search

Group by repeating value at value change

aseadmin
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
1 Solution

kamlesh_vaghela
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

bowesmana
SplunkTrust
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

 

0 Karma

kamlesh_vaghela
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

 

aseadmin
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

kamlesh_vaghela
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

aseadmin
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...