Splunk Search

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

Roy1
Explorer

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

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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?

TimeEquipmentIDStatusJobID
12:001X10
12:011"null"10
12:021"null"10
12:031Y10
12:041Y10
12:051Y10
12:061Y10
12:071X10
12:081X10
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Explorer

This works perfectly thank you very much.

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...