Splunk Search

Query for scenario with status change

sidhantbhayana
Path Finder

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.

Tags (1)
0 Karma

sundareshr
Legend

Try this

your base search | chart count over Date by Status
0 Karma

sidhantbhayana
Path Finder

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

0 Karma

sidhantbhayana
Path Finder

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.

0 Karma

sundareshr
Legend

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
0 Karma

sidhantbhayana
Path Finder

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

0 Karma

sundareshr
Legend

For cumulative results, add accum count to the end

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...