2.3 Time Functions and Joins

Time Functions

Time is the most important dimension in security investigation. Every question starts with “when”: when did the sign-in happen, when was the file downloaded, when did the alert fire. KQL provides a rich set of functions for working with datetime values.

ago() — Relative time windows

The function you will use most. ago() calculates a datetime relative to the current time.

1
2

SigninLogs
| where TimeGenerated > ago(24h)

Common intervals: ago(1h), ago(24h), ago(7d), ago(30d), ago(90d). You can also use minutes: ago(15m).

between() — Fixed time ranges

When you need a precise window — not relative to “now” — use between:

1
2

SigninLogs
| where TimeGenerated between (datetime(2026-03-15T14:00:00Z) .. datetime(2026-03-15T16:00:00Z))

This returns all sign-in events between 14:00 and 16:00 UTC on March 15. Essential when investigating a specific incident with a known timeline.

datetime_diff() — Calculate time differences

Measures the gap between two timestamps:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| extend MinutesSinceSignin = datetime_diff('minute', now(), TimeGenerated)
| project UserPrincipalName, TimeGenerated, MinutesSinceSignin

Units: 'second', 'minute', 'hour', 'day'. Use this to calculate dwell time (how long between initial compromise and detection) or response time (how long between alert and containment).

bin() — Time bucketing for trends

bin() rounds timestamps to a specified interval, creating discrete buckets for aggregation:

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0
| summarize FailedAttempts = count() by bin(TimeGenerated, 1h)
| sort by TimeGenerated asc

This creates an hourly failure count — the raw data behind a “failed sign-ins over time” chart. Change 1h to 1d for daily counts, 15m for 15-minute resolution, or 5m for near-real-time analysis.

Figure 2.3: bin(TimeGenerated, 1h) groups events into hourly buckets, making spikes visible. The red bars indicate anomalous failure volume.

Other useful time functions

Off-hours detection pattern:

1
2
3
4
5
6

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend Hour = hourofday(TimeGenerated)
| where Hour < 6 or Hour > 22
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, Hour

This surfaces successful sign-ins between 10pm and 6am — useful for detecting compromised accounts being used while the legitimate user is asleep.

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(3d)
| summarize
    Successful = countif(ResultType == 0),
    Failed = countif(ResultType != 0)
    by bin(TimeGenerated, 1h)
| sort by TimeGenerated asc
| render timechart

Joining Tables

Joins are how you correlate data across different sources — the fundamental operation in cross-product investigation. “Show me everyone who received a phishing email AND subsequently signed in from a new IP” requires joining EmailEvents to SigninLogs.

The four join kinds you will use

Worked example: correlating phishing emails with sign-ins

“Did any user who received a phishing email subsequently sign in from a new IP?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

let phishing_recipients =
    EmailEvents
    | where TimeGenerated > ago(1d)
    | where ThreatTypes has "Phish"
    | distinct RecipientEmailAddress;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where UserPrincipalName in (phishing_recipients)
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, Location
| sort by TimeGenerated desc

This uses a let statement to create a list of phishing recipients, then filters the sign-in logs to only those users. It is simpler than a full join and more efficient when you only need to check “is this user in the list?”

For a full join with data from both tables:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

EmailEvents
| where TimeGenerated > ago(1d)
| where ThreatTypes has "Phish"
| project EmailTime = TimeGenerated, Recipient = RecipientEmailAddress, Subject, SenderFromAddress
| join kind=innerunique (
    SigninLogs
    | where TimeGenerated > ago(1d)
    | where ResultType == 0
    | project SigninTime = TimeGenerated, User = UserPrincipalName, IPAddress, AppDisplayName
) on $left.Recipient == $right.User
| where SigninTime > EmailTime
| extend MinutesAfterEmail = datetime_diff('minute', SigninTime, EmailTime)
| project Recipient, Subject, SenderFromAddress, EmailTime, SigninTime, MinutesAfterEmail, IPAddress, AppDisplayName
| sort by MinutesAfterEmail asc

This joins phishing emails with subsequent successful sign-ins by the same user, calculates how many minutes elapsed between the email and the sign-in, and sorts by the shortest gap. A user who signs in from a new IP 5 minutes after receiving a phishing email is a strong compromise indicator.