Advanced KQL for Threat Hunting: Window Functions — Part 2

Photo by R Mo on Unsplash

In my previous blog, I explained what window functions are and how we can use them for threat hunting and detection by giving Cloud Account Takeover Attacks as an example and using the prev() function:

Advanced KQL for Threat Hunting: Window Functions — Part 1

In this post, I’ll explain the sliding_window_counts plugin and provide you with a query for Password Spray attack detection/hunting.

Sliding Window Counts

According to the documentation, the sliding_window_counts plugin calculates counts and distinct count of values in a sliding window over a lookback period. To make it more clear, here is a password spraying example:

Every hour:
    Query the last 3h of events:
        For each IP address:
            Get total count and distinct count of UserName

To make a sliding window, we query the last 3h of logs every hour. In other words, we use a 3h window and slide the window 1h at each step/iteration.

Sliding Window vs. Binning

You may be asking why we should use the sliding window instead of binning. When we use the bin() function, we group the data based on a value, and no group contains an event from any other groups. Continuing from the password spray attack, if we see 2 distinct UserName between 08:30–09:00 and 3 distinct UserName between 09:00–10:00 and use 3h bins, we can’t detect the scenario where 5 distinct UserName is seen in 3 hours from the same IP address because the first 2 distinct UserName is put into one bin, the other 3 UserName is put in another bin. This is where the sliding window comes into play. By using 3h window and sliding it 1h at each iteration, we can be sure that all the UserNames between 08:30–10:00 are counted together.

Below are two queries, one for SecurityEvent logs in Microsoft Sentinel and one for DeviceLogon logs in Defender for Endpoint:

SecurityEvent in Microsoft Sentinel

let start = ago(12h);
let end = now(); 
let lookbackWindow = 3h;  
let bin = 1h;
let threshold = 2;
SecurityEvent
| where EventID in (4624, 4625)
| where IpAddress !in ("127.0.0.1", "::1", "-")
| evaluate sliding_window_counts(TargetUserName, TimeGenerated, start, end, lookbackWindow, bin, IpAddress)
| sort by IpAddress, TimeGenerated asc
| where Dcount > threshold

DeviceLogon in Defender for Endpoint

let start = ago(12h);
let end = now(); 
let lookbackWindow = 3h;  
let bin = 1h;
let threshold = 2;
DeviceLogonEvents
| where Timestamp > ago(lookbackWindow)
| where RemoteIP !in ("127.0.0.1","::1","-") and isnotempty(RemoteIP)
| evaluate sliding_window_counts(AccountName, Timestamp, start, end, lookbackWindow, bin, RemoteIP)
| where Dcount > threshold

Thanks for reading this article! If you have any questions, leave a comment below. Want to master KQL for Threat Hunting, Detection Engineering, and DFIR in a hyper-realistic environment? Visit my academy for a free course!

The queries are also available on my GitHub Repo.

Threat-Hunting-and-Detection/Password Spray.md at main · Cyb3r-Monk/Threat-Hunting-and-Detection

Mehmet is the founder of Blu Raven Academy. He brings over 15 years of experience in cybersecurity, with a unique blend of expertise in KQL, threat hunting, detection engineering, and data science to his courses to help others advance their skills. Recognized four times as a Microsoft Security MVP, he is renowned for adapting the RITA beacon analyzer to KQL, developing novel methods for detecting threats, and for his insightful presentations at key conferences like the SANS DFIR Summit.