The more you ask , the more streamstats comes in 🙂 . Try this. Please be aware that if there are any some other event,it will aggregate to that row. So if you don't have an event at 00:03 for gone, it will display 037 at 00:05. Hope that's enough
| stats count | fields - count
| eval events="
00:01 037 new;
00:12 425 new;
00:13 539 new;
00:17 some other event;
00:18 539 gone;
00:19 425 gone;
00:21 some other event
"
|eval events=split(events,";") | mvexpand events | rex field=events "(?<_raw>(?<_time>\d{2}:\d{2}) ((?<state_codes>\d{3}) (?<new_or_gone>new|gone)|(?<eventtext>[^\d]*)))"
|fields state_codes new_or_gone eventtext
|sort _time
|eval event_id=if(isnull(new_or_gone),1,0) | accum event_id
|eventstats last(new_or_gone) as Final by state_codes,event_id
|eval alive=if(Final=="new",state_codes,"")
|streamstats current=t list(alive) as list_codes by event_id
|eval codes=ltrim(rtrim(mvjoin(list_codes,","),","),",")
|streamstats current=f window=1 first(codes) as final_codes
|table _raw _time eventtext state_codes new_or_gone final_codes
|search NOT state_codes="*"
|eval result=_raw.","."state_codes=\"".final_codes."\""|table result
This is a quick and dirty one. You might be able to optimize this by reducing the eventstats and streamstats
... View more