How to use results of one data set to identify outliers of another data set?

[parwindertaank]

Hi,

I have the average and standard deviation of a particular data set and I want to build a confidence interval from these values and test values of another data set to see if they fall outside of the bounds I created.

index="prototype" sourcetype ="access_combined" clientip=* 
 | iplocation clientip 
 | convert timeformat="%Y-%m-%d" ctime(_time) AS date 
 | stats count by date, Country 
 | eventstats avg(count) as avg_count stdev(count) as stdev_count BY Country

And another search's query as just the count value

index="test3" sourcetype ="access_combined" clientip=* 
 | iplocation clientip 
 | convert timeformat="%Y-%m-%d" ctime(_time) AS date 
 | stats count by date, Country

I want to use

| where count>(avg_count+(2*stdev_count))
Where the count above is from "test3" index and avg_count and stdev_count is from "prototype" index.

And to put it all together in one search

Thanks in advance.

[somesoni2 · Accepted solution]

Give this a try

(index="prototype" OR  index="test3") sourcetype ="access_combined" clientip=* 
| iplocation clientip 
| convert timeformat="%Y-%m-%d" ctime(_time) AS date 
| eval baseCount=if(index="prototype",1,0)
| eval Count=if(index="test3",1,0)
| stats sum(baseCount) as Base sum(Count) as count by date, Country 
| eventstats avg(Base) as avg_count stdev(Base) as stdev_count BY Country | fields - Base
| where count>(avg_count+(2*stdev_count))

[parwindertaank]

This is exactly the result I was looking for!
The if command is something I just learned as well, thanks!