How to write Splunk search to calculate TPS variance?

[shashankk]

The below query is giving the results for 30 days MaxTPS data. (Between the time range of 2:00 to 4:00)

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

Now I want to calculate the "MaxTPS Variance" for complete 30 days. Calculate the percentage MaxTPS variance between "Today's value to last week's value" (and so on) and show the MaxTPS variance percentage. (Example: Monday to last week Monday; Sunday to last week Sunday and so on)

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

@ITWhisperer @bowesmana @xpac 

[shashankk]

Expected Output:

Date columns showing the Variance %

TestMQ	2023-06-22	2023-06-21	2023-06-20	2023-06-19	2023-06-18	2023-06-17	2023-06-16	2023-06-15	And so on - for 30 days
MQ.Test	60.00%	20.00%	10.00%	80.00%	5.00%	19.00%	10.00%	95.00%	--

[ITWhisperer]

| autoregress MaxTPS p=7
| eval Variance_TPS_Today=case(MaxTPS_p7 > MaxTPS, round(((MaxTPS_p7 - MaxTPS) / MaxTPS_p7) * 100,2),MaxTPS_p7 < MaxTPS, round(((MaxTPS - MaxTPS_p7) / MaxTPS) * 100,2),MaxTPS_p7 = MaxTPS, round(((MaxTPS_p7 - MaxTPS)) * 100,2))
| eval Date=strftime(_time,"%F")
| eval Name="Test"
| chart useother=f values(Variance_TPS_Today) as variance by Name Date

[shashankk]

@ITWhisperer Thanks for your kind response. I have written a below query which is providing the TPS average variance output for complete 30 days.

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

I want to modify this query for calculating 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

[ITWhisperer]

I am not really sure what it is you are asking - if you want the variance of the MaxTPS for each day compared to the MaxTPS 7 days prior, try this

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
| autoregress MaxTPS p=7
| eval Variance_TPS_Today=case(MaxTPS_p7 > MaxTPS, round(((MaxTPS_p7 - MaxTPS) / MaxTPS_p7) * 100,2),MaxTPS_p7 < MaxTPS, round(((MaxTPS - MaxTPS_p7) / MaxTPS) * 100,2),MaxTPS_p7 = MaxTPS, round(((MaxTPS_p7 - MaxTPS)) * 100,2))
| eval Date=strftime(_time,"%F")
| eval Name="Test"
| chart useother=f values(Variance_TPS_Today) as variance by Name Date

[shashankk]

@ITWhisperer Thank you for your kind response. I tried your suggested approach, but the query is not giving any output. Yes, you understood my requirement correctly - "I want the variance of the MaxTPS for each day compared to the MaxTPS 7 days prior"

Please suggest, how this can be modified further to achieve the below expected output results.

Query Used:

index=<search_strings>

| timechart span=120m count(eval(searchmatch("sent"))) as HotCountToday
by TestMQ

