Search code examples
azureazure-data-explorerkqlkusto-explorer

Difference between 2 consecutive values in Kusto

I have the following script:

let StartTime = datetime(2022-02-18 10:10:00 AM);
let EndTime = datetime(2022-02-18 10:15:00 AM);
MachineEvents
| where Timestamp between (StartTime .. EndTime)
| where Id == "00112233" and Name == "Higher"
| top 2 by Timestamp
| project Timestamp, Value

I got the following result:

enter image description here

What I am trying to achieve after that is to check if the last Value received (in this case for example it is 15451.433) is less than 30,000. If the condition is true, then I should check again the difference between the last two consecutive values (in this case : 15451.433 - 15457.083). If the difference is < 0 then I should return the Value as true, else it should return as false (by other words the Value should give a boolean value instead of double as shown in the figure)

Solution 
  • datatable(Timestamp:datetime, Value:double)
[
 datetime(2022-02-18 10:15:00 AM), 15457.083,
 datetime(2022-02-18 10:14:00 AM), 15451.433,
 datetime(2022-02-18 10:13:00 AM), 15433.333,
 datetime(2022-02-18 10:12:00 AM), 15411.111
]
| top 2 by Timestamp
| project Timestamp, Value
| extend nextValue=next(Value)
| extend finalResult = iff(Value < 30000, nextValue - Value < 0, false)
| top 1 by Timestamp
| project finalResult

    Output:

    finalResult
    1