# KQL Time Functions and Temporal Analysis

> Chapter 30 of the Courseiva SC-200 curriculum — https://courseiva.com/learn/sc-200/kql-time-functions-sc200

**Official objective:** 2.2 — Sentinel

## Introduction

Detecting security patterns such as brute force attacks or beaconing requires mastery of KQL time functions and temporal analysis – a critical skill for the SC-200 exam, especially in the Sentinel domain (Objective 2.2). Approximately 15-20% of exam questions in the 'Create and manage analytics rules' section require you to write or interpret KQL queries that manipulate time. You will learn how to use datetime literals, timespan arithmetic, time extraction functions, and binning to build time-based aggregations and detect security patterns like brute force attacks or beaconing. Mastery of these functions is essential for creating efficient and accurate detection rules in Microsoft Sentinel.

## Time Machine for Security Logs

KQL time functions are like a time machine for security logs. Imagine you are a detective investigating a crime that happened over several days. You have a stack of time-stamped reports, but they are in random order. To find patterns, you need to group events by hour, look for spikes at specific times, and compare activity from one day to the next. KQL's time functions let you 'travel' through your log data: you can shift all timestamps by a fixed offset (like moving a clock forward or backward), round them to the nearest hour or day (like sorting reports into hourly bins), and extract parts like the day of week or hour of day (like labeling each report with the day it occurred). The `timespan` data type is the fuel for your time machine—it represents a duration, like '2 hours' or '3.5 days', and can be added to or subtracted from datetime values. Just as a detective uses a timeline to connect events, KQL time functions let you create time-based aggregations, detect anomalies over sliding windows, and correlate events across different time zones. Without these functions, analyzing temporal patterns in security data would be like trying to solve a crime without a clock—you'd miss the crucial timing of events.

## Core explanation

### 1. The datetime and timespan Data Types
KQL has two primary data types for temporal operations: `datetime` and `timespan`.

- **datetime**: Represents an absolute point in time. Default format is ISO 8601 (e.g., `2025-03-15T14:30:00Z`). You can write datetime literals as strings, but best practice is to use the `datetime()` function: `datetime(2025-03-15 14:30:00)`. In queries, the `now()` function returns the current UTC time.

- **timespan**: Represents a duration, such as `1h`, `30m`, `2.5d`. Internally, KQL stores both as 64-bit integers (datetime as ticks since epoch, timespan as ticks). You can add or subtract a timespan from a datetime: `datetime(2025-03-15) + 1d` yields `2025-03-16T00:00:00Z`. You can also subtract two datetimes to get a timespan: `now() - datetime(2025-01-01)` returns the duration since New Year.

### 2. Time Literals and Functions
KQL provides several functions to work with time:

- **now()**: Returns the current UTC datetime. Use `now(-1d)` for 1 day ago.
- **ago()**: Shorthand for relative time: `ago(1h)` is equivalent to `now(-1h)`. 
- **datetime()**: Parses a string into datetime. Always use this instead of relying on implicit conversion.
- **todatetime()**: Converts a value to datetime (e.g., from a dynamic field).
- **format_datetime()**: Formats a datetime as a string (e.g., `format_datetime(now(), 'yyyy-MM-dd')`).

### 3. Extracting Parts of a Date
Use these functions to extract components:

- **getyear()**, **getmonth()**, **dayofmonth()**, **dayofweek()**, **dayofyear()**, **hourofday()**, **minuteofday()**, **secondofday()**.

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.

- **datetime_part(part, datetime)**: Extracts a specific part as integer. Part can be 'Year', 'Month', 'Day', 'Hour', 'Minute', 'Second', 'Millisecond', 'Microsecond', 'Nanosecond'.

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

### 4. Time Rounding and Binning
Binning is crucial for aggregating events into time windows. The `bin()` function rounds a datetime down to the nearest multiple of a given timespan.

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.

