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
... View more
