Tutorial: Detect and analyze anomalies using KQL machine learning capabilities in Azure Monitor

The Kusto Query Language (KQL) includes machine learning operators, functions and plugins for time series analysis, anomaly detection, forecasting, and root cause analysis. Use these KQL capabilities to perform advanced data analysis in Azure Monitor without the overhead of exporting data to external machine learning tools.

Azure Monitor Logs is the service that stores your log data. Log Analytics is the tool in the Azure portal for querying that data by using KQL. The KQL function reference articles linked in this tutorial are shared Kusto documentation.

In this tutorial, you learn how to:

Prerequisites

• An Azure account with an active subscription. Create an account for free.
• A workspace with log data.

Permissions required

You must have Microsoft.OperationalInsights/workspaces/query/*/read permissions to the Log Analytics workspaces you query, as provided by the Log Analytics Reader built-in role, for example.

Create a time series with make-series

Use the KQL make-series operator to create a time series.

Create a time series based on logs in the Usage table, which holds information about how much data each table in a workspace ingests every hour, including billable and non-billable data.

This query uses make-series to chart the total amount of billable data ingested by each table in the workspace every day, over the past 21 days:

Run this query in the demo environment

let starttime = 21d; // The start date of the time series, counting back from the current date
let endtime = 0d; // The end date of the time series, counting back from the current date
let timeframe = 1d; // How often to sample data
Usage // The table we're analyzing
| where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime))) // Time range for the query, beginning at 12:00 AM of the first day and ending at 12:00 AM of the last day in the time range
| where IsBillable == "true" // Include only billable data in the result set
| make-series ActualUsage=sum(Quantity) default = 0 on TimeGenerated from startofday(ago(starttime)) to startofday(ago(endtime)) step timeframe by DataType // Creates the time series, listed by data type
| render timechart // Renders results in a timechart

The resulting chart shows some anomalies, for example, in the AzureDiagnostics and SecurityEvent data types:

To list all anomalies in a time series, use the series_decompose_anomalies() function, described in Find anomalies in a time series with series_decompose_anomalies().

Find anomalies in a time series with series_decompose_anomalies()

The series_decompose_anomalies() function takes a series of values as input and extracts anomalies.

Give the result set of the make-series query in Create a time series as input to the series_decompose_anomalies() function:

Run this query in the demo environment

let starttime = 21d; // Start date for the time series, counting back from the current date
let endtime = 0d; // End date for the time series, counting back from the current date
let timeframe = 1d; // How often to sample data
Usage // The table we're analyzing
| where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime))) // Time range for the query, beginning at 12:00 AM of the first day and ending at 12:00 AM of the last day in the time range
| where IsBillable == "true" // Includes only billable data in the result set
| make-series ActualUsage=sum(Quantity) default = 0 on TimeGenerated from startofday(ago(starttime)) to startofday(ago(endtime)) step timeframe by DataType // Creates the time series, listed by data type
| extend(Anomalies, AnomalyScore, ExpectedUsage) = series_decompose_anomalies(ActualUsage) // Scores and extracts anomalies based on the output of make-series
| mv-expand ActualUsage to typeof(double), TimeGenerated to typeof(datetime), Anomalies to typeof(double),AnomalyScore to typeof(double), ExpectedUsage to typeof(long) // Expands the array created by series_decompose_anomalies()
| where Anomalies != 0  // Returns all positive and negative deviations from expected usage
| project TimeGenerated,ActualUsage,ExpectedUsage,AnomalyScore,Anomalies,DataType // Defines which columns to return
| sort by abs(AnomalyScore) desc // Sorts results by anomaly score in descending ordering

This query returns all usage anomalies for all tables in the last three weeks:

The query results show that the function:

• Calculates an expected daily usage for each table.
• Compares actual daily usage to expected usage.
• Assigns an anomaly score to each data point, indicating the extent of the deviation of actual usage from expected usage.
• Identifies positive (1) and negative (-1) anomalies in each table.

Tweak anomaly detection settings to refine results

It's good practice to review initial query results and make tweaks to the query, if necessary. Outliers in input data can affect the function's learning, and you might need to adjust the function's anomaly detection settings to get more accurate results.

Filter the results of the series_decompose_anomalies() query for anomalies in the AzureDiagnostics data type:

The dates and scores in the results change as the 21-day query window moves. Compare the current results with the chart from the make-series query in Create a time series and note which points the function identifies as anomalies:

The difference in results occurs because the series_decompose_anomalies() function scores anomalies relative to the expected usage value, which the function calculates based on the full range of values in the input series.

To evaluate recent points against a baseline learned from earlier points, exclude one or more points at the end of the series from the function's learning process.

The syntax of the series_decompose_anomalies() function is:

series_decompose_anomalies(Series [, Threshold, Seasonality, Trend, Test_points, AD_method, Seasonality_threshold])

The function takes these arguments:

To exclude the last data point from the learning process, set Test_points to 1. This setting always holds out the final point in the current series. It doesn't refer to a fixed calendar date:

Run this query in the demo environment

let starttime = 21d; // Start date for the time series, counting back from the current date
let endtime = 0d; // End date for the time series, counting back from the current date
let timeframe = 1d; // How often to sample data
Usage // The table we're analyzing
| where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime))) // Time range for the query, beginning at 12:00 AM of the first day and ending at 12:00 AM of the last day in the time range
| where IsBillable == "true" // Includes only billable data in the result set
| make-series ActualUsage=sum(Quantity) default = 0 on TimeGenerated from startofday(ago(starttime)) to startofday(ago(endtime)) step timeframe by DataType // Creates the time series, listed by data type
| extend(Anomalies, AnomalyScore, ExpectedUsage) = series_decompose_anomalies(ActualUsage, 1.5, -1, 'avg', 1) // Excludes the final series value from learning. Other input values are the function defaults
| mv-expand ActualUsage to typeof(double), TimeGenerated to typeof(datetime), Anomalies to typeof(double),AnomalyScore to typeof(double), ExpectedUsage to typeof(long) // Expands the array created by series_decompose_anomalies()
| where Anomalies != 0  // Returns all positive and negative deviations from expected usage
| project TimeGenerated,ActualUsage,ExpectedUsage,AnomalyScore,Anomalies,DataType // Defines which columns to return
| sort by abs(AnomalyScore) desc // Sorts results by anomaly score in descending ordering

Filter the results for the AzureDiagnostics data type:

Compare the modified results with the original results. Depending on the current data, holding out the final point might change its expected value, anomaly score, or classification. Increase Test_points only when you intend to evaluate or forecast that many points at the end of the series.

Analyze the root cause of anomalies with the diffpatterns() plugin

Comparing expected values to anomalous values helps you understand the cause of the differences between the two sets.

The KQL diffpatterns() plugin compares two data sets of the same structure and finds patterns that characterize differences between the two data sets.

The following query selects the strongest AzureDiagnostics usage anomaly in the current 21-day window. It then compares records from that date with records from the other dates. Open the Log Analytics demo environment, and run the query.

let starttime = 21d; // Start date for the time series, counting back from the current date
let endtime = 0d; // End date for the time series, counting back from the current date
let anomalyDate = toscalar(
	Usage
	| where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime)))
	| where IsBillable == "true" and DataType == "AzureDiagnostics"
	| make-series ActualUsage=sum(Quantity) default = 0 on TimeGenerated from startofday(ago(starttime)) to startofday(ago(endtime)) step 1d
	| extend (Anomalies, AnomalyScore, ExpectedUsage) = series_decompose_anomalies(ActualUsage)
	| mv-expand TimeGenerated to typeof(datetime), Anomalies to typeof(double), AnomalyScore to typeof(double)
	| where Anomalies != 0
	| top 1 by abs(AnomalyScore) desc
	| project TimeGenerated
);
AzureDiagnostics
| extend AnomalyDate = iff(startofday(TimeGenerated) == anomalyDate, "AnomalyDate", "OtherDates") // Splits the result set into the selected anomaly date and all other dates
| where TimeGenerated between (startofday(ago(starttime))..startofday(ago(endtime))) // Defines the time range for the query
| project AnomalyDate, Resource // Defines which columns to return
| evaluate diffpatterns(AnomalyDate, "OtherDates", "AnomalyDate") // Compares usage on the anomaly date with the regular usage pattern

The query identifies each entry in the table as occurring on AnomalyDate or OtherDates. The diffpatterns() plugin then splits these data sets (A is OtherDates, and B is AnomalyDate) and returns patterns that contribute to the differences between the sets. If the query doesn't detect an anomaly, increase the time range or adjust the anomaly threshold before running this analysis.

Review the returned patterns to find resources whose record count or percentage differs most between AnomalyDate and OtherDates. The values vary with the selected anomaly and the current contents of the demo workspace.

The PercentDiffAB column shows the absolute percentage point difference between A and B (|PercentA - PercentB|), which is the main measure of the difference between the two sets. By default, the diffpatterns() plugin returns differences of over 5% between the two data sets. Adjust the threshold argument to change this behavior:

For example, to return only differences of 20% or more between the two data sets, set | evaluate diffpatterns(AnomalyDate, "OtherDates", "AnomalyDate", "~", 0.20) in the query above. The query returns only patterns with an absolute percentage-point difference of 20% or more. The number of results depends on the selected anomaly and the current demo data. The following image shows an example result:

Next steps

Learn more about:

• Log queries in Azure Monitor.
• Tutorial: Learn common operators.
• Analyze logs in Azure Monitor with KQL