Vfrdtyky

So I'm trying to find machines that throw exceptionally many exceptions as compared to their group by environment and function. The intuition is that the load and task type should be fairly similar across the group, so if one machine throws a lot more exceptions, it's probably somehow in a bad state and should be serviced.

This works fairly well for large groups of machines, but for smaller groups there's a problem: If there's very few machines, and only one of them is throwing a lot of exceptions, it may not get detected. The reason is that because that datapoint is pars of the general stddev and mean calculation for the group, the mean and stddev are biased towards this outlier.

The solution would be to either somehow subtract that data point from the calculated stddev and mean for the entire group, or to calculate the stddev and mean for each machine/environment/function combination (excluding the machine in question from the stddev/mean calculation) instead of just by environment/function group.

Here's the current code that does it by environment/function. Is there an elegant solution to expand it to do the machine/environment/function?

// Find sick machines

let SickMachinesAt = (AtTime:datetime , TimeWindow:timespan = 1h, Sigmas:double = 3.0, MinimumExceptionsToTrigger:int = 10) {

    // These are the exceptions we are looking at (time window constrained)

    let Exceptions = exception

    | where EventInfo_Time between((AtTime - TimeWindow ) .. AtTime);

    // Calculate mean and stddev for each bin of environmentName + machineFunction

    let MeanAndStdDev = Exceptions

    | summarize count() by environmentName, machineFunction, machineName

    | summarize avg(count_), stdev(count_) by environmentName, machineFunction

    | order by environmentName, machineFunction;

    let MachinesWithMeanAndStdDev = Exceptions

    | summarize count() by environmentName, machineFunction, machineName

    | join kind=fullouter MeanAndStdDev on environmentName, machineFunction;

    let SickMachines = MachinesWithMeanAndStdDev |

    project machineName, 

        machineFunction, 

        environmentName, 

        totalExceptionCount = count_, 

        cutoff = avg_count_ + Sigmas * stdev_count_, 

        signalStrength = ((count_ - avg_count_) / stdev_count_) 

    | where totalExceptionCount > cutoff and totalExceptionCount > MinimumExceptionsToTrigger

    | order by signalStrength desc;

    SickMachines

}

One option to avoid missed detects due to strong outlier values is to use percentiles based detection. For this purpose you could use make-series followed by the built-in series_outliers function.

Another option is to remove outliers from the calculation and then join back on the data and requires multiple joins. Assuming your exceptions are in Exceptions which contain the dimesnions: environmentName, machineFunction, machineName, you could remove all machines with counts above the 98th percentile with the following pseudo-query:

let ExceptionsCounts = Exceptions 

    | summarize counts = count() by environmentName, machineFunction, machineName;

let ExceptionsCleansed = ExceptionsCounts

    | summarize p98 = percentile(counts, 98) by environmentName, machineFunction

    | join kind=inner (ExceptionsCounts) on environmentName, machineFunction

    | where counts < p98;

From there you could use ExceptionsCleansed to calculate the mean/stddev and continue with the detection on the original Exceptions with the calculated figures with exactly the same query you posted.