LLI Wiki — My Digital Knowledge Base

KQL: slow Application Insights requests with their dependencies

KQL query listing long requests and the details of their dependencies (SQL, Service Bus).

This KQL query (Application Insights) finds slow requests and returns, for each one, the details of its dependencies (SQL queries, Service Bus calls, etc.).

How it works

Query

// ---------------- scope: Application Insights resource, for example appi-prd-contoso-mt-01 (middle tier) ----------------------
//---this query fetches all requests longer than the threshold (here 40 s),
//---and for those requests also retrieves the dependencies ( = SQL queries, Service Bus requests, ...)
//---for the dependencies: their number, their total duration in seconds, and the content of those that lasted at least 10 ms
requests 
| join (dependencies 
     | summarize operationNameList=make_list_if(strcat("Duration (s): ", duration/1000, "\r\nDependency: ", name, "\r\nResult: ", data),duration>=10), opid=count(),opdur_sec = sum(duration) / 1000 by operation_Id 
     | project operationNameList, operation_Id , opid, opdur_sec
     ) 
     on operation_Id 
| where duration  > 40000 
| project timestamp, user_Id,Duration_Sec=duration/1000, Duration_Sec_dependencies= opdur_sec , Count_dependencies = opid,url, name, operation_Id, operationNameList
| order by Duration_Sec

Notes

Mastodon LLIMastodon SocialFosstodon