Splunk Search

How to use tstats to calculate avg response times?

dsenapaty
Explorer

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)

Labels (1)
0 Karma
1 Solution

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

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
If this reply helps you an upvote is appreciated.

dsenapaty
Explorer

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

0 Karma

dsenapaty
Explorer

@scelikok thanks a ton works perfectly.

0 Karma

scelikok
SplunkTrust
SplunkTrust

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. 

If this reply helps you an upvote is appreciated.
0 Karma

dsenapaty
Explorer

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

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...