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

[Roy1]

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

[PickleRick]

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.

[ITWhisperer]

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

[Roy1]

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

[ITWhisperer]

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?

[Roy1]

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

[ITWhisperer · Accepted solution]

| 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

[Roy1]

This works perfectly thank you very much.