Query for scenario with status change

[sidhantbhayana]

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.

[sundareshr]

Try this

your base search | chart count over Date by Status

[sidhantbhayana]

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

[sidhantbhayana]

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.

[sundareshr]

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

[sidhantbhayana]

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

[sundareshr]

For cumulative results, add accum count to the end