CODEWITHSUNDEEP
azure-log-analyticsazure-data-explorer
Nicolae Daian
Nicolae Daian

Reputation: 1145

How to write a Kusto query to find two consecutive rows that have the same value in a field

I need to write a Kusto query for Azure Log Analysis that finds consecutive events that have the same value in a field (same error code). We basically need to find if the requests fail twice in a row. The case where a request fails, one succeeds and one fails is not to be returned.

Upvotes: 4

Views: 6832

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

Assuming you have a table with Id, Datetime, and a ErrorCode, you can utilize prev() function to achieve this:

https://learn.microsoft.com/en-us/azure/kusto/query/prevfunction

datatable(Id:string, Datetime:datetime, ErrorCode:string)
[
    '1', datetime(2018-10-16 00:00), 'Error 1',
    '1', datetime(2018-10-16 00:01), 'Error 1',
    '2', datetime(2018-10-16 00:02), 'Error 1',
    '2', datetime(2018-10-16 00:03), 'Error 2',
]
| order by Id, Datetime asc 
| extend prevErrorCode = prev(ErrorCode), prevId=prev(Id)
| where prevErrorCode==ErrorCode and prevId  == Id

Upvotes: 7

Related Questions