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?
...
| stats stdev(Volume) as VolumeStd, stdev(eval(case(VolumeOutlier=0, Volume))) as VolumeStdDev by service
add eval
...
| stats stdev(Volume) as VolumeStd, stdev(eval(case(VolumeOutlier=0, Volume))) as VolumeStdDev by service
add eval
ah, I forgot the eval. Damn. The hour I wasted on this. Thanks! This worked.
It's nice to be able to ask a community like this.