Get the difference between Successive timestamps KQL

Question

I have a dataset as below

[
env_seqNum:int, env_tim:datetime, message:string, scenario: string, subscenario: string, requestId: string
1,2024-05-30T09:33:29.00Z, StartingOperation getToken XXX, ScenarioA, SubscenarioA, abcd-fghf,
2,2024-05-30T09:33:35.00Z, FinishingOperation getToken XXX, ScenarioA, SubscenarioA, abcd-fghf,
15,2024-05-30T09:33:55.00Z, StartingOperation getToken XXX, ScenarioA, SubscenarioA, abcd-fghf,
19,2024-05-30T09:33:58.00Z, FinishingOperation getToken XXX, ScenarioA, SubscenarioA, abcd-fghf,
]

As you can see, I have a token acquire operation thats done twice in same request for doing two API calls. Now, I want to know the cumulative time thats taken for this token acquistion operation per request. I cannot just do a diff of first and last row as it gives me a diff of 29 secs while in reality both token acquire operations took 9s (6 & 3) How do I get this working in KQL? I am wondering how can we leverage the env_seqNum column to perform this operation as its unique to every row int he logs or Is there a better way?

Answer 1

Table looks like below:

To get cumulative duration you can use below KQL query:

let Rith = datatable (
    ri_env_seqNum:int, 
    ri_env_tim:datetime, 
    ri_message:string, 
    ri_scenario:string, 
    ri_subscenario:string, 
    ri_requestId:string
) [
    1, datetime(2024-05-30T09:33:29.00Z), "StartingOperation getToken XXX", "ScenarioA", "SubscenarioA", "abcd-fghf",
    2, datetime(2024-05-30T09:33:35.00Z), "FinishingOperation getToken XXX", "ScenarioA", "SubscenarioA", "abcd-fghf",
    15, datetime(2024-05-30T09:33:55.00Z), "StartingOperation getToken XXX", "ScenarioA", "SubscenarioA", "abcd-fghf",
    19, datetime(2024-05-30T09:33:58.00Z), "FinishingOperation getToken XXX", "ScenarioA", "SubscenarioA", "abcd-fghf"
];
let ri_so_OP = Rith
    | where ri_message startswith "StartingOperation getToken"
    | project ri_requestId, start_seqNum = ri_env_seqNum, start_time = ri_env_tim;
let ri_fo_OP = Rith
    | where ri_message startswith "FinishingOperation getToken"
    | project ri_requestId, ri_finish_seqNum = ri_env_seqNum, finish_time = ri_env_tim;
let ri_pa_OP = ri_so_OP
    | join kind=inner (ri_fo_OP) on ri_requestId
    | where ri_finish_seqNum > start_seqNum
    | summarize ri_min_finish_seqNum = min(ri_finish_seqNum) by ri_requestId, start_seqNum, start_time
    | project ri_requestId, start_seqNum, start_time, ri_min_finish_seqNum;
ri_pa_OP
| join kind=inner (ri_fo_OP) on $left.ri_requestId == $right.ri_requestId and $left.ri_min_finish_seqNum == $right.ri_finish_seqNum
| project ri_requestId, start_time, finish_time, duration = datetime_diff('second', finish_time, start_time)
| summarize rith_cumula_duration = sum(duration) by ri_requestId
| project ri_requestId, rith_cumula_duration

Output:

Fiddle.