How to use tstats to calculate avg response times?

[dsenapaty]

Hello All,

I need help trying to generate the average response times for the below data using tstats command. Need help with the splunk query.  I am dealing with a large data and also building a visual dashboard to my management. So trying to use tstats as searches are faster. Stuck with unable to find avg response time using the value of Total_TT in my tstat command. When i execute the below tstat it is saying as it returned some number of events but the value is blank. Can someone help me with the query.

Sample Data:

2022-09-11 22:00:59,998 INFO -(Success:true)-(Validation:true)-(GUID:68D74EBE-CE3B-7508-6028-CBE1DFA90F8A)-(REQ_RCVD:2022-09-11T22:00:59.051)-(RES_SENT:2022-09-11T22:00:59.989)-(SIZE:2 KB)-(RespSent_TT:0ms)-(Actual_TT:938ms)-(DB_TT:9ms)-(Total_TT:947ms)-(AppServer_TT:937ms)

SPL Query:

| tstats values(PREFIX(total_tt:)) as AVG-RT where index=test_data sourcetype="tomcat:runtime:log" TERM(guid)

[scelikok · Accepted solution]

Hi @dsenapaty,

I missed an important detail. Since we are grouping by the total_tt the same total_tt events are being grouped and that is why the average becomes wrong. Below should work correct.

| tstats count where index=test_data sourcetype="tomcat:runtime:log" TERM(guid) by PREFIX(total_tt:) 
| rename "total_tt:" as total_tt 
| eval total_tt=tonumber(replace(total_tt,"ms","")) * count 
| stats sum(total_tt) as avgrt sum(count) as count 
| eval AVG-RT=round(avgrt/count,0) 
| fields AVG-RT

[dsenapaty]

@scelikok anyway to generate p99,p95,mean median values with this datasets ? Please help

[dsenapaty]

@scelikok thanks a ton works perfectly.

[scelikok]

Hi @dsenapaty,

I assume you are on Splunk version 8.x or higher since PREFIX is not supported on previous versions. The below search should work.

| tstats count where index=test_data sourcetype="tomcat:runtime:log" TERM(guid) by PREFIX(total_tt:) 
| rename "total_tt:" as total_tt 
| eval total_tt=replace(total_tt,"ms","") 
| stats avg(total_tt) as AVG-RT

Please keep in mind that PREFIX does not work on hot buckets. Your latest data may not be included in the average calculation until the bucket rolls to warm. 

[dsenapaty]

@scelikok Thanks i am now able to view the results but average calculations are not correct. 

When i run the below tstat spl and normal spl without tstat i am getting different average results. But events that these two commands are pulling seems to be same.