Alerting

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
Communicator

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?

Labels (1)
0 Karma
1 Solution

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

add eval

View solution in original post

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

add eval

weidertc
Communicator

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

0 Karma

to4kawa
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...