2023-10-16

Useful Application Insights Log Analytics Kusto Queries

We use Azure’s Application Insights features as the performance monitoring tool on a lot of our apps.

These are some queries I’ve found that are useful for various troubleshooting situations

Filter logs

If you use Serilog or Microsoft’s ILogger and use the structured logging template, the placeholders in your log messages turn into customDimensions on the traces collection you can use to filter and group by.

For example _log.LogInformation("Customer {CustomerId} logged in", user.CustomerId) you can find traces:

traces
| where customDimensions.CustomerId == '1234'
| where timestamp > ago(24hr)
| order by timestamp desc

Filter logs by class

This assumes you use Serilog or Microsoft’s ILogger. It should set a SourceContext custom property on the log message which will let you filter:

traces
| where customDimensions.SourceContext contains "MyApp.SomeNamespace"
| project timestamp, customDimensions.SourceContext, message, operation_Id

Most common exceptions by endpoint

exceptions
| summarize count() by operation_Name, type
| order by count_ desc

Chattiest APIs

dependencies
| summarize count() by operation_ParentId
| join kind=inner requests on $left.operation_ParentId == $right.id
| order by count_ desc
| project timestamp, operation_Name, id, duration, db_queries=count_

Note that this is an expensive query, set a date range filter in the UI to reduce data processing costs

This counts “dependencies” per operation. In most of my apps, the dependencies are DB calls, but your app might include HTTP calls to downstream services. If so, you can filter the dependencies collection by | where type == 'SQL'

Count dependencies per request

If you have an operationId in hand, you can count the db calls with the count aggregate:

dependencies
| where operation_ParentId == '467e51e6b3343359'
| count

Find slowest call by Controller and Action

requests
| extend Action = extract(" (/api)?/([A-Za-z]+/[A-Za-z]+)", 2, name)
| summarize avg(duration), count(), percentiles(duration, 90, 95, 99) by Action
| order by avg_duration desc

This one assumes you have the default .NET controller routing, and all the names of your operations are something like api/Controller/Action. The extend operator uses extract to regex out the controller name and action name. This helps aggregate across different parameters. For example, if you have a bunch of operations that are like

• GET api/Customer/Get/1234
• GET api/Customer/Get/4230
• GET api/Customer/Get/2566

This will aggregate them all under the Action name of just Customer/Get which averages across different specific customer resources.

azure, dotnet, performance