How to Optimize Splunk Query to Avoid Join and Retain All Metrics with xyseries?

ravimishrabglr — Mon, 21 Oct 2024 21:51:35 GMT

Hi everyone,

I'm working on a Splunk query to analyze API request metrics, and I want to avoid using a join as it is making my query slow. The main challenge is that I need to aggregate multiple metrics (like min, max, avg, and percentiles) and pivot HTTP status codes (S) into columns, but the current approach withxyseries is dropping additional values: Min, Max, Avg, P95, P98, P99

The reason why using xyseries - it generates columns dynamically so that my result will contain only available statuses from many available and it count accordingly .

Here’s the original working query with join:

I attempted to optimize it by combining the metrics calculation into a single stats command and usingeventstats or streamstats to calculate the additional statistics without dropping the required fields. Also providing additional metrics with xyseries as below but did not help.

PS: Tried with chatGPT did not help. so seeking help from real experts 🙂

| stats count as TotalReq, min(D) as Min, max(D) as Max, avg(D) as Avg, perc95(D) as P95, perc98(D) as P98, perc99(D) as P99 by ApiName, S | xyseries ApiName S, TotalReq, Min, Max, Avg, P95, P98, P99

Re: How to Optimize Splunk Query to Avoid Join and Retain All Metrics with xyseries?

ITWhisperer — Mon, 21 Oct 2024 15:40:41 GMT

Glad you saw sense and ditched chatGPT!

Try something like this

index=sample_index sourcetype=kube:container:sample_container | fields U, S, D | where isnotnull(U) and isnotnull(S) and isnotnull(D) | rex field=U "(?P<ApiName>[^/]+)(?=\/[0-9a-fA-F\-]+$|$)" | eventstats min(D) as Min, max(D) as Max, avg(D) as Avg, perc95(D) as P95, perc98(D) as P98, perc99(D) as P99 by ApiName | stats count as TotalReq, by ApiName, Min, Max, Avg, P95, P98, P99, S | eval {S}=TotalReq | stats values(1*) as 1* values(2*) as 2* values(3*) as 3* values(4*) as 4* values(5*) as 5* sum(TotalReq) as TotalReq by ApiName, Min, Max, Avg, P95, P98, P99 | addtotals labelfield=ApiName col=t label="ColumnTotals" 1* 2* 3* 4* 5* TotalReq | addinfo | eval Availability% = round(100 - ('500'*100/TotalReq),8) | fillnull value=100 Availability% | eval range = info_max_time - info_min_time | eval AvgTPS=round(TotalReq/range,5) | eval Avg=floor(Avg) | eval P95=floor(P95) | eval P98=floor(P98) | eval P99=floor(P99) | sort TotalReq | table ApiName, 1*, 2*, 3*, 4*, 5*, Min, Max, Avg, P95, P98, P99, AvgTPS, Availability%, TotalReq

topic Re: How to Optimize Splunk Query to Avoid Join and Retain All Metrics with xyseries? in Knowledge Management

How to Optimize Splunk Query to Avoid Join and Retain All Metrics with xyseries?

Re: How to Optimize Splunk Query to Avoid Join and Retain All Metrics with xyseries?