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
- Scope: the Application Insights resource of the application tier (middle tier), for example
appi-prd-contoso-mt-01. - Requests whose duration exceeds a threshold are selected (here 40,000 ms, adjust as needed).
- For each
operation_Id, dependencies are counted, their duration is summed (in seconds), and the content of those lasting at least 10 ms is listed.
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
durationis expressed in milliseconds: changeduration > 40000to adjust the threshold.- The second threshold (
duration>=10) filters out very short dependencies from the detailed list.