topic Re: Query for scenario with status change in Splunk Search

Query for scenario with status change

sidhantbhayana — Fri, 09 Sep 2016 12:42:17 GMT

Hi All,

I have a scenario where an entity when enrolled has many status i.e.

EntityName Date Status
Entity1 01-03-2016 In Progress
Entity1 21-03-2016 Active
Entity1 04-04-2016 Blocked
Entity1 05-04-2016 Inactive
Entity1 10-05-2016 Active

I need to create column chart with time on x axis (mm/yyyy) format and y axis as status. Separate charts for all status, for example status is active then the chart should show for active scenario 1 as count for 05/2016 and for all other scenarios it should show zero.

Re: Query for scenario with status change

sundareshr — Fri, 09 Sep 2016 13:07:01 GMT

Try this

your base search | chart count over Date by Status

Re: Query for scenario with status change

sidhantbhayana — Fri, 09 Sep 2016 17:15:25 GMT

Hi Sundareshr

We have to show monthly trend, so one entity may be active in one month and might get inactive next month due to inactivity.

Active Entity Changes by Month is the actual requirement, if an entity is was active in 03/16 and it became inactive in 04/16 and then again in the same month it got active so our chart should display the data only for Final status as active in the month of april. Similarly, other entity has a status in the below fashion

05/16 E2 P
05/16 E2 A
06/16 E2 I

Here it should show Inactive entity changes by month in the month of june and in active it should not display in that month.

Re: Query for scenario with status change

sundareshr — Fri, 09 Sep 2016 17:56:19 GMT

Try this

... | eval date=strptime(date, "%d-%m-%Y") | sort - date | eval date=strftime(date, "%m-%Y") | dedup date | chart count over date by status

Re: Query for scenario with status change

sidhantbhayana — Tue, 29 Sep 2020 10:56:28 GMT

Hi Sundareshr
I am using this logic currently but when we check for total active entities and compare with this query there is a difference of two or three entites.
eval Date = strftime ("_time","%Y/%m")| stats last (_time) as _time last (status) as status by entityId l, Date|eventstats first (Date) as Date1by entityId|streamstats first (status) as statusnew by entityId window=2 | eval status1 = case ( (Date1=Date AND status=A),1,(Date1!=Date AND status1!=statusnew AND status=A),1,(Date!=Date AND status1!=statusnew),-1,(Date1!=Date AND status=statusnew),0,(Date1=Date AND status=I) OR (Date1=Date AND status=P) ,0, (Date1!=Date AND status=P) OR ( Date1!=Date AND status=I) ,0))|timechart span=mon sum(status1) as Active|accum Active

Re: Query for scenario with status change

sidhantbhayana — Sun, 11 Sep 2016 16:25:01 GMT

also we need to show a cumulative result at the end.

Re: Query for scenario with status change

sundareshr — Mon, 12 Sep 2016 00:44:24 GMT

For cumulative results, add accum count to the end