I am working to find the difference between two events and have the following:
| stats earliest(_time) as startTime, latest(_time) as endTime by JobName
| eval time=strftime(_time, "%H:%M:%S")
| eval runTime=(endTime-startTime)
| eval runTime=strftime(runTime,"%H:%M:%S")
| table JobName, runTime
I am getting back a runTime of 22:43:35, which is not what I expect or want. I would like the time to show starting from 0, I want the runTime to show in a format of 03:23:00; 3 hours and 23 minutes.
Try this:
... | stats range(_time) AS runTime BY JobName
| table JobName, runTime
| fieldformat runTime = tostring(runTime, "duration")
Give this a try
| stats earliest(_time) as startTime, latest(_time) as endTime by JobName
| eval time=strftime(_time, "%H:%M:%S")
| eval runTime=(endTime-startTime)
| eval runTime=tostring(runTime,"duration")
| table JobName, runTime
Minor Revisions 😛
| stats earliest(_time) as startTime, latest(_time) as endTime by JobName
| eval runTime=(endTime-startTime)
| eval time=strftime(runTime, "%H:%M:%S")
| table JobName, time
Yeah, tostring(runTime,"duration")
should work.
Would this mean that I cannot use the runTime in a timechart? The runTime is the running of jobs and I was looking to show the daily runs to graph over time.
runtime is just the number of seconds between start and end time. Not sure how you envisioned timecharting that 🙂
You might want to take a look at visualization add-ons that are perhaps more suitable for displaying job runtimes over time. e.g.: https://splunkbase.splunk.com/app/3120/
Also: not sure what that | eval time=strftime(_time, "%H:%M:%S")
is doing there, as there is no _time
field anymore after that stats command. But then again, you're not using that field anyway.
PS: you could also do | stats range(_time) as runTime by JobName| eval runTime=tostring(runTime,"duration")
. But if you really want to visualize the job run on a timescale, you are probably going to need those start and end time values.
That makes sense. The idea was that these jobs run every day so I wanted to keep track of the run time to chart how long it is taking over time; really to trend the items over time.
You could of course just timechart the daily runtime value by assigning _time = startTime
and then running a timechart with a span=1d
. That doesn't visualize when the job started and stopped exactly (I thought that is what you were after), but does allow you to show a line/bar chart to show the runtime trend over time.
For example:
| stats earliest(_time) as startTime, latest(_time) as endTime by JobName
| eval _time=startTime
| eval runTime=(endTime-startTime)
| timechart span=1d avg(runTime) as runTime by jobName
Note: this charts the runtime as a number of seconds. If jobs typically take several minutes or hours to run, you might be better of displaying it as number of minutes. For example:
| stats earliest(_time) as startTime, latest(_time) as endTime by JobName
| eval _time=startTime
| eval runTime=round((endTime-startTime)/60,1)
| timechart span=1d avg(runTime) as runTime by jobName
runTime was in seconds, after using tostring we have converted into string. You can use integer in timechart not string.
But I don't think timechart will fit here, you can use Timeline visualization.
@somesoni2 That worked perfect and exactly what I was looking for.