Help needed on Kusto Query

ADX 156 Reputation points


I have a source data in my table called "Test"


In the Third record the AlarmCode was "0" & Description is "OK" which means the first & second record the alarm went reset. 4th & 5th records (alarms) are active. Now i have to calculate the AlarmTriggerTime & AlarmResetTime based on the AlarmCode & Description.

Please see the sample expected output format.


Would be grateful if you can help with Kusto query.


Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
501 questions
{count} votes

Accepted answer
  1. PRADEEPCHEEKATLA-MSFT 83,566 Reputation points Microsoft Employee

    Hello @ADX ,

    Thanks for the question and using MS Q&A platform.

    You can make a cross join and then summarize over the "smallest greater TimeStamp" as shown below:

    let test = datatable(AlarmCode:int, TimeStamp:datetime, Description:string)  
    4, datetime(2022-04-04,09:09:10), "Smoke Detected",  
    1, datetime(2022-04-04,09:10:10), "Sensor activated for too long",  
    0, datetime(2022-04-04,09:12:10), "OK",  
    2, datetime(2022-04-04,10:20:10), "Weight on the scale",  
    1, datetime(2022-04-04,10:20:10), "Sensor activated for too long",  
    0, datetime(2022-04-04,10:20:25), "OK"  
    | where Description != 'OK'  
    | extend _key = 1  
    | join kind=inner (  
        | where Description  == 'OK'  
        | extend _key = 1  
    ) on _key  
    | where TimeStamp1 > TimeStamp  
    | summarize arg_min(TimeStamp1, *) by AlarmCode,TimeStamp  
    | project AlarmCode, TimeStamp, Description, AlarmStartTime=TimeStamp, ResetTime=TimeStamp1  

    By using the above code, you will get expected output format:


    Hope this will help. Please let us know if any further queries.


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

0 additional answers

Sort by: Most helpful