row_window_session()

  • Article

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Calculates session start values of a column in a serialized row set.

Syntax

row_window_session ( Expr , MaxDistanceFromFirst , MaxDistanceBetweenNeighbors [, Restart] )

Parameters

Name Type Required Description
Expr datetime ✔️ An expression whose values are grouped together in sessions. When Expr results in a null value, the next value starts a new session.
MaxDistanceFromFirst timespan ✔️ Determines when a new session starts using the maximum distance between the current Expr value and its value at the beginning of the session.
MaxDistanceBetweenNeighbors timespan ✔️ Another criterion for starting a new session using the maximum distance from one value of Expr to the next.
Restart boolean If specified, every value that evaluates to true immediately restarts the session.

Learn more about syntax conventions.

Returns

The function returns the values at the beginning of each session. It uses the following conceptual calculation model:

  1. Iterates over the input sequence of Expr values in order.

  2. For each value, it decides whether to create a new session.

  3. If a new session is created, the function returns the current value of Expr. Otherwise, it returns the previous value of Expr.

Note

The value represents a new session if it meets the following conditions using the logical OR operation:

  • If there was no previous session value, or the previous session value was null.
  • If the value of Expr equals or exceeds the previous session value plus MaxDistanceFromFirst.
  • If the value of Expr equals or exceeds the previous value of Expr plus MaxDistanceBetweenNeighbors.
  • If Restart condition is specified and evaluates to true.

Examples

The following example calculates session start values for a table, datatable, with a sequence ID column and a Timestamp column to record the time of each record. The data is sorted by the sequence IDs and timestamps and then the example returns values for ID, Timestamp, and a new SessionStarted column. A session can't exceed one hour. It continues for as long as records are less than five minutes apart and the ID stays the same. The example includes records that are less than five minutes apart.

datatable (ID:string, Timestamp:datetime) [
    "1", datetime(2024-04-11 10:00:00),
    "2", datetime(2024-04-11 10:18:00),
    "1", datetime(2024-04-11 11:00:00),
    "3", datetime(2024-04-11 11:30:00),
    "2", datetime(2024-04-11 13:30:00),
    "2", datetime(2024-04-11 10:16:00)
]
| sort by ID asc, Timestamp asc
| extend SessionStarted = row_window_session(Timestamp, 1h, 5m, ID != prev(ID))

Output

ID Timestamp SessionStarted
1 2024-04-11T10:00:00Z 2024-04-11T10:00:00Z
1 2024-04-11T11:00:00Z 2024-04-11T11:00:00Z
2 2024-04-11T10:16:00Z 2024-04-11T10:16:00Z
2 2024-04-11T10:18:00Z 2024-04-11T10:16:00Z
2 2024-04-11T13:30:00Z 2024-04-11T13:30:00Z
3 2024-04-11T11:30:00Z 2024-04-11T11:30:00Z

Related content