- **startofday()**, **startofweek()**, **startofmonth()**, **startofyear()**: Return the start of the respective period. `startofweek()` uses Sunday as the first day of the week by default.
- **endofday()**, **endofweek()**, etc.: Return the end of the period.

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

### 5. Time Zone Handling
KQL by default works in UTC. All datetime values in logs should be in UTC. If you need to convert to local time, use `datetime_utc_to_local()` or `datetime_local_to_utc()`. However, the exam expects you to work in UTC. Avoid time zone conversions unless explicitly required.

### 6. Common Temporal Patterns in Security Queries

**Pattern 1: Time-based aggregation**
Count events per hour:
```kusto
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`:
```kusto
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:
```kusto
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:
```kusto
SigninLogs
| where ResultType == "0" // Success
| sort by UserPrincipalName, TimeGenerated asc
| extend TimeDiff = TimeGenerated - prev(TimeGenerated, 1)
| where TimeDiff between (0s .. 5m) // Rapid successive logins
```

### 7. Performance Considerations
- Use `where` filters on `TimeGenerated` as early as possible. The `TimeGenerated` column is often indexed, so filtering on it reduces data scanned.
- Avoid using `datetime()` on large string columns inside `where` clauses; instead, compare directly to datetime columns.
- Binning with `bin()` is efficient because KQL can leverage hash-based aggregation.

### 8. Exam-Specific Details
- The `ago()` function is preferred over `now(-1h)` because it is cleaner and more readable.
- `datetime_part()` is the go-to function for extracting date parts. Memorize the string arguments: 'Year', 'Month', 'Day', 'Hour', 'Minute', 'Second', 'Millisecond', 'Microsecond', 'Nanosecond', 'DayOfYear', 'DayOfWeek'.
- `dayofweek()` returns a timespan, not an integer. To get an integer (0=Sunday), use `datetime_part('DayOfWeek', datetime) - 1` or `toint(dayofweek(datetime))` (which returns 0-6).
- `startofweek()` uses Sunday as the start. If you need Monday, use `startofweek(datetime) + 1d`.
- The `bin()` function rounds down. For rounding to nearest, you can add half the bin size before binning.
- Timespan literals: `1d`, `2h`, `30m`, `10s`, `100ms`. You can also use `1.5d` for 1.5 days.
- `now()` returns UTC. In Sentinel, all logs are in UTC. Do not convert to local time unless explicitly required.
- The `make-series` operator creates a time series with a fixed step, which is essential for anomaly detection functions like `series_decompose_anomalies()`.

### 9. Common Mistakes
- Using `datetime` as a string without conversion: `where TimeGenerated > '2025-01-01'` works implicitly but is less reliable. Always use `datetime('2025-01-01')`.
- Confusing `dayofweek()` (returns timespan) with `datetime_part('DayOfWeek', ...)` (returns integer).
- Forgetting that `bin()` rounds down, not up. For example, `bin(datetime(2025-03-15T14:59:59), 1h)` returns `14:00:00`, not `15:00:00`.
- Using `now()` in a `let` statement that is evaluated once; if you need the current time for each row, use `now()` in the query body.

### 10. Advanced: Calendar and Business Hours
For business hours analysis, you can create a custom function:
```kusto
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.

## Real-world context

### Scenario 1: Brute Force Detection in Enterprise Environment
A large enterprise with 50,000 users uses Azure AD Sign-in logs. The security team needs to detect brute force attacks where an attacker tries multiple passwords for the same user within a short window. They create a KQL query that bins `TimeGenerated` by 5-minute windows and counts failed logins per user. The query:
```kusto
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.