| eval TPS=round(HotCountToday/(120*60),2)
| eval Day=strftime(_time, "%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day
| autoregress MaxTPS p=7

| eval Variance_TPS_Today=case
(MaxTPS_p7 > MaxTPS, round(((MaxTPS_p7 - MaxTPS) / MaxTPS_p7) * 100,2),
MaxTPS_p7 < MaxTPS, round(((MaxTPS - MaxTPS_p7) / MaxTPS) * 100,2),
MaxTPS_p7 = MaxTPS, round(((MaxTPS_p7 - MaxTPS)) * 100,2))

| eval Date=strftime(_time,"%F")
| chart useother=f values(Variance_TPS_Today) as variance by TestMQ Date

Expected Output:

TestMQ	2023-06-24	2023-06-23	2023-06-22	2023-06-21	2023-06-20	2023-06-19	2023-06-18	2023-06-17	and so on..
MQ.NAME	19.79	10.17	5.18	61.13	79.13	77.51	31.23	83.12	...

[ITWhisperer]

Where does Test.MQ come from? Is this a new requirement that you haven't mentioned before (have you been wasting my time by not clearly specifying what you are trying to do?)?

[shashankk]

@ITWhisperer 
Apologize for the confusion! Please allow me to clarify it again.
So instead of using | eval Name="TestMQ"  I wanted to use this as group by TestMQ
Only that part is changed,  because I was not sure about the query and expected output. 

Can you please help further suggest on this changes?

Again, apologize for any confusion! 

Query Used:

index=<search_strings>
| rex "(?<TestMQ>*)"

| timechart span=120m count(eval(searchmatch("sent"))) as HotCountToday
by TestMQ

| eval TPS=round(HotCountToday/(120*60),2)
| eval Day=strftime(_time, "%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day
| autoregress MaxTPS p=7

| eval Variance_TPS_Today=case
(MaxTPS_p7 > MaxTPS, round(((MaxTPS_p7 - MaxTPS) / MaxTPS_p7) * 100,2),
MaxTPS_p7 < MaxTPS, round(((MaxTPS - MaxTPS_p7) / MaxTPS) * 100,2),
MaxTPS_p7 = MaxTPS, round(((MaxTPS_p7 - MaxTPS)) * 100,2))

| eval Date=strftime(_time,"%F")
| chart useother=f values(Variance_TPS_Today) as variance by TestMQ Date

Expected Output:

TestMQ	2023-06-24	2023-06-23	2023-06-22	2023-06-21	2023-06-20	2023-06-19	2023-06-18	2023-06-17	and so on..
MQ.NAME	19.79	10.17	5.18	61.13	79.13	77.51	31.23	83.12	...

[ITWhisperer · Accepted solution]

index=<search_strings>
| rex "(?<TestMQ>*)"

| timechart span=120m aligntime=earliest count(eval(searchmatch("sent"))) as HotCountToday by TestMQ
| untable _time TestMQ HotCountToday
| eval TPS=round(HotCountToday/(120*60),2)
| eval Day=strftime(_time,"%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day TestMQ
| xyseries Day TestMQ MaxTPS
| streamstats list(*) as * window=8
| foreach *
    [| eval <<FIELD>>=if("<<FIELD>>"="Day",mvindex(Day,-1),if(mvcount('<<FIELD>>')<8,null(),case(mvindex('<<FIELD>>',0)>mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',0))-tonumber(mvindex('<<FIELD>>',7)))/tonumber(mvindex('<<FIELD>>',0)),2),mvindex('<<FIELD>>',0)<mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',7))-tonumber(mvindex('<<FIELD>>',0)))/tonumber(mvindex('<<FIELD>>',7)),2),1==1,round(0,2))))]
| transpose 0 column_name=Name header_field=Day

[shashankk]

@ITWhisperer 

I need further help on how do we pass Variable/Arguments to the "timechart span=$variable"

I am trying to pass values from "TotalMinutes" to the timechart span value.

Refer below highlighted details from the query. Kindly suggest, how do we achieve this?

| timechart span=TotalMinutes aligntime=earliest count(eval(searchmatch("sent"))) as HotCount by TestMQ
| where tonumber(strftime(_time, "%H")) >= $timefrom$ AND tonumber(strftime(_time, "%H")) <= $timeto$
| untable _time TestMQ HotCount
| eval TotalMinutes = ($timeto$ - $timefrom$) * 60
| eval TPS=round(HotCount /(TotalMinutes*60),2)
| eval Day=strftime(_time,"%Y-%m-%d")

[ITWhisperer]

Given that timeto and timefrom are tokens, set up another token when these tokens are set which equates to the value you want in totalminutes.

[shashankk]

@ITWhisperer  Thank you so much Sir, this works as expected!

May I ask a follow up question please? In case I want to get this result for a specific time range only (for all 30 days) and based on that MaxTPS calculation should work only for the specified time range.

I have tried with below 3 different options but it doesn't work, no change in the output results.

Can you please suggest if anything missed here?

index=<search_strings> earliest=-30d@d date_hour>=2 AND date_hour<4
| rex "(?<TestMQ>*)"

