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! :disappointed_face: 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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...