TPS with events that happened between <1sec, between 3 to 5secs etc

[rakeshreddy1230]

Hello, 

I have an ask to calculate TPS (Avg and Peak) for API calls that took

1) <1sec to respond,

2) calls that took between  3-5 secs

3) calls that took >5secs

Based on Multiple different API calls

Something like this

Route	<1s Avg TPS	<1s Max TPS	3-5sec Avg TPS	3-5sec Max TPS	>5sec Avg TPS	>5sec max TPS

I am able to get them separately in multiple splunk queries like below, but i need them with the breakdown of event response time as above. 

Below are my queries

1) index=XXX service_name=YYY request_host=ZZZ | rex field=_raw "AAA" | rex field=request_route "^(?<route>.*)\?" | rex field=_id "^(?<route>.*)\?" | eval pTime = total_time | eval TimeFrames = case(pTime<=1000, "0-1", pTime>1000 AND pTime<=3000, "1-3", pTime>3000 AND pTime<=5000, "3-5", pTime>5000 AND pTime<=8000, "5-8", pTime>8000, ">8") | stats count as CallVolume by route, TimeFrames | eventstats sum(CallVolume) as Total by route | eval Percentage=(CallVolume/Total)*100 | sort by route, -CallVolume | fields route,CallVolume,TimeFrames,Percentage | chart values(CallVolume) over route by TimeFrames | sort -TimeFrames

2) TPS:

index=XXX service_name=YYY request_host=ZZZ | rex field=_raw "AAA" | rex field=request_route "^(?<route>.*)\?" | eval resptime = total_time | bucket _time span=1s | stats count as TPS by _time,route | stats max(TPS) as PeakTPS, avg(TPS) as AvgTPS by route | fields route, PeakTPS, AvgTPS | sort PeakTPS desc

Can you please help ? 

[to4kawa]

Don't just write the query. If there are no results, no one but you can understand them.

[rakeshreddy1230]

Result_Query1Result_Query2

[rakeshreddy1230]

@to4kawa as you see, the result from query 1 provides the timeframe call volume, where as the second query provides the TPS. Now, what i am trying to get is the combination of both in a way. To get the TPS Avg and Peak with the time frame breakdown (<1s, 3-5s, 5-8s,>8s). 

Thanks

[to4kawa]

query1

| join route [search query2]

[rakeshreddy1230]

Thank you. But that is not what i am looking for. So, you basically are combining the results of both queries. 

What i want is to breakdown the TPS into those time frame. So, if there are 1500 calls that took <1 second response time, i want to know the TPS for those calls. If 1000 calls took 3 to 5 seconds for responding, i need to find the TPS for that. 

Route	Avg TPS for <1s	Max TPS for <1s	Avg TPS for 3-5seconds	Max TPS for 3-5seconds	Avg TPS for 5-8seconds	Max TPS for 5-8 Seconds	...

Thanks in advance.