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!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...