Help needed on Kusto Query

ADX 156 Reputation points
2022-04-07T04:32:14.24+00:00

Hi,

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

190804-image.png

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.

190740-image.png

Would be grateful if you can help with Kusto query.

Thanks

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
    2022-04-07T11:40:36.283+00:00

    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"  
      
    ];  
      
    test  
      
    | where Description != 'OK'  
      
    | extend _key = 1  
      
    | join kind=inner (  
      
        test  
      
        | 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:

    190887-image.png

    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