Determine duration of events in Kusto?

Question

How do I calculate durations using Kusto in the following example?

Goal: Determine total "handling time" of a blob in Azure Blob Storage

Background:

• Blob is uploaded to Storage Account using Azure Data Factory (ADF).
  • This consists of several API calls and methods (CreatePathFile, LeaseFile, AppendFile, FlushFile, LeaseFile) to the Storage Account.
  • An typical end-to-end ADF upload of a large file looks like:
• Blob is then downloaded from Storage Account using an Azure Function
  • This consists also of several API calls (but only GetBlob methods)
  • Looks largely the same

So now I've combined both of these queries to show all OperationNames performed on a given blob:

• Query:

//==================================================//
// Assign variables
//==================================================//
let varStart = ago(2d);
let varEnd = now();
let varStorageAccount = 'stgaccountname';
let varSampleUploadUri = 'https://stgaccountname.dfs.core.windows.net/containername/filename.csv';
let varSampleDownloadUri = replace(@'%2F', @'/', replace(@'.dfs.', @'.blob.', tostring(varSampleUploadUri)));
//==================================================//
// Filter table
//==================================================//
StorageBlobLogs
| where TimeGenerated between (varStart .. varEnd)
  and AccountName == varStorageAccount
  //and StatusText == varStatus
  and split(Uri, '?')[0] == varSampleUploadUri
  or split(Uri, '?')[0] == varSampleDownloadUri
//==================================================//
// Group and parse results
//==================================================//
| summarize 
  count() by OperationName,
  TimeGenerated,
  UserAgent = tostring(split(UserAgentHeader, ' ')[0]),
  ChunkSize = iif(OperationName == 'GetBlob', format_bytes(ResponseBodySize, 2, 'MB'), format_bytes(RequestBodySize, 2, 'MB')),
  StatusCode,
  StatusText
| order by TimeGenerated asc

• Results: A nice combined table

Question is:

• How do I adjust the query to show the duration from the earliest TimeGenerated event to the last TimeGenerated event?
  • This would show the duration between first upload API call and final download API call.

Answer 1

Try this:

YourQuery
| summarize Duration = max(TimeGenerated) - min(TimeGenerated)

Here's an example with some synthetic data in datatable format:

datatable(Timestamp:datetime, SomeGuid:string)
[
    datetime(2021-05-27T06:03:59.5708689Z), "2e76bf18-04ed-4d3f-afe3-cff87c532b10", 
    datetime(2021-05-27T06:04:03.3834404Z), "27a7f8ec-f0a7-4fad-9784-996051d2a9f9", 
    datetime(2021-05-27T06:05:06.1334979Z), "568ab8a4-2ed2-486f-a7b9-1d27379b52db", 
    datetime(2021-05-27T06:06:20.3212560Z), "edd1f7d2-5fc5-482f-88d3-6ad16a1ae000", 
    datetime(2021-05-27T06:07:30.6034174Z), "cf5cb66b-05b1-43f3-ad04-23c56f96687e", 
]
| summarize Duration = max(Timestamp) - min(Timestamp)

Output:

00:03:31.0325485