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)
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")]
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 ....
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!
Try the updated version. I fixed the formatting.
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")]
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!
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