topic Create a backlog summary with the lastest status in Splunk Search

Create a backlog summary with the lastest status

francoisternois — Wed, 22 Dec 2021 16:38:37 GMT

Hi there,

I'm trying to do a search that look at the latest status of a given actionid everyday to make a kind of day by day backlog (event if no action was done). To be clearer, let's say we have the events :

12/01/2021 actionid=actionid1 status=start
12/02/2021 actionid=actionid2 status=start
12/06/2021 actionid=actionid1 status=sent
12/08/2021 actionid=actionid2 status=sent
12/09/2021 actionid=actionid1 status=done
12/10/2021 actionid=actionid2 status=done

The status of a given action doesn't evolve (until it change) but I've no event before it change. And the result that I want is something like (representing the backlog of the actionid by status day by day) :

	start	sent	done
12/01/2021	1	0	0
12/02/2021	2	0	0
12/03/2021	2	0	0
12/04/2021	2	0	0
12/05/2021	2	0	0
12/06/2021	1	1	0
12/07/2021	1	1	0
12/08/2021	0	2	0
12/09/2021	0	1	1
12/10/2021	0	0	2

I hope my request is clear enough. Any help would by great 🙂

Best regards,

Francois

Re: Create a backlog summary with the lastest status

johnhuang — Wed, 22 Dec 2021 20:57:40 GMT

There are probably better ways, here's my stab at it:

| makeresults | eval _raw="event_time=12/01/2021 actionid=actionid1 status=start, event_time=12/02/2021 actionid=actionid2 status=start, event_time=12/06/2021 actionid=actionid1 status=sent, event_time=12/06/2021 actionid=actionid2 status=sent, event_time=12/09/2021 actionid=actionid1 status=done, event_time=12/10/2021 actionid=actionid2 status=done" | eval events=SPLIT(_raw, ",") | mvexpand events | rex field=events "event_time=(?<event_time>[^\s]*)\sactionid=(?<actionid>[^\s]*)\sstatus=(?<status>[^\s]*)" | eval _time=strptime(event_time, "%m/%d/%Y") | bucket _time span=1d | eval epoch_time=_time | append [| makeresults | eval date_range=mvrange(relative_time(now(), "-365d"), relative_time(now(), "-0d"), 86400) | mvexpand date_range | eval _time=date_range | bucket _time span=1d | table _time] | eventstats min(epoch_time) AS start_time max(epoch_time) AS end_time | where _time>=start_time AND _time<=end_time | eval {status}=1 | eval start=IF(sent=1, sent * -1, start) | eval sent=IF(done=1, done * -1, sent) | sort 0 _time | fillnull value=0 start sent done | streamstats min(_time) AS min_time max(_time) AS max(_time) sum(start) AS start sum(sent) AS sent sum(done) AS done | dedup _time | table _time start sent done

Re: Create a backlog summary with the lastest status

somesoni2 — Wed, 22 Dec 2021 21:45:38 GMT

Give this a try