We are trying to get TPS for 3 diff hosts and ,need to be able to see the peak transactions for a given period. initially i did test with one host using below query for 15 mins , which is fine . But I need to check the peak transactions per second for 24 hours , which yields 9 lac records and only 1000 events are displayed in TPS Line graphe
and rest are truncated .
Help needed to finetune a query which can achieve the peak transactions for a given period
basesearch | timechart span=1s count as TPS .
basesearch | | timechart span=1s count as TPS |eventstats max(TPS) as PeakTPS
basesearch | bucket span=1s _time | stats count(TPS) by _time host | timechart max(TPS) as Peak -- no results - by adding host
I did search all the answers related to TPS , but couldn't help in achieving the my req.
Does you base search only rely on metadata / indexed fields (e.g., index, source, sourcetype, and host)? If so, you should get much better performance using
| tstats count where index=<insert index> by host _time span=1s
Extending @rjthibod's solution, here is what you can use to find peak tps value and corresponding time.
| tstats count as tps WHERE index= xyz host=yxs sourcetype=jhj by _time span=1s | eventstats max(tps) as peakTPS | where tps=peakTPS
OR (get top 5 peaks)
| tstats count as tps WHERE index= xyz host=yxs sourcetype=jhj by _time span=1s | sort 5 -tps
Thanks Soni for query of peak tps value and corresponding time and top 5 tps .
I'm looking for results of Max(TPS) in each hour of 24 hours time range where span=1s
and would like to schedule it to have results each individual hour against the 24 hr time range .
For First part you can use like this (Max(TPS) in each hour of 24 hours )
| tstats count as tps WHERE index= xyz host=yxs sourcetype=jhj by _time span=1s | timechart span=1h max(tps) as maxTPS
I'm not sure I understood the second part. Do you want to add a field say Result which will compare Hourly maxTPS with overall maxTPS for last 24 hrs? If so, try like this
| tstats count as tps WHERE index= xyz host=yxs sourcetype=jhj by _time span=1s | timechart span=1h max(tps) as maxTPS | eventstats max(maxTPS) as peakTPSDay | eval Result=case(maxTPS<peakTPSDay, "Over",maxTPS>peakTPSDay, "Under",1=1,"Same")
you can replace max(maxTPS) with avg as well if that interests you.
Thanks Soni .
yes I would like to have Max TPS for each hour in 24 hours time range .Initially , I was trying to achieve span=1s and have a "Line graphe to show " TPS for 24 hours . Since the search yields 10 lac plus events and Line graphe can only display 1000 events and truncate the rest .
I did prefer to have MaxTPS for each hour in 24 hours time range .
Thanks again for great help and You are a Pro!
yes , my basesearch
index= xyz host=yxs sourcetype=jhj | tstats count where index= by host _time span=1s
which gives an error : " Error in 'tstats' command: This command must be the first command of a search."
That is not how to correctly use
The base search would now become this
| tstats count where index=xyz host=yxs sourcetype=jhj by _time span=1s
If you want to chart that you would need to use
| tstats count where index=xyz host=yxs sourcetype=jhj by _time span=1s prestats=t | timechart span=1s fixedrange=f count as count
It's not absolutely necessary to use prestats in order to chart. Prestats gives you some underlying information that allows splunk to re-compute things like averages. If you just want to know and aggregate the number of transactions over time, you don't need that data.
You DO have to make sure not to confuse splunk between the "count" output field of the tstats command and the "count" input field of the timechart command.
| tstats count as trancount where index=xyz host=yxs sourcetype=jhj by _time span=1s | timechart span=1s fixedrange=f sum(trancount) as count