KQL Time Functions and Temporal Analysis

Example: dayofweek(now()) returns an integer (0=Sunday, 6=Saturday). Note: dayofweek() returns a timespan representing the offset from Sunday, but you can compare it to 0d for Sunday, 1d for Monday, etc. However, the exam often uses datetime_part() which is more flexible.

Example: datetime_part('DayOfYear', now()) returns the day number within the year.

Syntax: bin(datetime, timespan)

Example: bin(now(), 1h) rounds the current time down to the start of the current hour. If now is 14:37:22, it returns 14:00:00.

Common bin sizes: 1h, 30m, 15m, 1d. For weekly bins, use 7d but note that the base is the Unix epoch (1970-01-01). To align bins to a specific day (e.g., Monday), use startofweek() before binning.

These functions are useful for grouping events into natural time boundaries.

Pattern 1: Time-based aggregation Count events per hour:

SecurityEvent
| where TimeGenerated > ago(24h)
| summarize Count = count() by bin(TimeGenerated, 1h)
| render timechart

Pattern 2: Detecting spikes with time-series functions The series_decompose() function can detect anomalies in a time series. First, you need to create a time series using make-series:

let timeSeries = 
    SecurityEvent
    | where TimeGenerated > ago(7d)
    | make-series Count=count() on TimeGenerated from ago(7d) to now() step 1h;
timeSeries
| extend (anomalies, score, baseline) = series_decompose_anomalies(Count, 1.5, -1, 'linefit')
| render anomalychart with (anomalycolumns=anomalies)

Pattern 3: Sliding window analysis Detect multiple failed logins within a 5-minute window:

SigninLogs
| where ResultType == "50057" // User account is disabled
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 5m)
| where FailedAttempts > 10

Pattern 4: Time difference between events Calculate time between consecutive events for the same user:

SigninLogs
| where ResultType == "0" // Success
| sort by UserPrincipalName, TimeGenerated asc
| extend TimeDiff = TimeGenerated - prev(TimeGenerated, 1)
| where TimeDiff between (0s .. 5m) // Rapid successive logins

let IsBusinessHour = (t:datetime) {
    let h = datetime_part('Hour', t);
    let dow = datetime_part('DayOfWeek', t); // 1=Sunday, 7=Saturday
    print case(
        dow between (2 .. 6) and h between (9 .. 17), true,
        false
    )
};

But this is beyond the exam scope.

SigninLogs
| where ResultType == "50057" // User account is disabled
| summarize FailedAttempts = count() by UserPrincipalName, bin(TimeGenerated, 5m)
| where FailedAttempts > 10

In production, they must consider that legitimate users might trigger false positives if they have multiple devices. To reduce noise, they add a condition on AppDisplayName to exclude known internal apps. Performance is critical: the query scans billions of rows daily, so they always filter on TimeGenerated > ago(1d) first. Misconfiguration: if they use bin() on a non-indexed column, the query becomes slow. They also discovered that ago(1d) returns exactly 24 hours ago, which may miss events if the query runs at a different time; they now use a fixed datetime range for historical analysis.

CommonSecurityLog
| where TimeGenerated > ago(7d)
| make-series TrafficCount=count() on TimeGenerated from ago(7d) to now() step 1h by DeviceVendor
| extend (anomalies, score, baseline) = series_decompose_anomalies(TrafficCount, 1.5, -1, 'linefit')
| mv-expand TimeGenerated, TrafficCount, anomalies, score, baseline
| where anomalies == 1

In production, they tune the threshold parameter (1.5) to balance sensitivity. They also use series_decompose_forecast() for predictive alerts. Misconfiguration: if the step is too small (e.g., 1m), the time series becomes noisy and anomaly detection fails. They set step based on the typical attack duration. They also ensure that now() is evaluated per query run, not cached.