Close

Useful Application Insights Queries

Via the Kusto Query Language you cannot only query Log Analytics, but also Application Insights. We typically query Application Insights to get insight into API Management calls. Below are a few queries to get you started. Notice that I use the let statement in two queries to define variables. Normally you wouldn’t specify the timeframe in the query, but defining variables can be handy in other situations. That’s why I left it for educational purposes.

//Count requests by operation and resultcode
requests 
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode
| summarize count() by operation, resultCode
| order by operation asc, resultCode asc 

//Get Failed Requests and duration
let start=datetime("2019-12-01T08:00:00.000Z");
let end=datetime("2019-12-05T18:00:00.000Z");
requests
| where resultCode !startswith "2" 
| where timestamp > start and timestamp < end
| order by name asc, resultCode asc, timestamp desc
| project UTC=timestamp, name, resultCode, url, duration

//Get Exceptions
let start=datetime("2019-12-01T08:00:00.000Z");
let end=datetime("2019-12-05T18:00:00.000Z");
exceptions
| where timestamp > start and timestamp < end
| order by operation_Name, outerType, timestamp desc
| project UTC=timestamp, operation_Name, outerType, outerMessage 

//Get Duration of requests
requests
| extend durationTS = totimespan(duration)
| project operation=trim_start(' ', tostring(split(op//Get Duration of requests
requests
| extend durationTS = totimespan(duration)
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode, duration, durationTS
| order by operation asc, resultCode asc

// Get average duration per operation and resultcode
requests
| extend durationTS = totimespan(duration)
| summarize AvgDuration=avg(durationTS) by operation_Name, resultCode
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode
, toint(format_timespan(AvgDuration, 'fffffff'))
| order by operation asc//Count requests by operation and resultcode
requests 
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode
| summarize count() by operation, resultCode
| order by operation asc, resultCode asc 

//Get Failed Requests and duration
let start=datetime("2019-12-01T08:00:00.000Z");
let end=datetime("2019-12-05T18:00:00.000Z");
requests
| where resultCode !startswith "2" 
| where timestamp > start and timestamp < end
| order by name asc, resultCode asc, timestamp desc
| project UTC=timestamp, name, resultCode, url, duration

//Get Exceptions
let start=datetime("2019-12-01T08:00:00.000Z");
let end=datetime("2019-12-05T18:00:00.000Z");
exceptions
| where timestamp > start and timestamp < end
| order by operation_Name, outerType, timestamp desc
| project UTC=timestamp, operation_Name, outerType, outerMessage 
//Get Duration of requests
requests
| extend durationTS = totimespan(duration)
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode, duration, durationTS
| order by operation asc, resultCode asc

// Get average duration per operation and resultcode
requests
| extend durationTS = totimespan(duration)
| summarize AvgDuration=avg(durationTS) by operation_Name, resultCode
| project operation=trim_start(' ', tostring(split(operation_Name, "-")[1])), resultCode
, toint(format_timespan(AvgDuration, 'fffffff'))
| order by operation asc

To see LogAnalytics Queries: Link