How to get the TPS of all events in a 24 hours by host .

[guru865]

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.

Thanks.

[rjthibod]

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, e.g.,

| tstats count where index=<insert index> by host _time span=1s

[somesoni2]

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

[guru865]

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 .

[somesoni2]

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.

[guru865]

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!

[rjthibod]

Thanks as always for backing me up with even more useful queries.

[guru865]

Thanks Rjthibod for quick turn around.

[guru865]

rjthibod ,

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

Thank you

[rjthibod]

That is not how to correctly use tstats

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 prestats

| tstats count where index=xyz host=yxs sourcetype=jhj by _time span=1s prestats=t | timechart span=1s fixedrange=f count as count

[DalJeanis]

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