Query your top 10 log messages from App Insights using KQL

We use App Insights at work to collect all our logged messages. App Insights comes with a very powerful query language confusingly named Kusto Query Language (KQL) that lets you get whatever you want from the logs. However App Insights shows you surprisingly little out of the box. So without some configuration you may not be getting as much value from your logs as you could.

The thing I really wanted to see was the most common log messages ordered by how often they had happened in the specified time. This is the KQL query for that:

// Setup mapping array to make severity level use friendly name
let error_mapping = dynamic(
    "4": "Critical",
    "3": "Error",
    "2": "Warning",
    "1": "Information",
    "0": "Verbose"
| extend application = customDimensions.Application
// Only show messages from the production instance (required if you share your App Insights accross environments)
| where application == "My App Name (Production)"
// Group the messages by the MessageTemplate, get the time of first and last occurence of the error and get the custom dimensions for a random example of the error
| summarize Count = count(), min(timestamp), max(timestamp) 
    Message = tostring(customDimensions.MessageTemplate),
    ["Action Name"] = tostring(customDimensions.ActionName),
    ["Severity Level"] = tostring(error_mapping[tostring(severityLevel)])
// Rename the columns to more friendy names
| project-rename 
    Started = min_timestamp,
    Finished = max_timestamp
// Get the ten most common errors
| order by Count
| limit 10

App Insights KQL also lets you do some nice graphs. This one shows you the number of logged messages each hour as a bar chart. A quick glance at this should show you if there has been a sudden surge in activity.

| extend application = customDimensions.Application
| where application == "My App Name (Production)"
| summarize count() by bin(timestamp, 1h) | render columnchart

No Comments