Why is my search not producing an average in the resulting stats table?

[dcascione]

Hello Splunk Ninjas

I'm trying to create a SPL query that displays the avg and max response time. When I run my search, only the max values are displayed. How do I edit my search to fix this?

 index=my_index sourcetype=my_sourcetype| stats avg(Response_Time), max(Response_Time) by service | table avg(Response_Time), max(Response_Time) | sort -max(Response_Time)

[sundareshr]

See if this gives you better results

**** UPDATED ****

     index=my_index sourcetype=my_sourcetype | eval Response_Time=strptime(Response_Time,"%H:%M:%S.%3N")  | eval base=strptime("00:00:00.000","%H:%M:%S.%3N") | eval Response_Time=Response_Time-base| stats avg(Response_Time) as Avg_Response_Time , max(Response_Time) as Max_Response_Time by service | table Avg_Response_Time  Max_Response_Time | sort -num(Max_Response_Time) | foreach * [eval <<FIELD>>=tostring('<<FIELD>>',"duration")]

[dcascione]

After updating the query and removing the last pipe, I do see the Service field, however the results format seems to be off..Results are returned in this format: 28.611000 . Raw data looks like this: 0:00:28.611.

Adding the pipe back in (foreach * [eval <>=strftime('<>',"%H:%M:%S.%3N")]) appends every returned avg and max value with 19 hours, however the format looks better .... 19:00:06.698 ....

[dcascione]

Lets see.... The update produces these results :

Avg_Response_Time= 00:00:11.744500 Max_Response_Time= 00:00:16.711000

The service field contains no results, even after modifying to include service in the table | service, Avg_response_Time, Max_Response_Time.

Removing the last | "each * [eval <>=tostring('<>',"duration")]" ( Not sure what this part of the query does - can you explain?) produces these results and shows data for the service field:

service =quote/save Average_Response_Time = 11.744500 Max_Response_Time =16.711000

I think we are very close! For the formatting, I'm wondering if we can filter to show two places after the decimal and up to 4 place markers before : 00:00.00 ?

Thanks!

[sundareshr]

Try the updated version. I fixed the formatting.

[somesoni2]

Based on the screenshot, the value of the field Response_Time field looks like in string format. You would need to convert the string formatted duration to numeric to be able to calculate average time.
Give this a try

index=my_index sourcetype=my_sourcetype | eval Response_Time=strptime(Response_Time,"%H:%M:%S.%N") | stats avg(Response_Time) as Avg_Response_Time , max(Response_Time) as Max_Response_Time by service | table Avg_Response_Time  Max_Response_Time | sort -num(Max_Response_Time) | foreach * [eval <<FIELD>>=strftime('<<FIELD>>',,"%H:%M:%S.%N")]

[dcascione]

This appears to be working with the exception of showing the service field( column contains no data!). I truncated the Time to show HMS (for some reasone I couldn't get .%N" to run without errors), removed the ,, after the <>' tag, to get the query working...

| eval Response_Time=strptime(Response_Time,"%H:%M:%S") | stats avg(Response_Time) as Avg_Response_Time , max(Response_Time) as Max_Response_Time by service | sort -num(Max_Response_Time) | foreach * [eval <>=strftime('<>',"%H:%M:%S")] | Table service, Avg_Response_Time, Max_Response

Any Suggestions? Thanks again for your help!

[somesoni2]

You added the table command with field service, hence it's showing the field and it's value is null as the foreach is using * and trying to format it. Give this a try

| eval Response_Time=strptime(Response_Time,"%H:%M:%S") | stats avg(Response_Time) as Avg_Response_Time , max(Response_Time) as Max_Response_Time by service | sort -num(Max_Response_Time) | foreach *_Time [eval <>=strftime('<>',"%H:%M:%S")] | Table service, Avg_Response_Time, Max_Response