Aggregating pre-aggregated metrics in Azure Monitor

We at Tresorit use Microsoft’s Azure Monitor (previously called Application Insights) for logging and metrics collecting purposes, for example to measure the performance of our application.

To increase the performance and reduce the network usage of the measurements themselves, metrics can be pre-aggregated on the client side, this is automatically done when using the official Microsoft.ApplicationInsights .NET package. This client side aggregation means that the measured sample values are not actually sent, but only their count, sum, minimum, maximum and standard deviation.

The problem we’d like to solve is to be able to further aggregate these pre-aggregated metrics. More precisely, we’d like to compute the overall count, sum, minimum, maximum and standard deviation using Log Analytics. Calculating these values is trivial using the summarize operator except for the standard deviation.

Short version

Suppose your metrics are called “TestMetrics” and you‘d like to aggregate all the values. You can use this query in Log Analytics:

Long version

Here is a longer, but more easily understandable version of the aggregation query, the short version above calculates the same in a single step.

The basic idea was to realize that we can calculate the “second raw moment” (M₂) of each pre-aggregated sample, doing the aggregation using that and later convert it back to standard deviation. The n-th raw moment of a sample is defined as the expected value (average) of the n-th powers of the sample values. The first raw moment of a sample is its mean.

Step-by-step:

1. Calculate mean, variance and M₂ and finally the sum of squared values for each pre-aggregated sample.
2. Aggregate the sum of squared values simply by using sum(). Note that in theory we could have used a weighted average to aggregate M₂ values directly, but the built-in avg() function does not support weights.
3. Calculate M₂, mean, variance and finally the standard deviation of the aggregated result.

The relation between the standard deviation, variance, mean and M₂ can be seen from the following equation where E(Y) represents the expected value of the random variable Y. Using this notation E(X) is the mean and E(X²) is M₂.

Variance = StdDev² = E((X - E(X))²) = E(X² + E(X)² - 2*X*E(X)) = E(X²) + E(X)² - 2*E(X)*E(X) = E(X²) - E(X)² = M₂ - Mean²

This equation is used both when calculating M₂ from the standard deviation and at the end when calculating the standard deviation from M₂.

Demonstration

Let’s consider the following C# code which generates 100 random values between 0.0 and 1.0, sends them in a randomly aggregated way (“TestMetrics”) and also sends them together in a single metric (“TestMetricsTogether”) which makes it easy to check if our aggregation logic works.

The data uploaded was (as can be seen in Log Analytics):

customMetrics | where name == “TestMetrics” or name == “TestMetricsTogether”

Using the example data, running any of the above queries produce the correct valueStdDev = 0,307 result, the same that was uploaded manually for “TestMetricsTogether”.