topic Re: Need help with Spunk query calculating TPS variance in Dashboards & Visualizations

How to write Splunk search to calculate TPS variance?

shashankk — Mon, 26 Jun 2023 15:15:57 GMT

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

Re: Need help with Spunk query calculating TPS variance

shashankk — Thu, 22 Jun 2023 19:06:44 GMT

Current Output:

Day	MaxTPS
2023-05-23	1.40
2023-05-22	1.10
and so on
2023-06-22	1.34

Re: Need help with Spunk query calculating TPS variance

shashankk — Fri, 23 Jun 2023 11:56:31 GMT

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%	--

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Thu, 22 Jun 2023 20:34:41 GMT

| 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

Re: Need help with Spunk query calculating TPS variance

shashankk — Fri, 23 Jun 2023 04:21:11 GMT

@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

Re: Need help with Spunk query calculating TPS variance

shashankk — Tue, 27 Jun 2023 07:06:42 GMT

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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Fri, 23 Jun 2023 15:43:23 GMT

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

Re: Need help with Spunk query calculating TPS variance

shashankk — Sat, 24 Jun 2023 12:50:31 GMT

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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Sat, 24 Jun 2023 23:04:54 GMT

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?)?

Re: Need help with Spunk query calculating TPS variance

shashankk — Sun, 25 Jun 2023 03:21:24 GMT

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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Sun, 25 Jun 2023 05:08:59 GMT

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?

Re: Need help with Spunk query calculating TPS variance

shashankk — Sun, 25 Jun 2023 05:45:21 GMT

@ITWhisperer

Yes, the expected output would have multiple rows with different TestMQ names. And the requirement is to show MaxTPS variance for each TestMQ names.

I agree on my mistake - I could have been more specific while sharing the initial requirement.

Requesting you to suggest what different approach can be tried here?

Thank you for your understanding. 🙂

Best Regards!

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Sun, 25 Jun 2023 06:13:16 GMT

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

Re: Need help with Spunk query calculating TPS variance

shashankk — Mon, 26 Jun 2023 13:20:11 GMT

@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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Mon, 26 Jun 2023 16:34:58 GMT

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

Re: Need help with Spunk query calculating TPS variance

shashankk — Tue, 27 Jun 2023 04:13:59 GMT

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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Tue, 27 Jun 2023 05:53:19 GMT

Try this

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

After

| autoregress MaxTPS p=7

add

| where isnotnull(MaxTPS_p7)

Re: Need help with Spunk query calculating TPS variance

shashankk — Tue, 27 Jun 2023 07:43:31 GMT

@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

Re: Need help with Spunk query calculating TPS variance

ITWhisperer — Tue, 27 Jun 2023 08:01:40 GMT

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.

Re: Need help with Spunk query calculating TPS variance

shashankk — Tue, 27 Jun 2023 09:17:06 GMT

@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>.*)"