Splunk Search

Join wont perform accurate count (Event table joined to summary table)

maryV
Engager

Hi there!

I am trying to join an event table (E1) with a summary table (S1). S1 is just a summary table containing stats derived from the event table (E1). I am trying to accomplish this cause I have to compare the stats to each event.

The query runs smoothly but won't give me the correct stats. Whenever I try to run them separately, the results are correct but when joined together as with the query below, it gives the wrong answer. For context, it gives bigger values for the stats.

Hope anybody can help! 😞 Thank you in advance! Please see query below. 

 

 

index=test sourcetype=aws* earliest=-0.5d@d

| search source=*RDS* metric_name=AbortedClients

| bin span=5m _time
| stats count as DataCount by _time, metric_name
| table _time, metric_name, DataCount

| join left=L right=R where L.metric_name = R.metric_name 

[
| search source=*RDS* metric_name=AbortedClients

| bin span=5m _time
| stats count as DataCount by _time, metric_name

| stats sum(DataCount) as TotalCount, avg(DataCount) as Average, stdev(DataCount) as StanDev, p25(DataCount) as P_25, p50(DataCount) as P_50, p75(DataCount) as P_75 by metric_name

| eval IQR = P_75 - P_50
| eval LB = P_25 - (IQR*1.5) 
| eval UB = P_75 + (IQR*1.5)
| eval OneThres = Average + (2 * StanDev)
| table metric_name, TotalCount, Average, StanDev, P_25, P_50, P_75, IQR, LB, UB, OneThres
]

 

 

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try using eventstats

index=test sourcetype=aws* earliest=-0.5d@d

| search source=*RDS* metric_name=AbortedClients

| bin span=5m _time
| stats count as DataCount by _time, metric_name
| table _time, metric_name, DataCount

| eventstats sum(DataCount) as TotalCount, avg(DataCount) as Average, stdev(DataCount) as StanDev, p25(DataCount) as P_25, p50(DataCount) as P_50, p75(DataCount) as P_75 by metric_name

| eval IQR = P_75 - P_50
| eval LB = P_25 - (IQR*1.5) 
| eval UB = P_75 + (IQR*1.5)
| eval OneThres = Average + (2 * StanDev)
| table metric_name, TotalCount, Average, StanDev, P_25, P_50, P_75, IQR, LB, UB, OneThres

 

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try using eventstats

index=test sourcetype=aws* earliest=-0.5d@d

| search source=*RDS* metric_name=AbortedClients

| bin span=5m _time
| stats count as DataCount by _time, metric_name
| table _time, metric_name, DataCount

| eventstats sum(DataCount) as TotalCount, avg(DataCount) as Average, stdev(DataCount) as StanDev, p25(DataCount) as P_25, p50(DataCount) as P_50, p75(DataCount) as P_75 by metric_name

| eval IQR = P_75 - P_50
| eval LB = P_25 - (IQR*1.5) 
| eval UB = P_75 + (IQR*1.5)
| eval OneThres = Average + (2 * StanDev)
| table metric_name, TotalCount, Average, StanDev, P_25, P_50, P_75, IQR, LB, UB, OneThres

 

0 Karma

maryV
Engager

Wow I was overthinking my query! Thank you so much for this!!! 

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...