join-with-time

It is an inner join: a pair needs a key both sides have, and the two rows' timestamps at most within apart.

How it runs: both sides are cut into time bins of within. The left side is read first. The right side reads a bin once the left side has finished the bins around it, and keeps only the rows whose key the left side has there. With hint.strategy=twophase, both sides first collect per-bin filters of their keys, then read only the rows whose key both sides have. Both strategies return the same pairs.

Timestamps: rows pair by the timestamp stored with them. A column a query computes under that name is shown in the output, but does not move a row in time; see a computed timestamp.

within can widen: bins of within cover a time range of at most 10,079 × within (just under 7 days at within 1m). Over a longer range they widen to the smallest multiple of within that covers it, and rows up to the widened span apart pair too. A WithinWidened warning names the widened span; narrow the time range to keep within.

Requirements:

• The query needs a bound on timestamp: a --since window, or a where timestamp > ago(...) in the query.
• Each side reads a table through where, project, extend and parse only. A side's project keeps timestamp and the join keys.

Output: the left side's columns, then the right side's. A right column whose name the left side also has is named $right.<name>.

Memory: a side that outgrows its memory budget drops whole join keys, in the same order on both sides, and the result carries a JoinKeysSampled warning with the share of keys kept.

Syntax

join-with-time (table) on column within duration

Pairs rows with equal column whose timestamps are at most duration apart

Parameters

Syntax

join-with-time hint.strategy=twophase (table) on column within duration

Collects both sides' per-bin key filters before reading their rows

Parameters

Examples

Example 1 — Count the logs written within a minute of an archive export in the same trace

otel_appsec
| where span_name == "archive.export"
| project timestamp, trace_id
| join-with-time (otel_appsec | where isnotempty(body) | project timestamp, trace_id, body) on trace_id within 1m
| count

Example 2 — Show each export next to the logs around it; the right side's timestamp is $right.timestamp

otel_appsec
| where span_name == "archive.export"
| project timestamp, trace_id, collection = tostring(attributes['export.collection'])
| join-with-time (otel_appsec | where isnotempty(body) | project timestamp, trace_id, body) on trace_id within 1m
| project collection, exported_at = timestamp, logged_at = $right.timestamp, body
| sort by exported_at asc, logged_at asc
| take 3

Example 3 — Count the logs around the exports of each collection

otel_appsec
| where span_name == "archive.export"
| project timestamp, trace_id, collection = tostring(attributes['export.collection'])
| join-with-time (otel_appsec | where isnotempty(body) | project timestamp, trace_id) on trace_id within 1m
| summarize logs = count() by collection
| sort by collection asc

Related

• join
• trace-find