Kusto query to find out the difference in duration on a table

Question

I am trying to find out the duration of time taken in below sample table.

So I need the difference in duration of the method entered with start and exited with End with their specific message. For ex : For method ABC I want (2023-12-06T010:08:52.8576365Z - 2023-12-06T08:08:52.8576365Z)

So I need the difference in duration of the method entered with start and exited with End with their specific message. For ex : For method ABC I want (2023-12-06T010:08:52.8576365Z - 2023-12-06T08:08:52.8576365Z) difference in method executiontiming of method abc

Answer 1

In order to find the difference between start and end of the message, you can use below code.

datatable(Timestamp:datetime,Message:string,StartorEnd:string)
[
'2023-12-06T01:08:52.8576365Z','Entered into method():ABC', 'Start',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'Start',
'2023-12-06T01:09:52.8576365Z','Entered into method():ABC', 'End',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'End',
]
| sort by Message
| evaluate pivot(StartorEnd,min(Timestamp))
| extend difference= End-Start
| project Message, difference

The above query sorts the table by the "Message" column, pivots the "StartorEnd" column to create two new columns "Start" and "End", and then calculates the difference between the "End" and "Start" columns. The output for the above code,

Output:

Message	difference
Entered into method():ABC	00:01:00
Entered into method():XYZ	00:00:00

demo

Answer 2

Here is an alternative Kusto query to find the difference in duration for each method entered with "Start" and exited with "End" based on your sample table. This solution has lots of flexibility, so you can change it based on your scenario.

let T = datatable(Timestamp:datetime, Message:string, StartorEnd:string)
[
'2023-12-06T01:08:52.8576365Z','Entered into method():ABC', 'Start',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'Start',
'2023-12-06T01:09:52.8576365Z','Entered into method():ABC', 'End',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'End',
];

T
| summarize
    StartTime = take_anyif(Timestamp, StartorEnd == "Start"), // Selects one entry randomly for each start event
    EndTime = take_anyif(Timestamp, StartorEnd == "End") // Selects one entry randomly for each end event
by Message
| extend Duration = EndTime - StartTime

Explanation:

• The take_anyif function is used to select one entry randomly for each start and end event.

If you have multiple entries and you want to:

• Calculate the duration using the very first start event and the very last end event for each method, you can replace take_anyif with minif and maxif respectively.
• Handle unique start and end events with an additional "id" column, you can include that column in the summarize by clause.

Here's an example of how to use minif and maxif for the very first and last events:

T
| summarize
    StartTime = minif(Timestamp, StartorEnd == "Start"), // Selects the very first start event
    EndTime = maxif(Timestamp, StartorEnd == "End") // Selects the very last end event
by Message
| extend Duration = EndTime - StartTime

And if you have an "id" column, you can include it in the summarize by clause:

T
| summarize
    StartTime = minif(Timestamp, StartorEnd == "Start"), // Selects the very first start event
    EndTime = maxif(Timestamp, StartorEnd == "End") // Selects the very last end event
by Message, Id // Include the "Id" column here
| extend Duration = EndTime - StartTime

These variations allow you to customize how you select start and end events based on your specific requirements.

Answer 3

Here is another possible solution that doesn't use pivot or summarize, but uses serialization (sorting). Note that this will breakdown if you have multiple events per message:

datatable(Timestamp:datetime,Message:string,StartorEnd:string)
[
'2023-12-06T01:08:52.8576365Z','Entered into method():ABC', 'Start',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'Start',
'2023-12-06T01:09:52.8576365Z','Entered into method():ABC', 'End',
'2023-12-06T01:08:52.8576365Z','Entered into method():XYZ', 'End',
]
| sort by Message, Timestamp asc
| extend IsMatch = prev(Message) == Message and prev(StartorEnd) == "Start" and StartorEnd == "End"
| extend difference = iif(IsMatch, Timestamp-prev(Timestamp), timespan(null))
| where isnotempty(difference)
| project Message, difference

Explanation:

• First I sort the rows by the message and then the timestamp in ascending order
• Next I create a column IsMatch that stores a boolean on whether we have a match in two consecutive lines based on three conditions. We look at the "end" message and check if:
  • the previous row is a message match,
  • if the StartorEnd is "End"
  • and the prev row StartorEnd is "Start"
• If it's a match, we assign the timestamp difference to the difference column, else it will be null
• Lastly we filter out the rows where difference is null