prev()

  • Article
  • 09/30/2024

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

Returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set.

Syntax

prev(column, [ offset ], [ default_value ] )

Learn more about syntax conventions.

Parameters

Name Type Required Description
column string ✔️ The column from which to get the values.
offset int The offset to go back in rows. The default is 1.
default_value scalar The default value to be used when there are no previous rows from which to take the value. The default is null.

Examples

Filter data based on comparison between adjacent rows

The following query returns rows that show breaks longer than a quarter of a second between calls to sensor-9.

Run the query

TransformedSensorsData
| where SensorName == 'sensor-9'
| sort by Timestamp asc
| extend timeDiffInMilliseconds = datetime_diff('millisecond', Timestamp, prev(Timestamp, 1))
| where timeDiffInMilliseconds > 250

Output

Timestamp SensorName Value PublisherId MachineId timeDiff
2022-04-13T00:58:53.048506Z sensor-9 0.39217481975439894 fdbd39ab-82ac-4ca0-99ed-2f83daf3f9bb M100 251
2022-04-13T01:07:09.63713Z sensor-9 0.46645392778288297 e3ed081e-501b-4d59-8e60-8524633d9131 M100 313
2022-04-13T01:07:10.858267Z sensor-9 0.693091598493419 278ca033-2b5e-4f2c-b493-00319b275aea M100 254
2022-04-13T01:07:11.203834Z sensor-9 0.52415808840249778 4ea27181-392d-4947-b811-ad5af02a54bb M100 331
2022-04-13T01:07:14.431908Z sensor-9 0.35430645405452 0af415c2-59dc-4a50-89c3-9a18ae5d621f M100 268
... ... ... ... ... ...

Perform aggregation based on comparison between adjacent rows

The following query calculates the average time difference in milliseconds between calls to sensor-9.

Run the query

TransformedSensorsData
| where SensorName == 'sensor-9'
| sort by Timestamp asc
| extend timeDiffInMilliseconds = datetime_diff('millisecond', Timestamp, prev(Timestamp, 1))
| summarize avg(timeDiffInMilliseconds)

Output

avg_timeDiffInMilliseconds
30.726900061254298

Extend row with data from the previous row

In the following query, as part of the serialization done with the serialize operator, a new column previous_session_type is added with data from the previous row. Since there was no session prior to the first session, the column is empty in the first row.

Run the query

ConferenceSessions
| where conference == 'Build 2019'
| serialize previous_session_type = prev(session_type)
| project time_and_duration, session_title, session_type, previous_session_type

Output

time_and_duration session_title session_type previous_session_type
Mon, May 6, 8:30-10:00 am Vision Keynote - Satya Nadella Keynote
Mon, May 6, 1:20-1:40 pm Azure Data Explorer: Advanced Time Series analysis Expo Session Keynote
Mon, May 6, 2:00-3:00 pm Azure's Data Platform - Powering Modern Applications and Cloud Scale Analytics at Petabyte Scale Breakout Expo Session
Mon, May 6, 4:00-4:20 pm How BASF is using Azure Data Services Expo Session Breakout
Mon, May 6, 6:50 - 7:10 pm Azure Data Explorer: Operationalize your ML models Expo Session Expo Session
... ... ... ...