I am trying to create a dashboard for the Job status and I want to convert the job duration to HH:MM:SS. I use the below Splunk search which gives result, but when the duration is more than 24 hours it outputs 1+10:29:14.000000 and with this I cannot sort the long running jobs. I want the duration always in HH:MM:SS. Any suggestions
My searh|eval starttime = strptime(start,"%m/%d/%Y %H:%M:%S")|eval endtime = strptime(end,"%m/%d/%Y %H:%M:%S") |eval Diff=tostring((endtime-starttime ),"duration")|search Status!=RU|dedup job|table job start end Diff.
So instead of 1+10:29:14.000000 I want to out it as 34:29:14
I'm not positive of your situation, but in our environment we have all our time fields in Epoch time format. When we have it presented in dashboards/searches we use the fieldformat function to change the Epoch time into something human readable. Most commonly we have typically used the fieldformat function when we reference our lookups so that they can be saved back to the lookup without changing the actual data(or changing out/away from the epoch time format in the lookup). For example, something simple for what we use with our lookups:
The search:
| inputlookup MyLookup.csv
| eval MyTimeField=coalesce(MyTimeField, now())
| fieldformat MyTimeField=strftime(MyTimeField, "%m/%d/%Y %H:%M:%S")
| outputlookup MyLookup.csv
Search explanation:
| inputlookup MyLookup.csv
^^^^Brings the lookup into the search
| eval MyTimeField=coalesce(MyTimeField, now())
^^^^Fills the "MyTimeField" with the current Epoch time if it does not already have a value
| fieldformat MyTimeField=strftime(MyTimeField, "%m/%d/%Y %H:%M:%S")
^^^^Changes the "MyTimeField" Epoch times into human readable format
| outputlookup MyLookup.csv
^^^^Saves the newly filled in Epoch time blanks back into the lookup file.
Although the above search would only work if your time field is in Epoch time format, the handy thing about it Epoch time is that when sorting after using the fieldformat function, the time is only presented to the user in human readable format. While all the while under the hood Splunk still sees the "MyTimeField" as the Epoch time format and can sort it accordingly.
I'm not positive if this helps or answers you question. But I have found this useful in the past.
For sorting purposes, it's best to use the integer form of (endtime-starttime). Try something like this.
your search | eval starttime = strptime(start,"%m/%d/%Y %H:%M:%S") | eval endtime = strptime(end,"%m/%d/%Y %H:%M:%S") | eval diff=endtime-starttime | eval Diff=tostring(diff, "duration") | search Status!=RU | dedup job | sort - diff | table job start end Diff
BTW, it would probably improve performance if you could move the search Status!=RU
clause into your base search.
Thanks for the response. It worked. But is it possible to get the result only in HH:MM:SS so that the user can sort the duration (Diff) in any order they want.
Eg: 1+10:29:14 to 34:29:14
It's not the cleanest thing I've ever written, but this is functional:
| makeresults| eval dur=26*60*60 | eval Duration=tostring(dur, "duration") | rex field=Duration "^(?<days>\d+)\+(?<hours>\d+):" | eval Duration=replace(Duration, "((\d+)\+)", "") | eval total_hours=(days*24)+hours | eval Duration=replace(Duration, "(\d+:)(\d+:\d+)", total_hours.":\2")
@elliotproebstel, if your problem is resolved, please accept an answer to help future readers.
There's no built-in option to produce that format. You'll have to do it yourself either using SPL or a custom command.