Demystifying Anomaly Detection in Microsoft Sentinel using KQL

In this article, we break down the math behind anomaly detection, explain it in simple terms, and walk through practical use cases using sample data such as user login attempts and firewall logs.

Understanding the Basics: Key Math Concepts 

• Trend Line / Line of Best Fit: A straight line that shows the overall direction of data. It is calculated using linear regression with the formula: y = mx + c. 
  
• Linear Regression: A method to calculate the best-fit line that minimizes the difference between actual values and the predicted line. 
  
  

• Standard Deviation (SD): Tells us how much the values deviate from the average. A small SD means values are tightly clustered; a large SD means more variability. 
  
  

• Z-Score: Measures how far a point is from the expected value, in terms of standard deviations. A Z-score > 3 usually indicates an anomaly. 
  
  

Simple Explanation of Anomaly Detection Imagine tracking your daily steps. If every day you walk around 5,000–6,000 steps, and suddenly one day you walk 12,000 — that’s an anomaly. Anomaly detection automates this spotting process using statistical thresholds. 

In KQL: series_decompose_anomalies()

series_decompose_anomalies(series, threshold, seasonality_hint, decomposition_kind, test_points) 

• threshold: Number of SDs to flag a point as anomaly (e.g., 1.5). 
• seasonality_hint: Set to -1 for auto detection. You could set it to specific values like 7 (weekly), 30 (monthly), etc. 
• test_points: Number of most recent points excluded from training. How many of the most recent points should be excluded from training and treated as test data (to check for anomalies). 
  
  

Use Case 1: User Login Activity Here’s a simplified dataset of login attempts: 

 
Most days, the number of logins is normal — between 10 and 14 logins per day. If you calculate the average (mean), it comes out to about 12. Also, the data does not jump around too wildly — the standard deviation (how much the numbers usually vary) is only about 1.3. 

But then suddenly, on: 

March 24, we saw 150 logins. 

April 3, we saw 180 logins. 

That’s more than usual — not just a little bump. It is more like a huge spike. 

To measure how unusual these values are, we use the Z-score — it tells us how many "steps" away these numbers are from the average. 

If something is 3 steps (3 standard deviations) away, it’s already suspicious 

But these values were more than 100 steps away — that's massively abnormal 

On a bell curve, almost all the normal values fall near the center. These spikes? They’d be way out in the flat, far end of the curve — the kind of values you'd almost never expect. 

Sample KQL to Explore Anomalies – Adjust the Values and Observe the Changes

let UserLoginActivity = datatable(TimeGenerated: datetime, UserId: string, LoginAttempts: int) 
    [ 
       datetime(2025-03-18), "UserA", 12, 
       datetime(2025-03-19), "UserA", 10, 
       datetime(2025-03-20), "UserA", 14, 
       datetime(2025-03-21), "UserA", 13, 
        datetime(2025-03-22), "UserA", 11, 
        datetime(2025-03-23), "UserA", 12, 
        datetime(2025-03-24), "UserA", 150, 
        datetime(2025-03-25), "UserA", 13, 
        datetime(2025-03-26), "UserA", 12, 
        datetime(2025-03-27), "UserA", 14, 
        datetime(2025-03-28), "UserA", 11, 
        datetime(2025-03-29), "UserA", 10, 
        datetime(2025-03-30), "UserA", 12, 
        datetime(2025-03-31), "UserA", 13, 
        datetime(2025-04-01), "UserA", 14, 
        datetime(2025-04-02), "UserA", 13, 
        datetime(2025-04-03), "UserA", 180, 
        datetime(2025-04-04), "UserA", 11, 
        datetime(2025-04-05), "UserA", 12, 
        datetime(2025-04-06), "UserA", 13, 
        datetime(2025-04-07), "UserA", 14 
    ]; 
    UserLoginActivity 
    | make-series LoginAttempts=sum(LoginAttempts) default=0 
    on TimeGenerated from datetime(2025-03-18) to datetime(2025-04-07) step 1d 
    | extend (Anomalies, AnomalyScore, Baseline) = series_decompose_anomalies(LoginAttempts, 1.5, -1, 'linefit', 1) 
    | mv-expand TimeGenerated, LoginAttempts, Anomalies, AnomalyScore, Baseline 
    | where Anomalies != 0 
    | project TimeGenerated, LoginAttempts, Baseline, AnomalyScore 

Use Case 2: Firewall Logs with BytesIn/BytesOut  

 
A typical BytesOut value is ~10,000. The values on Mar 24 and Apr 3 stand out as large outliers. 

Sample KQL to Explore Anomalies – Adjust the Values and Observe the Changes

let FirewallLogs = datatable(TimeGenerated: datetime, SourceIP: string, DestinationIP: string, BytesIn: long, BytesOut: long, Action: string) 

