Date time difference within a column (Kusto Query Language)

Question

I have a data set like this for single server. So multiple servers are having multiple records like this in a kusto table eg Table1.

TimeStamp   State   Servername  Type
7/13/2021   Healthy abcdefgh    Server
7/13/2021   Repair  abcdefgh    Server
7/14/2021   Repair  abcdefgh    Server
7/15/2021   Repair  abcdefgh    Server
7/15/2021   Healthy abcdefgh    Server
7/15/2021   Healthy abcdefgh    Server
7/16/2021   Repair  abcdefgh    Server
7/17/2021   Repair  abcdefgh    Server
7/17/2021   Repair  abcdefgh    Server
7/17/2021   Repair  abcdefgh    Server
7/18/2021   Repair  abcdefgh    Server
7/18/2021   Repair  abcdefgh    Server
7/19/2021   Repair  abcdefgh    Server
7/19/2021   Repair  abcdefgh    Server

I need to know the list of servers which are stuck in repair state for more than 10 days at once. (query should not add the previous repair state time as for the above server it was in repair and came to healthy). Just need the consecutive repair time. Can someone help? I have made the following query but this does not work as expected.

Table1
| order by TIMESTAMP desc
| extend prevstate = prev(State)
| where prevstate == State
| summarize arg_max(TIMESTAMP,*) by Servername  //summarizing the top time//
| extend endtime = TIMESTAMP //assigning the top time//
| join kind= innerunique (Table1) //joining same table to find the startdate//
on Servername
| order by TIMESTAMP desc
| extend prevstate = prev(State)
| where prevstate == State
| summarize arg_min(TIMESTAMP,*) by Servername   //summarizing the start time//
| extend starttime= TIMESTAMP
| extend Duration = datetime_diff('day',endtime,starttime)
| project Server, State, Duration

Alexander Vos de Wael Alexander Vos de Wael · Accepted Answer · 2021-07-29T14:33:09

First group records into uninterrupted partitions with the same Servername and State, then check whether the oldest and youngest observations are more than 10 days apart:

datatable(TimeStamp:datetime, Servername:string, State:string)
[
  '2021-07-01', 'abc', 'Healthy',
  '2021-07-02', 'abc', 'Repair',
  '2021-07-04', 'def', 'Healthy',
  '2021-07-05', 'abc', 'Healthy',
  '2021-07-07', 'abc', 'Repair',
  '2021-07-10', 'def', 'Healthy',
  '2021-07-18', 'abc', 'Repair',
]
| order by Servername, TimeStamp
| extend new_partition = Servername != prev(Servername) or State != prev(State)
| extend partition_id = row_cumsum(toint(new_partition))
| where State == 'Repair'
| summarize 
    repair_start = min(TimeStamp),
    repair_last = max(TimeStamp)
    by Servername, partition_id
| project Servername, repair_start, repair_last, duration = repair_last - repair_start
| where duration > 10d

Date time difference within a column (Kusto Query Language)

1 Answers