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!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...