Why is my search adding 18 hours to the value of my response time field?

[ksing]

Hi,

I am running a search to find out the response time using the below query:

mysearch | |eval diffResponse= ackTime - triggerTime 
|eval responseTime=strftime(diffResponse, "%H:%M:%S" )
|eval alertTriggerTime=strftime(triggerTime, "%b %d %H:%M:%S" )
|eval alertAckTime=strftime(ackTime, "%b %d %H:%M:%S" )
|eval ResponseMeanTime=strftime(ResponseMean, "%H:%M:%S" )
|table rule_id,alertTriggerTime,alertAckTime,responseTime

where ackTime and triggerTime are in epoch time. When this search is running, it is adding 18 hours to the value of responseTime. Attached the screenshot.

Kindly help on this.

[rjthibod · Accepted solution]

Per my recent post on this issue, https://answers.splunk.com/answers/480763/why-am-i-unable-to-convert-a-duration-value-to-a-h.html#an..., you are using the wrong eval function to generate the human readable form of the duration.

strftime() expects a epoch time value. You need to use tostring() on any fields that are time values not based on epoch time. For example, replace your first eval with the following. You may need to do the same for your field "ResponseMeanTime". calculated.

|eval responseTime= tostring(diffResponse, "duration")

So, the update search would be as follows, where I removed the "ResponseMeanTime" value since you don't appear to use it.

 mysearch | ...
 |eval diffResponse= ackTime - triggerTime 
 |eval responseTime=tostring(diffResponse, "duration" )
 |eval alertTriggerTime=strftime(triggerTime, "%b %d %H:%M:%S" )
 |eval alertAckTime=strftime(ackTime, "%b %d %H:%M:%S" )
 |table rule_id,alertTriggerTime,alertAckTime,responseTime

[ksing]

Thanks rjthibod for your assistance. When I was trying this

I have created the below search for calculating the Response Mean time:

| incident_review |chart values(_time) over rule_id by status_label |join rule_id [search notable | search NOT suppression owner!=unassigned| rename _time as triggerTime ]
| rename "Resolved" as resolvedTime, "Closed" as closedTime "In Progress" as inProgressTime, "Pending" as pendingTime
|eval ackTime = case(isnotnull(inProgressTime), inProgressTime, isnotnull(pendingTime), pendingTime, isnotnull(resolvedTime), resolvedTime, isnotnull(closedTime), closedTime, 1=1 , "Unassigned")
|eval diffResponse= ackTime - triggerTime
|eventstats avg(diffResponse) as ResponseMean
|eval responseTime=strftime(diffResponse, "%H:%M:%S" )
|eval alertTriggerTime=strftime(triggerTime, "%b %d %H:%M:%S" )
|eval alertAckTime=strftime(ackTime, "%b %d %H:%M:%S" )
|eval ResponseMeanTime=strftime(ResponseMean, "%H:%M:%S" )
|table rule_id,alertTriggerTime,alertAckTime,responseTime,ResponseMeanTime

I have tried to use your query (|eval responseTime= tostring(diffResponse, "duration")), but I am unable to get the average value of Response time.

Kindly assist on this.

[rjthibod]

I am a little confused. Does the following search work? All I did was replace strftime with tostring for diffResponse and ResponseMean. I am a little perplexed if you are getting values in your previous search but not this one.

| incident_review |chart values(_time) over rule_id by status_label |join rule_id [search notable | search NOT suppression owner!=unassigned| rename _time as triggerTime ] 
| rename "Resolved" as resolvedTime, "Closed" as closedTime "In Progress" as inProgressTime, "Pending" as pendingTime
| eval ackTime = case(isnotnull(inProgressTime), inProgressTime, isnotnull(pendingTime), pendingTime, isnotnull(resolvedTime), resolvedTime, isnotnull(closedTime), closedTime, 1=1 , "Unassigned")
| eval diffResponse= ackTime - triggerTime 
| eventstats avg(diffResponse) as ResponseMean
| eval responseTime=tostring(diffResponse, "duration") 
| eval alertTriggerTime=strftime(triggerTime, "%b %d %H:%M:%S" )
| eval alertAckTime=strftime(ackTime, "%b %d %H:%M:%S" )
| eval ResponseMeanTime=tostring(ResponseMean, "duration")
| table rule_id,alertTriggerTime,alertAckTime,responseTime,ResponseMeanTime

If the previous one does not work, what about this one? All it does is filter out places where the ackTime value is "unassigned" which would mess up your calculations in your original search if that is happening.

| incident_review |chart values(_time) over rule_id by status_label |join rule_id [search notable | search NOT suppression owner!=unassigned| rename _time as triggerTime ] 
| rename "Resolved" as resolvedTime, "Closed" as closedTime "In Progress" as inProgressTime, "Pending" as pendingTime
| eval ackTime = case(isnotnull(inProgressTime), inProgressTime, isnotnull(pendingTime), pendingTime, isnotnull(resolvedTime), resolvedTime, isnotnull(closedTime), closedTime, 1=1 , "Unassigned")
| search ackTime != "Unassigned"
| eval diffResponse= ackTime - triggerTime 
| eventstats avg(diffResponse) as ResponseMean
| eval responseTime=tostring(diffResponse, "duration") 
| eval alertTriggerTime=strftime(triggerTime, "%b %d %H:%M:%S" )
| eval alertAckTime=strftime(ackTime, "%b %d %H:%M:%S" )
| eval ResponseMeanTime=tostring(ResponseMean, "duration")
| table rule_id,alertTriggerTime,alertAckTime,responseTime,ResponseMeanTime

[ksing]

Thankyou, it works fine. The ResponseMeanTime shows in "HH:MM:SS.NNNNNNN" format, Is there any way to get the values in "HH:MM:SS" since I want to display the results as Dashboard for KPI.

[aaraneta_splunk]

@ksing - Did the answer provided by rjthibod help provide a working solution to your question? If yes, please don't forget to resolve this post by clicking "Accept". If no, please leave a comment with more feedback. Don't forget to up vote any comments that were helpful. Thanks!

[ksing]

thanks evenone for the great help. It was really helpful.

[somesoni2]

Update the ResponseMeanTime eval with this.

| eval ResponseMeanTime=tostring(round(ResponseMean), "duration")

[rjthibod]

Thanks @somesoni2 for answering this while I was away. You are extra helpful as always.