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
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
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
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
@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 ?
Can you pleas share some sample event or table same like you shared in question ? So we can help you on
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.