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 and "Accept as Solution" 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 and "Accept as Solution" 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 and "Accept as Solution" 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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...