### Scenario 2: Anomaly Detection on Network Traffic
A managed security service provider (MSSP) monitors network traffic for hundreds of clients. They use `make-series` to create hourly traffic counts and apply `series_decompose_anomalies()` to detect spikes. The query:
```kusto
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.

### Scenario 3: Time Zone Confusion in Global Deployments
A multinational company has offices worldwide. Their logs are in UTC, but analysts want to see events in local time. They use `datetime_utc_to_local()` with the correct time zone, e.g., `datetime_utc_to_local(TimeGenerated, 'US Eastern Standard Time')`. However, this function is not available in all KQL contexts; in Sentinel, it works. They must be careful with daylight saving time transitions. Misconfiguration: using `datetime_local_to_utc()` on already UTC data shifts times incorrectly. The security team learned to always store logs in UTC and only convert for display.

## Exam focus

### What the SC-200 Exam Tests
Objective 2.2: 'Create and manage analytics rules in Microsoft Sentinel'. This includes writing KQL queries that use time functions for detection. The exam expects you to:
- Use `ago()`, `now()`, `datetime()` correctly.
- Apply `bin()` for time-based aggregation.
- Extract date parts with `datetime_part()`.
- Calculate time differences using subtraction and `prev()`.
- Understand the difference between `dayofweek()` (returns timespan) and `datetime_part('DayOfWeek', ...)` (returns integer).
- Know that `startofweek()` uses Sunday as the first day.
- Recognize that `TimeGenerated` is the primary time column in Sentinel.

### Common Wrong Answers and Why
1. **Using `now()` in a `let` statement and expecting it to be re-evaluated per row**: `let currentTime = now();` is evaluated once. If you need per-row evaluation, use `now()` directly in the query. Wrong answer: 'Use `let` to store the current time for performance.'
2. **Confusing `ago(1d)` with `now(-24h)`**: They are functionally identical, but `ago()` is cleaner. The exam may test that `ago(1d)` is equivalent to `now(-1d)`. Wrong answer: '`ago(1d)` returns the start of the day.'
3. **Using `bin()` on a string column**: `bin()` only works on numeric and datetime columns. Wrong answer: 'Use `bin()` to group text values.'
4. **Assuming `dayofweek()` returns an integer**: It returns a timespan. To get an integer, use `datetime_part('DayOfWeek', ...)`. Wrong answer: '`dayofweek(now())` returns 1 for Monday.'
5. **Forgetting that `bin()` rounds down**: For example, `bin(datetime(2025-03-15T14:59:59), 1h)` returns 14:00:00, not 15:00:00. Wrong answer: 'It rounds to the nearest hour.'

### Specific Numbers and Values
- `ago(7d)` is the most common time range for hunting queries.
- `bin(TimeGenerated, 1h)` is the standard for hourly aggregation.
- `datetime_part('DayOfWeek', ...)` returns 1 (Sunday) through 7 (Saturday).
- `startofweek()` returns the preceding Sunday at midnight.
- `timespan` literals: `1d`, `2h`, `30m`, `10s`, `100ms`.
- `now()` returns UTC; Sentinel logs are in UTC.

### Edge Cases and Exceptions
- When using `make-series`, the `from` and `to` arguments must be datetime literals or `ago()`; using `now()` inside `make-series` is allowed but evaluated once.
- `series_decompose_anomalies()` requires a time series with uniform step; gaps are filled with `fill_forward()`.
- Time zone functions `datetime_utc_to_local()` and `datetime_local_to_utc()` are available in Sentinel but not in Azure Data Explorer directly.

### How to Eliminate Wrong Answers
- If a query uses `datetime` as a string without `datetime()`, it is likely incorrect because implicit conversion can fail.
- If a query uses `bin()` on a non-datetime column, it will cause an error.
- If a query uses `dayofweek()` in a `where` clause comparing to an integer, it will not work because `dayofweek()` returns a timespan. Look for `datetime_part('DayOfWeek', ...)` instead.
- If a query uses `now()` inside a `let` and then uses that variable in a `where` clause that is expected to be dynamic per row, it is wrong.
- If a query uses `startofweek()` and expects Monday, it is wrong; Sunday is the default.

## Step by step

1. **Identify the time column** — Every security log in Microsoft Sentinel has a `TimeGenerated` column (datetime, UTC). This is the primary time field used for temporal analysis. For custom logs, there may be other datetime fields. Always filter on `TimeGenerated` first to limit the data scope. Use `where TimeGenerated > ago(24h)` to get the last 24 hours. This step is critical for performance because Sentinel indexes `TimeGenerated`. Without this filter, the query may scan all data in the workspace, causing timeouts and throttling.
2. **Filter to relevant time range** — Use `ago()` or `datetime()` to define the time window. For example, `where TimeGenerated between (ago(7d) .. ago(1d))` gives data from 7 days ago to 1 day ago. Avoid using `now()` in a `let` statement if you need the same 'now' throughout; instead, use `let start = now(-7d); let end = now();` and then `where TimeGenerated between (start .. end)`. This ensures consistency across the query. The exam often tests the difference between `ago(1d)` (24 hours ago) and `now(-1d)` (same as ago).
3. **Round timestamps with bin()** — Use `bin(TimeGenerated, 1h)` to group events into hourly buckets. This is essential for `summarize` operations. For example, `summarize count() by bin(TimeGenerated, 1h)` creates a time series. The bin size should match the desired granularity: `1h` for hourly, `1d` for daily. Remember that `bin()` rounds down, so events at 14:59:59 fall into the 14:00:00 bin. If you need to align to natural boundaries (e.g., start of day), use `startofday()` instead of `bin()`.
4. **Extract date parts for analysis** — Use `datetime_part()` to extract components like hour, day of week, or month. For example, `extend Hour = datetime_part('Hour', TimeGenerated)` then `summarize count() by Hour`. This helps detect patterns like brute force attacks that spike during off-hours. The exam expects you to know the string arguments: 'DayOfWeek' returns 1=Sunday, 7=Saturday. To get Monday=1, you can subtract 1 or use `case` logic. `datetime_part('DayOfYear', ...)` is useful for annual comparisons.
5. **Calculate time differences** — Use subtraction of datetime values to get a timespan. For example, `extend TimeSinceLastEvent = TimeGenerated - prev(TimeGenerated, 1)` in a sorted dataset. This is used for detecting rapid successive events (e.g., password spray). The `prev()` function requires a sort order. You can also use `row_cumsum()` for cumulative sums over time. The exam may ask you to find events where the time difference is less than a threshold, like `where TimeSinceLastEvent < 1m`.

## Comparisons

### bin() vs startofday()/startofweek()

**bin():**
- Rounds down to the nearest multiple of a specified timespan (e.g., 1h, 1d).
- Works with any timespan value, not just natural boundaries.
- Useful for arbitrary time windows like 5-minute bins.
- Example: bin(TimeGenerated, 1h) groups events into hourly buckets starting from epoch.
- The base alignment is relative to Unix epoch (1970-01-01), so bins may not align with natural days if using non-24h bins.

**startofday()/startofweek():**
- Returns the start of a natural time period (day, week, month, year).
- Only works for predefined periods: startofday, startofweek, startofmonth, startofyear.
- Useful for aligning to calendar boundaries (e.g., start of today).
- Example: startofday(TimeGenerated) groups events into calendar days.
- startofweek uses Sunday as the first day of the week; can be adjusted by adding 1d for Monday.

## Common misconceptions

- **Misconception:** ago(1d) returns the start of the current day. **Reality:** ago(1d) returns a datetime exactly 24 hours before the current time. For example, if now is 2025-03-15T14:30:00Z, ago(1d) returns 2025-03-14T14:30:00Z. To get the start of the current day, use startofday(now()).
- **Misconception:** dayofweek() returns an integer representing the day of the week. **Reality:** dayofweek() returns a timespan representing the offset from Sunday. For example, dayofweek(datetime('2025-03-17')) (Monday) returns 1d (a timespan of 1 day). To get an integer, use datetime_part('DayOfWeek', datetime) which returns 1 for Sunday, 2 for Monday, etc., or use toint(dayofweek(datetime)) which returns 0 for Sunday, 1 for Monday, etc.
- **Misconception:** bin() rounds a datetime to the nearest time unit. **Reality:** bin() always rounds down to the nearest multiple of the specified timespan. For example, bin(datetime('2025-03-15T14:59:59'), 1h) returns 2025-03-15T14:00:00, not 15:00:00. To round to the nearest, you can add half the bin size before binning: bin(datetime + 0.5h, 1h).
- **Misconception:** startofweek() returns Monday as the first day of the week. **Reality:** startofweek() returns the preceding Sunday at midnight. For example, startofweek(datetime('2025-03-19')) (Wednesday) returns 2025-03-16T00:00:00 (Sunday). To get Monday, use startofweek(datetime) + 1d.
- **Misconception:** now() in a let statement is re-evaluated for each row in the query. **Reality:** let statements are evaluated once when the query is parsed. If you use let currentTime = now();, it captures the time at the start of query execution. For per-row evaluation, use now() directly in the query body, e.g., where TimeGenerated > now(-1d).

## Key takeaways

- Always filter on TimeGenerated as early as possible to reduce data scan and improve performance.
- Use ago() for relative time ranges; it is cleaner than now(-1d) and is preferred in Sentinel queries.
- bin() rounds down to the nearest multiple of the specified timespan; it does not round to the nearest.
- datetime_part('DayOfWeek', datetime) returns 1 (Sunday) through 7 (Saturday); dayofweek() returns a timespan.
- startofweek() returns the preceding Sunday; to get Monday, add 1d.
- now() returns UTC; all Sentinel logs are in UTC; avoid time zone conversions unless explicitly required.
- Use make-series for creating time series with uniform step for anomaly detection functions.
- timespan literals: 1d, 2h, 30m, 10s, 100ms; can be fractional like 1.5d.
- The prev() function requires a sort order; use sort by before extend to calculate time differences between consecutive events.
- In let statements, now() is evaluated once; use now() directly in the query body for per-row evaluation.

## FAQ

**What is the difference between ago() and now(-1d)?**

They are functionally identical: ago(1d) returns the same as now(-1d). However, ago() is cleaner and more readable, and it is the preferred syntax in Microsoft Sentinel. The exam expects you to use ago() for relative time filters. For example, `where TimeGenerated > ago(7d)` is standard.

**How do I get the day of week as an integer in KQL?**

Use datetime_part('DayOfWeek', datetime) which returns 1 for Sunday, 2 for Monday, ..., 7 for Saturday. Alternatively, toint(dayofweek(datetime)) returns 0 for Sunday, 1 for Monday, ..., 6 for Saturday. The exam often tests that dayofweek() returns a timespan, not an integer.

**Why does bin() sometimes not align with calendar days?**

bin() rounds down to the nearest multiple of the specified timespan relative to the Unix epoch (1970-01-01). For example, bin(TimeGenerated, 1d) may not align with midnight if the epoch offset is not a multiple of 1 day. To align to calendar days, use startofday() instead.

**Can I use now() inside a let statement and expect it to update per row?**

No. let statements are evaluated once at query start. If you need the current time to be evaluated for each row, use now() directly in the query body. For example, `where TimeGenerated > now(-1h)` is evaluated per row.

**How do I calculate the time difference between consecutive events in KQL?**

First, sort the data by the entity and time: `sort by UserPrincipalName, TimeGenerated asc`. Then use `extend TimeDiff = TimeGenerated - prev(TimeGenerated, 1)`. The prev() function returns the previous row's value. Then you can filter on TimeDiff, e.g., `where TimeDiff < 1m`.

**What is the default first day of the week in startofweek()?**

Sunday. startofweek(datetime) returns the preceding Sunday at midnight. If you need Monday, use startofweek(datetime) + 1d.

**How do I create a time series for anomaly detection?**

Use the make-series operator: `make-series Count=count() on TimeGenerated from ago(7d) to now() step 1h by SomeColumn`. This creates a time series with uniform step. Then apply series_decompose_anomalies() to detect spikes.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/sc-200/kql-time-functions-sc200