How to modify Spunk search to calculate the MaxTPS variance?

[shashankk]

I am having a below query which is providing the TPS average variance output for complete 30 days.

Can you please help guide me with the logic on how to modify this query for MaxTPS variance?

Requirement is to calculate MaxTPS variance (instead of the below logic for Average TPS variance)

Modification to be added:

index=<search string> earliest=-30d@d date_hour>=$timefrom$ AND date_hour<$timeto$
| timechart span=$TotalMinutes $m count(eval(searchmatch("sent"))) as HotCountToday
| eval TPS=round(HotCountToday/($TotalMinutes $*60),2)
| eval TotalMinutes = ($timeto$ - $timefrom$) * 60
| eval Day=strftime(_time, "%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day

Original Query:

index=<search_strings> earliest=-30d@d date_hour>=$timefrom$ AND date_hour<$timeto$
| eval Date = strftime(_time, "%Y-%m-%d")
| stats count(eval(Date=strftime(now(), "%Y-%m-%d"))) as HotCountToday,
count(eval(Date=strftime(relative_time(now(), "-1d@d"), "%Y-%m-%d"))) as HotCountBefore1Day,
count(eval(Date=strftime(relative_time(now(), "-2d@d"), "%Y-%m-%d"))) as HotCountBefore2Day,
count(eval(Date=strftime(relative_time(now(), "-3d@d"), "%Y-%m-%d"))) as HotCountBefore3Day,
count(eval(Date=strftime(relative_time(now(), "-4d@d"), "%Y-%m-%d"))) as HotCountBefore4Day,
count(eval(Date=strftime(relative_time(now(), "-5d@d"), "%Y-%m-%d"))) as HotCountBefore5Day,
count(eval(Date=strftime(relative_time(now(), "-6d@d"), "%Y-%m-%d"))) as HotCountBefore6Day,
count(eval(Date=strftime(relative_time(now(), "-7d@d"), "%Y-%m-%d"))) as HotCountBefore7Day,
.
.
count(eval(Date=strftime(relative_time(now(), "-30d@d"), "%Y-%m-%d"))) as HotCountBefore30Day

by TestMQ

| eval Today = strftime(now(), "%Y-%m-%d")
| eval Before1Day = strftime(relative_time(now(), "-1d@d"), "%Y-%m-%d")
| eval Before2Day = strftime(relative_time(now(), "-2d@d"), "%Y-%m-%d")
| eval Before3Day = strftime(relative_time(now(), "-3d@d"), "%Y-%m-%d")
| eval Before4Day = strftime(relative_time(now(), "-4d@d"), "%Y-%m-%d")
| eval Before5Day = strftime(relative_time(now(), "-5d@d"), "%Y-%m-%d")
| eval Before6Day = strftime(relative_time(now(), "-6d@d"), "%Y-%m-%d")
| eval Before7Day = strftime(relative_time(now(), "-7d@d"), "%Y-%m-%d")
.
.
| eval Before23Day = strftime(relative_time(now(), "-23d@d"), "%Y-%m-%d")

| eval TotalMinutes = ($timeto$ - $timefrom$) * 60

| eval TPS_Today=round(HotCountToday/(TotalMinutes*60),3)
| eval TPS_Before1Day=round(HotCountBefore1Day/(TotalMinutes*60),3)
| eval TPS_Before2Day=round(HotCountBefore2Day/(TotalMinutes*60),3)
| eval TPS_Before3Day=round(HotCountBefore3Day/(TotalMinutes*60),3)
| eval TPS_Before4Day=round(HotCountBefore4Day/(TotalMinutes*60),3)
| eval TPS_Before5Day=round(HotCountBefore5Day/(TotalMinutes*60),3)
| eval TPS_Before6Day=round(HotCountBefore6Day/(TotalMinutes*60),3)
| eval TPS_Before7Day=round(HotCountBefore7Day/(TotalMinutes*60),3)
.
.
| eval TPS_Before30Day=round(HotCountBefore30Day/(TotalMinutes*60),3)

| eval Variance_TPS_Today = case(TPS_Before7Day > TPS_Today, round(((TPS_Before7Day - TPS_Today) / TPS_Before7Day) * 100,3),
TPS_Before7Day < TPS_Today, round(((TPS_Today - TPS_Before7Day) / TPS_Today) * 100,3),
TPS_Before7Day = TPS_Today, round(((TPS_Before7Day - TPS_Today)) * 100,3))

| eval Variance_TPS_Before1Day = case(TPS_Before8Day > TPS_Before1Day, round(((TPS_Before8Day - TPS_Before1Day) / TPS_Before8Day) * 100,3),
TPS_Before8Day < TPS_Before1Day, round(((TPS_Before1Day - TPS_Before8Day) / TPS_Before1Day) * 100,3),
TPS_Before8Day = TPS_Before1Day, round(((TPS_Before8Day - TPS_Before1Day)) * 100,3))

| eval Variance_TPS_Before2Day = case(TPS_Before9Day > TPS_Before2Day, round(((TPS_Before9Day - TPS_Before2Day) / TPS_Before9Day) * 100,3),
TPS_Before9Day < TPS_Before2Day, round(((TPS_Before2Day - TPS_Before9Day) / TPS_Before2Day) * 100,3),
TPS_Before9Day = TPS_Before2Day, round(((TPS_Before9Day - TPS_Before2Day)) * 100,3))
.
.
.
| eval Variance_TPS_Before23Day = case(TPS_Before30Day > TPS_Before23Day, round(((TPS_Before30Day - TPS_Before23Day) / TPS_Before30Day) * 100,3),
TPS_Before30Day < TPS_Before23Day, round(((TPS_Before23Day - TPS_Before30Day) / TPS_Before23Day) * 100,3),
TPS_Before30Day = TPS_Before23Day, round(((TPS_Before30Day - TPS_Before23Day)) * 100,3))

| eval {Today}=Variance_TPS_Today | fields - Today Variance_TPS_Today
| eval {Before1Day}=Variance_TPS_Before1Day | fields - Before1Day Variance_TPS_Before1Day
| eval {Before2Day}=Variance_TPS_Before2Day | fields - Before2Day Variance_TPS_Before2Day
| eval {Before3Day}=Variance_TPS_Before3Day | fields - Before3Day Variance_TPS_Before3Day
| eval {Before4Day}=Variance_TPS_Before4Day | fields - Before4Day Variance_TPS_Before4Day
| eval {Before5Day}=Variance_TPS_Before5Day | fields - Before5Day Variance_TPS_Before5Day
| eval {Before6Day}=Variance_TPS_Before6Day | fields - Before6Day Variance_TPS_Before6Day
| eval {Before7Day}=Variance_TPS_Before7Day | fields - Before7Day Variance_TPS_Before7Day
.
.
.
| eval {Before23Day}=Variance_TPS_Before23Day | fields - Before23Day Variance_TPS_Before23Day

| table TestMQ 2*

Query Output as below:

TestMQ	2023-06-23	2023-06-22	2023-06-21	2023-06-20	2023-06-19	2023-06-18	2023-06-17	2023-06-16	And so on - till 30 days
MQ.NAME	5.003	17.004	25.775	19.882	32.114	56.881	10.991	85.114	....

I am new to Splunk and still learning. Looking forward to hear from you. Kindly suggest how this can be achieved.

@ITWhisperer @bowesmana @xpac @MuS @yuanliu - looking forward to hear from you, please help assist.