How to calcuate the average response time in seconds for a field?

[rajeshmeea21]

I am trying to calculate the average response time in seconds for one of my fields.
Getting exception in result set.. Highlighted with value 1:16.000 and 1:46.675
While calculating the average (avg) response time, i am not able to get these converted to seconds.

Is there any workaround where i can calculate avg response time correctly? This field is a number.

Below image will give you clearer idea.

[woodcock]

If I understand you correctly, like this:

... | rex field=response_time "^(?<remainder>.*?)(?<seconds>[^:\s]+)$" 
| rex field=remainder "^(?<remainder>.*?)(?<minutes>[^:\s]+):$" 
| rex field=remainder "^(?<days>.*?)(?<hours>[^:\s]+):$" 
| rex field=days mode=sed "s/://" 
| fillnull value="0" days hours minutes seconds 
| eval days=if(len(days)=0, 0, days) | rename COMMENT AS "<-Bug fix for values like 2:3:4" 
| fields - remainder 
| eval response_time_seconds_only = seconds + 60 * (minutes + 60 * (hours + 24 * days))

[hunters_splunk]

Hi rajeshmeea21,

I don't know how you calculated the response time field, but your log sample doesn't look right with the two exceptions.
Assuming that you calculated your response duration by subtracting time of response and time of post, the duration should be converted into a time format:

duration=tostring(post_time - response_time,"duration")

Hope this helps. Thanks!
Hunter

[gokadroid · Accepted solution]

If the current response times are in field called response_time then can you please try to see if this works for you:

your query to return events
| rex field=response_time "^(?<myMinutes>[^\:]+)\:(?<mySeconds>.*)"
| eval myResponseTime=(myMinutes*60)+mySeconds
| eval response_time=coalesce(myResponseTime, response_time)
| stats avg(response_time) as AvgRespTimeInSec

[woodcock]

My solution is very similar but is extended for longer periods of time.

[rajeshmeea21]

Here is a sample log..

01/06 11:23:52.792 - 18.208 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 2336365 - 
01/06 11:23:26.549 - 1:16.000 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 987 - 
01/06 11:23:26.215 - 1:46.675 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 1650 - 
01/06 11:23:26.215 - 27.817 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 1650 -

I am capturing full value in response_time field.

[jagadeeshm]

Posting a sample event will help.

[aaraneta_splunk]

@rajeshmeea21 - It would be helpful if you were to provide your current search. The more information and context you can provide in your question, the better chance users in the Answers community can help provide you with a working solution.

[rajeshmeea21]

Sample logs:

01/06 11:23:52.792 - 18.208 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 2336365 - 
01/06 11:23:26.549 - 1:16.000 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 987 - 
01/06 11:23:26.215 - 1:46.675 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 1650 - 
01/06 11:23:26.215 - 27.817 - 10.10.20.100:25640 - 8080 - "POST / HTTP/1.1" - 200 OK - 1650 -

Search query:-

index=test | stats avg(response_time) by Module_Name