| timechart span=120m aligntime=earliest count(eval(searchmatch("sent"))) as HotCountToday by TestMQ
| untable _time TestMQ HotCountToday
| eval TPS=round(HotCountToday/(120*60),2)
| eval Day=strftime(_time,"%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day TestMQ
| xyseries Day TestMQ MaxTPS
| streamstats list(*) as * window=8
| foreach *
[| eval <<FIELD>>=if("<<FIELD>>"="Day",mvindex(Day,-1),if(mvcount('<<FIELD>>')<8,null(),case(mvindex('<<FIELD>>',0)>mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',0))-tonumber(mvindex('<<FIELD>>',7)))/tonumber(mvindex('<<FIELD>>',0)),2),mvindex('<<FIELD>>',0)<mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',7))-tonumber(mvindex('<<FIELD>>',0)))/tonumber(mvindex('<<FIELD>>',7)),2),1==1,round(0,2))))]
| transpose 0 column_name=Name header_field=Day

Also tried with below approach, none of them working for the time range - I am getting the same old result.

1. index=<search_strings> earliest=-30d@d date_hour>=14 AND date_hour<=16

2. | search strftime(_time, "%H:%M:%S") >= "14:00:00" AND strftime(_time, "%H:%M:%S") <= "16:00:00"

3. | where date_hour >= 14 AND date_hour <= 16

[ITWhisperer]

Timechart will fill in the missing time periods so if you want to restrict the stats you should apply the restriction / filter after the timechart command - you could try something like this

| where strftime(_time, "%H") >= 14 AND strftime(_time, "%H") < 16

[shashankk]

@ITWhisperer 

Getting an error with this command, please suggest.
Point 1:

| where strftime(_time, "%H") >= 14 AND strftime(_time, "%H") < 16

Error in 'where' command: Type checking failed. The '>=' operator received different types.

Point 2 
Also, one more point to add - as I am having a restriction of maximum 30 days of data in Splunk. So my query is giving me final output of 23 days only, the other remaining last 7 days coming as NULL values. How do I exclude (not show) these NULL values columns and show the MaxTPS variance of only the 23 days.

Output Now: 
I do not want to show this NULL columns.

Name	2023-05-28	2023-05-27	..so on  (last 7 days)	2023-06-03	2023-06-04	2023-06-05	.. so on	2023-06-26	2023-06-27
MQ.Name	NULL	NULL	NULL	NULL	10.15	41.23	..	76.12	31.67

Expected Output:

This final output should display only the 23 days data (due to max limitation of 30 days data in Splunk) - and MaxTPS variance calculation is comparing with last 7 days 

Name	2023-06-03	2023-06-04	2023-06-05	2023-06-04	2023-06-03	so on ..	2023-06-25	2023-06-26	2023-06-27
MQ.Name	10.15	41.23	14.25	76.12	31.67	..	12.12	76.14	52.15

Kindly help.

[ITWhisperer]

Try this

| where tonumber(strftime(_time, "%H")) >= 14 AND tonumber(strftime(_time, "%H")) < 16

After

| autoregress MaxTPS p=7

add

| where isnotnull(MaxTPS_p7)

[shashankk]

@ITWhisperer 

Thank you for kind response and support!

Point 1: [Date Range condition]
I tried the suggested options on small part of query and found that it is not doing the search on the Event time, rather checking the Splunk time. I think this filtering needs to done on "date_hour" rather than using "_time".


Scenarios Tested:

index=<search_strings> earliest=-1d@d
| rex "(?<TestMQ>*)"

| timechart span=120m aligntime=earliest count(eval(searchmatch("sent"))) as HotCount by TestMQ
| where tonumber(strftime(_time, "%H")) >= 14 AND tonumber(strftime(_time, "%H")) < 16

Getting below output:
191,596 events (6/26/23 12:00:00.000 AM to 6/27/23 9:04:03.432 AM)

_time	MQ.Name
2023-06-26 14:00 (This time is not referring to date_hour range)	15043

Expected output is on the "date_hour" range for 14:00:00 to 15:59:59

