Conditional standard deviations: How to create an alert that excludes specific outlier rows of one KPI from a StDev calculation that also must be included in a StDev where it’s not an outlier for other KPIs?

[weidertc]

I need to create an alert that's more intelligent and based on a baseline. I have a search that produces the following dataset in run anywhere spl:

|makeresults 1 | eval service="placeOrder", week=1, Volume=100, VolumeMed=100, VolumeLowerBound=28.75, VolumeIQR=47.5, VolumeUpperBound=175.25, VolumeOutlier=0, SuccessRate=80, FailureRate=20, RespTimeMed=500
| append [|makeresults 1 | eval service="placeOrder", week=2, Volume=95, VolumeMed=100, VolumeLowerBound=28.75, VolumeIQR=47.5, VolumeUpperBound=175.25, VolumeOutlier=0, SuccessRate=10, FailureRate=90, RespTimeMed=11400]
| append [|makeresults 1 | eval service="placeOrder", week=3, Volume=105, VolumeMed=100, VolumeLowerBound=28.75, VolumeIQR=47.5, VolumeUpperBound=175.25, VolumeOutlier=0, SuccessRate=85, FailureRate=15, RespTimeMed=450]
| append [|makeresults 1 | eval service="placeOrder", week=4, Volume=100, VolumeMed=100, VolumeLowerBound=28.75, VolumeIQR=47.5, VolumeUpperBound=175.25, VolumeOutlier=0, SuccessRate=75, FailureRate=25, RespTimeMed=550]
| append [|makeresults 1 | eval service="placeOrder", week=5, Volume=15, VolumeMed=100, VolumeLowerBound=28.75, VolumeIQR=47.5, VolumeUpperBound=175.25, VolumeOutlier=1, SuccessRate=75, FailureRate=25, RespTimeMed=450]
| fields service, week, Volume, VolumeMed, VolumeLowerBound, VolumeIQR, VolumeUpperBound, VolumeOutlier, SuccessRate, FailureRate, RespTimeMed

There are 5 total KPIs: Volume, SuccessRate, WarningRate, FailureRate, ResponseTime. I want to remove outliers prior to calculating the standard deviation but the problem is a single row may only contain an outlier in one of these KPIs, so the row must stay. In this case, each KPI outlier is on a different row, so i need the StDev of Volume to exclude week 5, and I need the StDev of SuccessRate to exclude week 2, but week 2 must still be included for the Volume StDev because it is not a Volume outlier, only a SuccessRate outlier.

I tried adding this to the end of the search:

| stats stdev(Volume), stdev(case(VolumeOutlier=0, Volume)) as VolumeStdDev by service

But, this is the result:

service                stdev(Volume)                VolumeStdDev
placeOrder             38.1772         

The column where I try to limit what rows it uses is blank. VolumeStdDev should be 4.0824.

How can I achieve excluding certain outlier rows of one KPI from a StDev calculation but whose row must remain to be included in StDev where it is not an outlier for those other KPIs?

[to4kawa · Accepted solution]

...
| stats stdev(Volume) as VolumeStd, stdev(eval(case(VolumeOutlier=0, Volume))) as VolumeStdDev by service

add eval

[weidertc]

ah, I forgot the eval. Damn. The hour I wasted on this. Thanks! This worked.

[to4kawa]

It's nice to be able to ask a community like this.