topic Re: How to aggregate states that have a "new" and a "gone" event? in Splunk Search

How to aggregate states that have a "new" and a "gone" event?

gschr — Tue, 29 Sep 2020 08:37:05 GMT

Hi,
I have a sequence of data describing state changes of a device. Now this device can have multiple state_codes at once or even no state_code at some points in time.
The data I have only implies if a new state_code is set or unset. It looks similar to this:

00:01 037 new
00:03 037 gone
00:05                  some other event
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

Is it possible to describe the other events by listing the state_codes that were valid at this point in time?

I mean somthing like this:

00:05 some other event, state_codes=""
00:17 some other event, state_codes="425,539"
00:21 some other event, state_codes=""

I thought of something like a streamstats command but I don't know how to use a stats function that deals with a "gone" event.

P.S. One could easily map the same problem to some users logging in and out and you want to tell which users were logged in at the moment an event arrived.

Re: How to aggregate states that have a "new" and a "gone" event?

renjith_nair — Fri, 29 Jan 2016 13:41:39 GMT

If your events are time based, then try something like below

your search
|sort _time|table _raw _time state_codes
|streamstats current=f window=2 list(state_codes) as list_codes|table _raw _time state_codes list_codes
|search NOT state_codes="*"|eval codes=mvjoin(list_codes,",")|table _raw codes

Re: How to aggregate states that have a "new" and a "gone" event?

gschr — Fri, 29 Jan 2016 14:12:01 GMT

OK so I did:

| stats count | fields - count 
| eval events="
 00:01 037 new;
 00:03 037 gone;
 00:05                  some other event;
 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 |table _raw _time state_codes 
| streamstats current=f window=2 list(state_codes) as list_codes | table _raw _time state_codes list_codes
| search NOT state_codes="*" |eval codes=mvjoin(list_codes,",") |table _raw codes

but that gives me

00:05 some other event  037,037
00:17 some other event  425,539
00:21 some other event  539,425

Re: How to aggregate states that have a "new" and a "gone" event?

somesoni2 — Fri, 29 Jan 2016 15:52:00 GMT

Try something like this

| stats count | fields - count 
 | eval events="
  00:01 037 new;
  00:03 037 gone;
  00:05                  some other event;
  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 |table _raw _time state_codes new_or_gone eventtext  | eval active=if(new_or_gone="new",state_codes,null()) | eval rank=if(isnull(new_or_gone),1,0) | accum rank | eventstats values(active) as state_codes by rank delim="," | where isnull(new_or_gone) | table _time,eventtext , state_codes |  nomv state_codes

Re: How to aggregate states that have a "new" and a "gone" event?

gschr — Tue, 29 Sep 2020 08:37:10 GMT

Unfortunately that's not what I was looking for either.

I get a state_code 037 at 00:05 even though by that time state_code 037 has already been marked as gone. This code seems to ignore the gone messages completely.

Re: How to aggregate states that have a "new" and a "gone" event?

renjith_nair — Sat, 30 Jan 2016 02:46:18 GMT

Isn't that what you wanted? what's the change from this output ? If it's about formatting just replace the last part with below

 | sort _time |table _raw _time state_codes 
 | streamstats current=f window=2 list(state_codes) as list_codes | table _raw _time state_codes list_codes
 | search NOT state_codes="*" |eval codes=mvjoin(list_codes,",") |eval result=_raw.","."state_codes=\"".codes."\""|table result

Re: How to aggregate states that have a "new" and a "gone" event?

gschr — Mon, 01 Feb 2016 08:11:01 GMT

No it's not just about formatting the output.

The third field (new or gone) describes whether the status code is set to active or not active.

So let's assume that there were no active state codes at point 00:00

 00:00 some event     active_state_codes=""

Starting at 00:01 the state code 037 gets active. If there was an event at 00:02 it would be

 00:02 some event     active_state_codes="037"

at 00:03 the state code 037 is set to not active again. So there are no active state codes at this time.

 00:04 some event     active_state_codes=""

Re: How to aggregate states that have a "new" and a "gone" event?

renjith_nair — Mon, 01 Feb 2016 10:12:33 GMT

Alright, got it , try below

| stats count | fields - count 
 | eval events="
  00:01 037 new;
  00:03 037 gone;
  00:05                  some other event;
  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}) ((?\d{3}) (?new|gone)|(?[^\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=f window=2 list(alive) as list_codes
|eval codes=ltrim(rtrim(mvjoin(list_codes,","),","),",")
|table _raw _time eventtext state_codes new_or_gone Final codes

For Final formatted result add below as well,

|search NOT state_codes="*"
|eval result=_raw.","."state_codes=\"".codes."\""|table result

The above gives me

00:05 some other event,state_codes=""
00:17 some other event,state_codes="425,539"
00:21 some other event ,state_codes=""

Re: How to aggregate states that have a "new" and a "gone" event?

gschr — Mon, 01 Feb 2016 18:38:30 GMT

Great answer! Thank you renjith. I wouldn't have made it to that point.

Unfortunately it's still not 100% what I wanted. Let's say the event at 00:03 never happened. So state_code 037 is active all the time (because it's never marked as gone). Then at point 00:17 it should still be active but it isn't.

| stats count | fields - count 
 | eval events="
  00:01 037 new;

  00:05                  some other event;
  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}) ((?\d{3}) (?new|gone)|(?[^\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=f window=2 list(alive) as list_codes
 |eval codes=ltrim(rtrim(mvjoin(list_codes,","),","),",")
 |table _raw _time eventtext state_codes new_or_gone Final codes

gives me

    00:17   some other event                425,539

instead of

    00:17   some other event                037,425,539

Re: How to aggregate states that have a "new" and a "gone" event?

renjith_nair — Tue, 02 Feb 2016 06:33:13 GMT

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