Kusto cheatsheet

requests durations distribution with steps (bins) of 100 ms

app("bustroker-sales-webapi").requests
| where name == "POST SalesController/Post"
| project req_timestamp = timestamp, req_id = id, req_name = name, req_url = url, req_success = success, req_resultCode = resultCode, req_duration = duration, req_operation_name = operation_Name, req_operation_Id = operation_Id
| summarize count() by bin(req_duration, 100)
| sort by req_duration asc
| render timechart 

requests duration percentiles and requests dependencies duration percentiles

app("bustroker-sales-webapi").requests
| where name == "POST SalesControler/Post"
| project req_timestamp = timestamp, req_id = id, req_name = name, req_url = url, req_success = success, req_resultCode = resultCode, req_duration = duration, req_operation_name = operation_Name, req_operation_Id = operation_Id
| summarize count(), avg(req_duration), stdev(req_duration) , percentiles(req_duration, 25, 50, 95, 99)
| project type_tag = "Core Request", total = count_ , avg_duration = avg_req_duration, stdev = stdev_req_duration, percentile_duration_25 = percentile_req_duration_25, percentile_duration_50 = percentile_req_duration_50, percentile_duration_95 = percentile_req_duration_95, percentile_duration_99 = percentile_req_duration_99  
| union(
app("bustroker-sales-webapi").requests
| project req_timestamp = timestamp, req_id = id, req_name = name, req_url = url, req_success = success, req_resultCode = resultCode, req_duration = duration, req_operation_name = operation_Name, req_operation_Id = operation_Id
| join (app("bustroker-sales-webapi").dependencies
        | project dep_timestamp = timestamp, dep_id = id, dep_target = target, dep_type = type, dep_name = name, dep_data = data, dep_success = success, 
                dep_duration = duration, dep_operation_Id = operation_Id, dep_operation_parentId = operation_ParentId,
                dep_data_clean = iif(name == "Replace document", strcat_array(array_slice(split(data, "/"),0, array_length(split(data, "/"))-2), "/"), data))
        on $left.req_id == $right.dep_operation_parentId
| order by req_id, req_timestamp desc, dep_timestamp asc
| project req_timestamp, req_id, req_name, req_url, req_success, req_resultCode, req_duration, req_operation_name, req_operation_Id,
        dep_timestamp, dep_id, dep_target, dep_type, dep_name, dep_data, dep_data_clean, dep_success, 
        dep_duration, dep_operation_Id, dep_operation_parentId,
        dep_type_unique_key = strcat(dep_name, " => ", dep_data_clean),
        dep_type_tag = case(strcat(dep_name, " => ", dep_data_clean) == "POST /api/translate => https://bustroker-crypto-webapi.azurewebsites.net/api/translate", "Crypto Translate PIN",
                       strcat(dep_name, " => ", dep_data_clean) == "POST / => http://bustroker.exit:6050/", "Exit",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Create Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Query Sessions",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Replace Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Create Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Replace Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "List documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos List Applications",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos Query Applications",
                       strcat(dep_name, " => ", dep_data_clean) startswith "HEAD templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates?", "Blob Head Template",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Denied.html?", "Blob GET DeniedTemplate",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Accepted.html?", "Blob GET AcceptedTemplate",
                        "Unclassified!")
| summarize count(), avg(dep_duration), stdev(dep_duration) , percentiles(dep_duration, 25, 50, 95, 99) by dep_type_tag
| project type_tag = dep_type_tag, total = count_, avg_duration = avg_dep_duration, stdev = stdev_dep_duration, percentile_duration_25 = percentile_dep_duration_25, percentile_duration_50 = percentile_dep_duration_50, percentile_duration_95 = percentile_dep_duration_95, percentile_duration_99 = percentile_dep_duration_99  
| union (
app("bustroker-sales-webapi").dependencies 
| where type == "RedisConnector"
| summarize total = count(), avg_duration = avg(duration), stdev = stdev(duration) , percentiles(duration, 25 , 50, 95, 99) by name
| project type_tag = strcat("RedisConnector", name), total, avg_duration, stdev, percentile_duration_25, percentile_duration_50 , percentile_duration_95, percentile_duration_99 
)| order by type_tag asc)

## Duration distributions per dependency 

app("bustroker-sales-webapi").requests
| project req_timestamp = timestamp, req_id = id, req_name = name, req_url = url, req_success = success, req_resultCode = resultCode, req_duration = duration, req_operation_name = operation_Name, req_operation_Id = operation_Id
| join (app("bustroker-sales-webapi").dependencies
        | project dep_timestamp = timestamp, dep_id = id, dep_target = target, dep_type = type, dep_name = name, dep_data = data, dep_success = success, 
                dep_duration = duration, dep_operation_Id = operation_Id, dep_operation_parentId = operation_ParentId,
                dep_data_clean = iif(name == "Replace document", strcat_array(array_slice(split(data, "/"),0, array_length(split(data, "/"))-2), "/"), data))
        on $left.req_id == $right.dep_operation_parentId
