topic Re: Show the current duration of equipment where the Status is not "null" in Splunk Search

Show the current duration of equipment where the Status is not "null"

Roy1 — Mon, 12 Feb 2024 13:09:32 GMT

Hello, I have the following data:

I want to use this data to setup a dashboard. In this dashboard I want to show the current duration of equipment where the Status is not "null" (null is a string in this case and not a null value)

Each JobID only has one EquipmentID
The same status can occur and disappear multiple times per JobID
There are around 10 different status
I want to the results to show only durations above 60 seconds

If the current time is 12:21 I would like the to look like this.

EquipmentID	Duration	Most_recent_status
2	120	Z

Time	EquipmentID	Status	JobID
12:00	1	"null"	10
12:01	2	"null"	20
12:02	2	X	20
12:03	2	X	20
12:04	1	X	10
12:05	1	Y	10
12:06	1	Y	20
12:07	2	Y	20
12:08	1	X	10
12:09	2	Y	20
12:10	1	"null"	11
12:11	2	"null"	21
12:12	2	"null"	21
12:13	1	"null"	11
12:14	1	"null"	11
12:15	2	X	21
12:16	1	X	11
12:17	2	X	21
12:18	1	"null"	11
12:19	2	Z	21
12:20	2	Z	21

This is the query I use now only the duration_now resets every time a new event occurs

index=X sourcetype=Y JobID!=”null” |sort _time 0 | stats last(_time) as first_time last(Status) as "First_Status" latest(status) as Last_status latest(_time) as latest_times values(EquipmentID) as Equipment by JobID | eval final_duration = case(Last_status ="null", round(latest_times - first_time,2)) | eval duration_now = case(isnull(final_duration), round(now() - first_time,2)) | eval first_time=strftime(first_time, "%Y-%m-%d %H:%M:%S") | eval latest_times=strftime(latest_times, "%Y-%m-%d %H:%M:%S") | sort - first_time

Any help would be greatly appreciated

Re: Show the current duration of equipment where the Status is not "null"

ITWhisperer — Mon, 12 Feb 2024 11:48:33 GMT

If I understand correctly, you want to know when the equipment changed to its current status so long as the current status is not "null"?

Try something like this:

| eventstats last(Status) as lastStatus by EquipmentID | where lastStatus!="null" | streamstats last(Status) as previous current=f global=f by EquipmentID | where Status=lastStatus and Status != previous | stats last(_time) as lastTime last(Status) as lastStatus by EquipmentID | eval duration=now()-lastTime

Re: Show the current duration of equipment where the Status is not "null"

PickleRick — Mon, 12 Feb 2024 11:52:21 GMT

If I understand your requirements correctly, the easiest approach would be to use the transaction command with relatively low thresholds for transaction continuity. But the transaction command is relatively resource-intensive so you might want to try streamstats-based approach instead.

Re: Show the current duration of equipment where the Status is not "null"

Roy1 — Mon, 12 Feb 2024 12:15:22 GMT

Thanks for the reply unfortunately it doesn’t seem to work completely.
I have the timerange set to the previous 15 minutes

what I think happens is that the query takes the first !=null and starts the duration from there. I fixed this by adding |sort _time 0 to the top of the query but then it only tracks the time of the last status. I would like for it to track the total time all statuses are !=null

When I use your query I get durations of ~900 seconds while they are between 1-100

When I add the time sort I only get the duration of X and not X+Y (from the following events)

Do you happen to know how I get the duration of X+Y?

Time	EquipmentID	Status	JobID
12:00	1	X	10
12:01	1	"null"	10
12:02	1	"null"	10
12:03	1	Y	10
12:04	1	Y	10
12:05	1	Y	10
12:06	1	Y	10
12:07	1	X	10
12:08	1	X	10

Re: Show the current duration of equipment where the Status is not "null"

ITWhisperer — Mon, 12 Feb 2024 12:34:44 GMT

This is different to your original question - using this criteria, with your first set of events, the duration would have been 360 not 120, correct?

Re: Show the current duration of equipment where the Status is not "null"

Roy1 — Mon, 12 Feb 2024 12:37:35 GMT

I see, you are correct I made a mistake in my example events the 120 should indeed be 360. Thanks for catching my mistake

Re: Show the current duration of equipment where the Status is not "null"

ITWhisperer — Mon, 12 Feb 2024 12:42:29 GMT

| sort 0 _time | eventstats last(Status) as lastStatus by EquipmentID | where lastStatus!="null" | streamstats last(Status) as previous current=f global=f by EquipmentID | where Status!="null" and previous=="null" | stats last(_time) as lastTime last(lastStatus) as lastStatus by EquipmentID | eval duration=now()-lastTime

Re: Show the current duration of equipment where the Status is not "null"

Roy1 — Mon, 12 Feb 2024 13:02:45 GMT

This works perfectly thank you very much.