Splunk Search
Highlighted

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

Explorer

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)

alt text

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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")]
Highlighted

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

Explorer

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!

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma
Highlighted

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

Legend

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")]
0 Karma
Highlighted

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

Explorer

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 ....

0 Karma
Highlighted

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

Legend

Try the updated version. I fixed the formatting.

0 Karma
Highlighted

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

Explorer

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

AvgResponseTime= 00:00:11.744500 MaxResponseTime= 00:00:16.711000

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

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 AverageResponseTime = 11.744500 MaxResponseTime =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!

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.