Using KQL for Time-Delta and Pattern Matching in Identity Attack Detection

Explore advanced techniques using datetime_diff, next, and statistical aggregation

The Kusto Query Language (KQL) is a powerful tool for analyzing large datasets, particularly in security contexts like detecting identity attacks such as password sprays or brute-force attempts. By leveraging KQL’s time-delta calculations and pattern matching capabilities, analysts can identify suspicious login patterns with temporal logic. This blog post explores advanced techniques using datetime_diff, next, and statistical aggregations, with results stored in JSON arrays for flexible downstream processing.

Understanding Identity Attacks

Password spray and brute-force attacks involve repeated login attempts to compromise accounts. Password sprays use a single password across multiple accounts, while brute-force attacks target a single account with many passwords. Both exhibit temporal patterns, such as rapid or periodic login attempts, which KQL can detect by analyzing time differences and event sequences.

Time-Delta Analysis with datetime_diff and next

KQL’s datetime_diff function calculates the time difference between two timestamps, enabling analysis of inter-event gaps. Combined with the next operator, it helps track the time between consecutive login attempts, which is critical for identifying attack patterns.

Example: Calculating Time Gaps

Suppose you have a log table SignInLogs with columns Timestamp, UserPrincipalName, and ResultType (indicating success or failure). To detect rapid login attempts, calculate the time difference between consecutive failed logins for each user.

SignInLogs
| where ResultType == “Failed”
| sort by UserPrincipalName, Timestamp asc
| extend TimeDelta = datetime_diff(’second’, Timestamp, next(Timestamp, UserPrincipalName))
| where TimeDelta < 60
| summarize AttemptCount = count(), MinTimeDelta = min(TimeDelta), MaxTimeDelta = max(TimeDelta) by UserPrincipalName

Explanation:

• sort by UserPrincipalName, Timestamp asc: Orders events by user and time to ensure next retrieves the subsequent event for the same user.

• datetime_diff(’second’, Timestamp, next(Timestamp, UserPrincipalName)): Computes the time difference (in seconds) between the current and next login attempt for the same user.

• where TimeDelta < 60: Filters for gaps less than 60 seconds, indicating rapid attempts.

• summarize: Aggregates the count of attempts and the min/max time deltas per user to identify suspicious patterns.

This query flags users with frequent login attempts, a hallmark of brute-force or password spray attacks.

Pattern Matching for Attack Detection

KQL’s pattern matching capabilities, such as series_stats or custom aggregations, can further refine detection by identifying consistent or anomalous time-delta patterns.

Example: Detecting Periodic Patterns

Password sprays often involve periodic attempts to avoid detection. To identify users with consistent inter-event gaps:

SignInLogs
| where ResultType == “Failed”
| sort by UserPrincipalName, Timestamp asc
| extend TimeDelta = datetime_diff(’second’, Timestamp, next(Timestamp, UserPrincipalName))
| where TimeDelta != null
| summarize TimeDeltas = make_list(TimeDelta) by UserPrincipalName
| extend Stats = series_stats(TimeDeltas)
| project UserPrincipalName, TimeDeltas, AvgTimeDelta = Stats.avg, StdDevTimeDelta = Stats.stdev
| where StdDevTimeDelta < 10 and AvgTimeDelta < 300

Explanation:

• make_list(TimeDelta): Creates an array of time deltas for each user.

• series_stats(TimeDeltas): Computes statistical metrics like average and standard deviation of time deltas.

• where StdDevTimeDelta < 10 and AvgTimeDelta < 300: Flags users with consistent (low standard deviation) and rapid (low average) time deltas, suggesting automated attack patterns.

Storing Results in JSON Arrays

Storing results in JSON arrays enables flexible downstream processing, such as integration with alerting systems or machine learning pipelines. KQL’s make_list and tojson functions are ideal for this.

Example: JSON Output for Downstream Processing

To store time-delta data in a JSON array for further analysis:

SignInLogs
| where ResultType == “Failed”
| sort by UserPrincipalName, Timestamp asc
| extend TimeDelta = datetime_diff(’second’, Timestamp, next(Timestamp, UserPrincipalName))
| where TimeDelta != null
| summarize TimeDeltas = make_list(TimeDelta), AttemptCount = count() by UserPrincipalName
| extend JsonOutput = tojson(bag_pack(”User”, UserPrincipalName, “Attempts”, AttemptCount, “TimeDeltas”, TimeDeltas))
| project JsonOutput

Explanation:

• make_list(TimeDelta): Aggregates time deltas into an array.

• bag_pack: Combines user, attempt count, and time deltas into a dynamic object.

• tojson: Converts the object to a JSON string, ready for export or processing.

Sample output:

{
  “User”: “user@domain.com”,
  “Attempts”: 50,
  “TimeDeltas”: [15, 12, 14, 13, ...]
}

This JSON format is easily parsed by downstream systems for alerting, visualization, or further analysis.

Practical Considerations

• Performance: Sorting and next operations can be resource-intensive on large datasets. Use partition by UserPrincipalName to optimize queries by processing users in parallel.

• False Positives: Rapid logins may occur legitimately (e.g., misconfigured apps). Combine time-delta analysis with other signals, like IP addresses or device types, to reduce false positives.

• Threshold Tuning: Adjust time-delta thresholds (e.g., 60 seconds) based on your environment’s baseline behavior.

TLDR

KQL’s datetime_diff, next, and statistical functions provide a robust framework for detecting identity attacks like password sprays and brute-force attempts. By calculating inter-event gaps and analyzing patterns, security teams can identify suspicious behavior. Storing results in JSON arrays ensures compatibility with downstream systems, enabling seamless integration into broader security workflows. With these techniques, KQL empowers analysts to stay ahead of evolving threats.