[ 
    datetime(2025-03-18), "10.0.0.5", "8.8.8.8", 12000, 10000, "Allowed", 
    datetime(2025-03-19), "10.0.0.5", "8.8.8.8", 12500, 9800, "Allowed", 
    datetime(2025-03-20), "10.0.0.5", "8.8.8.8", 13000, 10200, "Allowed", 
    datetime(2025-03-21), "10.0.0.5", "8.8.8.8", 12800, 10100, "Allowed", 
    datetime(2025-03-22), "10.0.0.5", "8.8.8.8", 12700, 9700, "Allowed", 
    datetime(2025-03-23), "10.0.0.5", "8.8.8.8", 12900, 9900, "Allowed", 
    datetime(2025-03-24), "10.0.0.5", "203.0.113.20", 11500, 145000, "Allowed", 
    datetime(2025-03-25), "10.0.0.5", "8.8.8.8", 12400, 10000, "Allowed", 
    datetime(2025-03-26), "10.0.0.5", "8.8.8.8", 12300, 10100, "Allowed", 
    datetime(2025-03-27), "10.0.0.5", "8.8.8.8", 12600, 9800, "Allowed", 
    datetime(2025-03-28), "10.0.0.5", "8.8.8.8", 12200, 9500, "Allowed", 
    datetime(2025-03-29), "10.0.0.5", "8.8.8.8", 12100, 9900, "Allowed", 
    datetime(2025-03-30), "10.0.0.5", "8.8.8.8", 12500, 10100, "Allowed", 
    datetime(2025-03-31), "10.0.0.5", "8.8.8.8", 12400, 10000, "Allowed", 
    datetime(2025-04-01), "10.0.0.5", "8.8.8.8", 12600, 10200, "Allowed", 
    datetime(2025-04-02), "10.0.0.5", "8.8.8.8", 12700, 10300, "Allowed", 
    datetime(2025-04-03), "10.0.0.5", "198.51.100.99", 12000, 160000, "Allowed", 
    datetime(2025-04-04), "10.0.0.5", "8.8.8.8", 12400, 9900, "Allowed", 
    datetime(2025-04-05), "10.0.0.5", "8.8.8.8", 12200, 10100, "Allowed", 
    datetime(2025-04-06), "10.0.0.5", "8.8.8.8", 12300, 10000, "Allowed", 
    datetime(2025-04-07), "10.0.0.5", "8.8.8.8", 12400, 9900, "Allowed" 
]; 
FirewallLogs 
| make-series TotalBytesOut = sum(BytesOut) default=0 on TimeGenerated from datetime(2025-03-18) to datetime(2025-04-07) step 1d 
| extend (Anomalies, AnomalyScore, Baseline) = series_decompose_anomalies(TotalBytesOut, 1.5, -1, 'linefit', 1) 
| mv-expand TimeGenerated to typeof(datetime), TotalBytesOut to typeof(long), Anomalies to typeof(double), AnomalyScore to typeof(double), Baseline to typeof(long) 
| where Anomalies != 0 
| project TimeGenerated, TotalBytesOut, Baseline, AnomalyScore 
| sort by TimeGenerated 

Forecasting with series_decompose_forecast()

In addition to detecting anomalies, KQL allows you to forecast future values based on historical trends. This is useful for proactive monitoring and capacity planning. 

In cybersecurity, forecasting can help by: 

• Anticipating abnormal behavior before it becomes critical (e.g., sudden spikes in outbound traffic or failed login attempts) 

• Early warning for potential data exfiltration, DDoS activity, or malware spreading. 

• Capacity planning for VPN usage, firewall throughput, or bandwidth during predictable high-traffic windows (e.g., month-end backups) 

• Setting dynamic thresholds instead of fixed values — more adaptive detection logic 
  
  
• Predicting user login activity based on historical patterns
   

To better understand how forecasting works, let’s step away from security for a moment — and explore a fun example: forecasting pizza shop sales based on hourly order data.

Sample KQL – Adjust the Values and Observe the Changes

// Step 1: Simulate 4 weeks of hourly pizza sales
let PizzaSales = 
range hour from 1 to 24*7*4 step 1
| extend Timestamp = datetime(2018-03-01 05:00) + 1h * hour
| extend 
    HourlySales = 
        10 + (hour / 100.0) +                                 // gradual growth
        iff((hour / 24) % 7 in (5,6,0), 15.0, 5.0) +          // weekend boost
        (-1.5 * pow((hour % 24 - 12)/8.0, 2)) +               // night dip
        (rand() * 5.0)                                        // random noise
| extend 
    HourlySales = iff(hour in (150, 200, 780), HourlySales - 10.0, HourlySales)  // rainy dip
| extend 
    HourlySales = iff(hour in (300, 400, 600), HourlySales + 10.0, HourlySales)  // promo spike 
| project Timestamp, HourlySales;
// Step 2: Build time series and forecast next week's hourly sales
PizzaSales
| make-series Sales = max(HourlySales) 
    on Timestamp 
    from datetime(2018-03-01 05:00) 
    to datetime(2018-03-01 05:00) + 24*7*5h  // 5 weeks (4 real + 1 empty)
    step 1h
| extend SalesForecast = series_decompose_forecast(Sales, 24*7)  // forecast 7 days
| render timechart with(title = "Pizza Shop Hourly Sales + Forecast")