Search code examples
azure-data-explorerkqlkusto-explorer

Kusto Query to Filter and calculate the Time difference between rows

Timestamp Correlationld EventName
2022-04-12T14:10:49.387Z 02a9407f-3e2a-448c-961a-67c83d63720a HandlingRequest <-- 1a
2022-04-12T14:21:47.797Z 02a9407f-3e2a-448c-961a-67c83d63720a RequestComplete <-- 1b
2022-04-12T14:25:28.748Z 0455a17f-098a-4204-bf21-459280603f53 HandlingRequest
2022-04-12T16:09:09.066Z 017fac2a-2d7b-496a-aed8-36265674c19a HandlingRequest <-- 2a
2022-04-12T19:19:02.076Z 01aba138-470f-406f-927c-1055c3776231 HandlingRequest
2022-04-12T21:30:22.943Z 005dab40-b601-4f5d-a983-8a92945af909 HandlingRequest <-- 3a
2022-04-12T23:19:45.32Z 00676b81-cb5d-4847-b0de-328a3346c7e7 HandlingRequest
2022-04-12T23:25:28.291Z 00574df8-5c92-4667-8566-62dabf65208a HandlingRequest
2022-04-12T23:48:28.306Z 02d078c2-739b-460b-9fa9-776f7f97b863 HandlingRequest
2022-04-12T23:50:18.577Z 043117f6-c972-423a-8368-f0476e89ed9f HandlingRequest
2022-04-12T23:53:36.762Z 008cbb4c-21ca-421e-890f-335320d4e922 HandlingRequest
2022-04-13T00:00:26.387Z 005c4b88-7d14-48ba-b292-65e3a0971405 HandlingRequest
2022-04-13T00:03:51.262Z 02fd7cb9-0307-4eef-8d5c-dbd7f67199a7 HandlingRequest
2022-04-13T00:04:37.902Z 02a3fd47-e73e-4305-bcea-9b4ce3743cbb HandlingRequest
2022-04-13T00:05:52.555Z 03777fcd-c43b-41cf-969f-aad87f3a7318 HandlingRequest
2022-04-13T00:12:13.579Z 00159845-9089-4671-bb93-fdce974ce804 HandlingRequest
2022-04-13T00:23:13.138Z 005dab40-b601-4f5d-a983-8a92945af909 RequestComplete <-- 3b
2022-04-13T08:09:14.458Z 017fac2a-2d7b-496a-aed8-36265674c19a RequestComplete <-- 2b
2022-04-13T13:02:08.395Z 0142b2f2-032b-49d7-bcdf-fe0b1c32ef93 HandlingRequest

Refer attached image for the sample data table. I have correlation Id´s with 2 types of event names. Not all Correlation ID has both event name. I need to calculate the time difference between Handling request and Request complete for the correlation ID´s . Is there a way to filter only the ID´s that has these event names and calculate time diff. Thanks

Solution

  • Two quick ways I can think of -

    let t = datatable (Timestamp:datetime,Correlationld:string,EventName:string)
[
     "2022-04-13 00:12:13.5790", "00159845-9089-4671-bb93-fdce974ce804", "HandlingRequest"
    ,"2022-04-12 23:25:28.2910", "00574df8-5c92-4667-8566-62dabf65208a", "HandlingRequest"
    ,"2022-04-13 00:00:26.3870", "005c4b88-7d14-48ba-b292-65e3a0971405", "HandlingRequest"
    ,"2022-04-12 21:30:22.9430", "005dab40-b601-4f5d-a983-8a92945af909", "HandlingRequest"
    ,"2022-04-13 00:23:13.1380", "005dab40-b601-4f5d-a983-8a92945af909", "RequestComplete"
    ,"2022-04-12 23:19:45.3200", "00676b81-cb5d-4847-b0de-328a3346c7e7", "HandlingRequest"
    ,"2022-04-12 23:53:36.7620", "008cbb4c-21ca-421e-890f-335320d4e922", "HandlingRequest"
    ,"2022-04-13 13:02:08.3950", "0142b2f2-032b-49d7-bcdf-fe0b1c32ef93", "HandlingRequest"
    ,"2022-04-12 16:09:09.0660", "017fac2a-2d7b-496a-aed8-36265674c19a", "HandlingRequest"
    ,"2022-04-13 08:09:14.4580", "017fac2a-2d7b-496a-aed8-36265674c19a", "RequestComplete"
    ,"2022-04-12 19:19:02.0760", "01aba138-470f-406f-927c-1055c3776231", "HandlingRequest"
    ,"2022-04-13 00:04:37.9020", "02a3fd47-e73e-4305-bcea-9b4ce3743cbb", "HandlingRequest"
    ,"2022-04-12 14:10:49.3870", "02a9407f-3e2a-448c-961a-67c83d63720a", "HandlingRequest"
    ,"2022-04-12 14:21:47.7970", "02a9407f-3e2a-448c-961a-67c83d63720a", "RequestComplete"
    ,"2022-04-12 23:48:28.3060", "02d078c2-739b-460b-9fa9-776f7f97b863", "HandlingRequest"
    ,"2022-04-13 00:03:51.2620", "02fd7cb9-0307-4eef-8d5c-dbd7f67199a7", "HandlingRequest"
    ,"2022-04-13 00:05:52.5550", "03777fcd-c43b-41cf-969f-aad87f3a7318", "HandlingRequest"
    ,"2022-04-12 23:50:18.5770", "043117f6-c972-423a-8368-f0476e89ed9f", "HandlingRequest"
    ,"2022-04-12 14:25:28.7480", "0455a17f-098a-4204-bf21-459280603f53", "HandlingRequest"
];
// Option 1
t
| summarize diff = anyif(Timestamp,EventName == "RequestComplete") - anyif(Timestamp,EventName == "HandlingRequest") by Correlationld
| where isnotnull(diff);
// Option 2
t
| partition hint.strategy=native by Correlationld 
(
  order by Timestamp asc 
| project Correlationld, diff = Timestamp - prev(Timestamp) 
| where isnotnull(diff)
)

    Result set 1

    Correlationld diff
    005dab40-b601-4f5d-a983-8a92945af909 02:52:50.1950000
    017fac2a-2d7b-496a-aed8-36265674c19a 16:00:05.3920000
    02a9407f-3e2a-448c-961a-67c83d63720a 00:10:58.4100000

    Result set 2

    Correlationld diff
    005dab40-b601-4f5d-a983-8a92945af909 02:52:50.1950000
    02a9407f-3e2a-448c-961a-67c83d63720a 00:10:58.4100000
    017fac2a-2d7b-496a-aed8-36265674c19a 16:00:05.3920000

    Fiddle