KQL Query to calculate time difference between multiple statements

Question

timestamp [IST]                 OperationID                       Message
2022/30/6, 4:10:00.460 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  Work flow Started
2022/30/6, 4:12:00.091 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  External Work Flow1 Started
2022/30/6, 4:10:00.460 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  External Work Flow2 Started
2022/30/6, 4:14:10.791 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  External Work Flow1 Ended
2022/30/6, 4:15:10.460 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  External Work Flow2 Ended
2022/30/6, 4:20:04.343 AM   fbf759a0-d4be-4d07-adfb-7090a207e667  Work flow Ended

Hi Team,

I am looking for a help on KQL which display the processing time overall and external component processing times For Example: For above table we need an output like in seconds

Operation ID                           EndtoEndTimings    ExternalWorkflow1Timings  ExternalWorkflow2Timings
fbf759a0-d4be-4d07-adfb-7090a207e667     600 sec          120 sec                   300 sec

Appreciate if any one can help on this.

Answer 1

I have reproduced in my environment and got expected results as below:

The below KQL query which worked for me:

let x= datatable(timestamp:datetime,OperationID: string,Message: string)[
 "2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Work flow Started"
   ,"2022-06-30 04:12:00.091" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow1 Started"
   ,"2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow2 Started"
   ,"2022-06-30 04:14:10.791" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow1 Ended"
   ,"2022-06-30 04:15:10.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow2 Ended"
   ,"2022-06-30 04:20:04.343" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Work flow Ended"
];
let t1=x
|summarize EndtoEndTimings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
let t2=x
|where Message contains "External Work Flow1 Started" or Message contains "External Work Flow1 Ended"
|summarize ExternalWorkflow1Timings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
let t3=x
|where Message contains "External Work Flow2 Started" or Message contains "External Work Flow2 Ended"
|summarize ExternalWorkflow2Timings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
let t4=t1
|join kind=inner t2 on OperationID;
t4
| join kind=inner t3 on OperationID
| project-away OperationID1,OperationID2

Output:

Fiddle.