| order by req_id, req_timestamp desc, dep_timestamp asc
| project req_timestamp, req_id, req_name, req_url, req_success, req_resultCode, req_duration, req_operation_name, req_operation_Id,
        dep_timestamp, dep_id, dep_target, dep_type, dep_name, dep_data, dep_data_clean, dep_success, 
        dep_duration, dep_operation_Id, dep_operation_parentId,
        dep_type_unique_key = strcat(dep_name, " => ", dep_data_clean),
        dep_type_tag = case(strcat(dep_name, " => ", dep_data_clean) == "POST /api/translate => https://bustroker.crypto.azurewebsites.net/api/translate", "Crypto",
                       strcat(dep_name, " => ", dep_data_clean) == "POST / => http://bustroker.exit:6050/", "Exit",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Create Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Query Sessions",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Replace Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Create Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Replace Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "List documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos List Applications",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos Query Applications",
                       strcat(dep_name, " => ", dep_data_clean) startswith "HEAD templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates?", "Blob Head Template",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Denied.html?", "Blob GET DeniedTemplate",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Accepted.html?", "Blob GET AcceptedTemplate",
                        "Unclassified!") 
| summarize count() by bin(dep_duration, 100), dep_type_tag
| order by dep_duration asc
| render timechart


# partitioning times by exact operation

## relation between requests duration and total number of requests 

app("bustroker-sales-webapi").requests
| where url == "https://bustroker-sales-webapi.core.azurewebsites.net/Transactions/Financial/Sale"
| summarize number_of_requests_rescaled = count()/10 by bin(timestamp, 10m)
| union(
app("bustroker-sales-webapi").requests
| where url == "https://bustroker-sales-webapi.core.azurewebsites.net/Transactions/Financial/Sale"
| summarize avg_duration_rescaled = avg(duration)/2000  by bin(timestamp, 10m))
| render timechart 


# Exploration

app("bustroker-sales-webapi").requests
| project req_timestamp = timestamp, req_id = id, req_name = name, req_url = url, req_success = success, req_resultCode = resultCode, req_duration = duration, req_operation_name = operation_Name, req_operation_Id = operation_Id
| join (app("bustroker-sales-webapi").dependencies
        | project dep_timestamp = timestamp, dep_id = id, dep_target = target, dep_type = type, dep_name = name, dep_data = data, dep_success = success, 
                dep_duration = duration, dep_operation_Id = operation_Id, dep_operation_parentId = operation_ParentId,
                dep_data_clean = iif(name == "Replace document", strcat_array(array_slice(split(data, "/"),0, array_length(split(data, "/"))-2), "/"), data))
        on $left.req_id == $right.dep_operation_parentId
| order by req_id, req_timestamp desc, dep_timestamp asc
| project req_timestamp, req_id, req_name, req_url, req_success, req_resultCode, req_duration, req_operation_name, req_operation_Id,
        dep_timestamp, dep_id, dep_target, dep_type, dep_name, dep_data, dep_data_clean, dep_success, 
        dep_duration, dep_operation_Id, dep_operation_parentId,
        dep_type_unique_key = strcat(dep_name, " => ", dep_data_clean),
        dep_type_tag = case(strcat(dep_name, " => ", dep_data_clean) == "POST /api/translate => https://bustroker.crypto.azurewebsites.net/api/translate", "Crypto",
                       strcat(dep_name, " => ", dep_data_clean) == "POST / => http://bustroker.exit:6050/", "Exit",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Create Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Query Sessions",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Sessions/docs", "Cosmos Replace Session",
                       strcat(dep_name, " => ", dep_data_clean) == "Create document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Create Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "Replace document => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/transactions/colls/Transactions/docs", "Cosmos Replace Transaction",
                       strcat(dep_name, " => ", dep_data_clean) == "List documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos List Applications",
                       strcat(dep_name, " => ", dep_data_clean) == "Query documents => https://bustroker-cosmosdb-westeurope.documents.azure.com/dbs/masterdata/colls/Application/docs", "Cosmos Query Applications",
                       strcat(dep_name, " => ", dep_data_clean) startswith "HEAD templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates?", "Blob Head Template",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Denied.html?", "Blob GET DeniedTemplate",
                       strcat(dep_name, " => ", dep_data_clean) startswith "GET templatesstorage => https://templatesstorage.blob.core.windows.net:443/templates/Sale/DisplayTemplate.Accepted.html?", "Blob GET AcceptedTemplate",
                        "Unclassified!") 
| summarize count() by bin(dep_duration, 100), dep_type_tag
| order by dep_duration asc
| render timechart