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

[maryV]

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
]

[ITWhisperer · Accepted solution]

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

[maryV]

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