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,
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.
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
| 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 ?
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.
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