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

[ravimishrabglr]

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:

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\-]+$|$)"
| stats count as TotalReq, by ApiName, S
| xyseries ApiName S, TotalReq
| addtotals labelfield=ApiName col=t label="ColumnTotals" fieldname="TotalReq"
| join type=left ApiName
[ search 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\-]+$|$)"
| stats 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]
| addinfo
| eval Availability% = round(100 - ('500'*100/TotalReq), ‌😎‌
| 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

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

[ITWhisperer · Accepted solution]

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