Kusto Query to Filter and calculate the Time difference between rows

Question

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

Answer 1

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

Answer 2

Check if this works for you:

datatable(Timestamp:datetime, CorrelationId:string, EventName:string)
[
    datetime(2022-01-01), "a", "HandlingRequest",
    datetime(2022-01-01), "b", "HandlingRequest",
    datetime(2022-01-01), "c", "HandlingRequest",
    datetime(2022-01-02), "c", "RequestComplete",
    datetime(2022-01-01), "d", "HandlingRequest",
    datetime(2022-01-01), "e", "HandlingRequest",
    datetime(2022-01-01), "f", "HandlingRequest",
    datetime(2022-01-03), "e", "RequestComplete",
    datetime(2022-01-04), "b", "RequestComplete",
]
| order by CorrelationId asc , Timestamp asc 
| extend Correlated = prev(CorrelationId) == CorrelationId and EventName  == "RequestComplete" and prev(EventName) == "HandlingRequest"
| project CorrelationId, HandlingRequest = prev(Timestamp), RequestComplete = Timestamp, Correlated
| extend Diff = RequestComplete - HandlingRequest
| where Correlated | project-away Correlated

CorrelationId	HandlingRequest	RequestComplete	Diff
b	2022-01-01 00:00:00.0000000	2022-01-04 00:00:00.0000000	3.00:00:00
c	2022-01-01 00:00:00.0000000	2022-01-02 00:00:00.0000000	1.00:00:00
e	2022-01-01 00:00:00.0000000	2022-01-03 00:00:00.0000000	2.00:00:00