Advanced KQL for Threat Hunting: Window Functions — Part 2
In my previous blog, I explained what window functions are and how we can use them for threat hunting and detection by giving AiTM Phishing attacks as an example and using the prev() function.
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:
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