I tried the below condition as well on "date_hour" - but it not giving any output results.
| where date_hour >= 14 AND date_hour < 16



Point 2: [NULL values]
And on the query part of the NULL scenario - I think you referred to an old query suggested.
In the modified query, we are not using this "autoregress MaxTPS p=7" (refer below)

Kindly clarify where exactly these null changes to be added:

| where isnotnull(MaxTPS_p7)

index=<search_string> earliest=-30d@d
| rex "(?<TestMQ>.*)"

| timechart span=120m aligntime=earliest count(eval(searchmatch("sent"))) as HotCount by TestMQ
| untable _time TestMQ HotCount
| eval TPS=round(HotCount/(120*60),2)
| eval Day=strftime(_time,"%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day TestMQ
| xyseries Day TestMQ MaxTPS
| streamstats list(*) as * window=8

| foreach *
[| eval <<FIELD>>=if("<<FIELD>>"="Day",mvindex(Day,-1),if(mvcount('<<FIELD>>')<8,null(),
case(mvindex('<<FIELD>>',0)>mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',0))-tonumber(mvindex('<<FIELD>>',7)))/tonumber(mvindex('<<FIELD>>',0)),2),mvindex('<<FIELD>>',0)<mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',7))-tonumber(mvindex('<<FIELD>>',0)))/tonumber(mvindex('<<FIELD>>',7)),2),1==1,round(0,2))))]

| transpose 0 column_name=Name header_field=Day

[ITWhisperer]

---

Point 1: [Date Range condition]
I tried the suggested options on small part of query and found that it is not doing the search on the Event time, rather checking the Splunk time. I think this filtering needs to done on "date_hour" rather than using "_time".

Obviously, you know your data better than I, so, what do you mean by Event time and Splunk time?

Bear in mind that date_hour is based on _time (at least in my environment) and date_hour gets removed by the timechart command.

[shashankk]

@ITWhisperer 
Thank you! Let me revalidate again and come back on this time range changes suggested by you. 

Meanwhile, can you please help suggest on the other part for the NULL values? Below for your reference:

Point 2: [NULL values]
And on the query part of the NULL scenario - I think you referred to an old query suggested.
In the modified query, we are not using this "autoregress MaxTPS p=7" (refer below)

Kindly clarify where exactly these null changes to be added:

| where isnotnull(MaxTPS_p7)

I tried modifying it as below, but no change in output results.
| stats max(TPS) as MaxTPS by Day TestMQ
| where isnotnull(MaxTPS)

index=<search_string> earliest=-30d@d
| rex "(?<TestMQ>.*)"

| timechart span=120m aligntime=earliest count(eval(searchmatch("sent"))) as HotCount by TestMQ
| untable _time TestMQ HotCount
| eval TPS=round(HotCount/(120*60),2)
| eval Day=strftime(_time,"%Y-%m-%d")
| stats max(TPS) as MaxTPS by Day TestMQ
| xyseries Day TestMQ MaxTPS
| streamstats list(*) as * window=8

| foreach *
[| eval <<FIELD>>=if("<<FIELD>>"="Day",mvindex(Day,-1),if(mvcount('<<FIELD>>')<8,null(),
case(mvindex('<<FIELD>>',0)>mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',0))-tonumber(mvindex('<<FIELD>>',7)))/tonumber(mvindex('<<FIELD>>',0)),2),mvindex('<<FIELD>>',0)<mvindex('<<FIELD>>',7),round(100*(tonumber(mvindex('<<FIELD>>',7))-tonumber(mvindex('<<FIELD>>',0)))/tonumber(mvindex('<<FIELD>>',7)),2),1==1,round(0,2))))]

| transpose 0 column_name=Name header_field=Day

[ITWhisperer]

OK I already answered this but Answers seems to have lost my answer - try adding this

| stats values(*) as * by Name

[shashankk]

@ITWhisperer 
Thank you so much for your kind support!
You're the best 🙂

[ITWhisperer]

So, your expected output would have multiple rows?

Do you not think this is a fundamental part of your requirement, and should have been clarified from the outset?