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"
  });
traces
| 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) 
    by 
    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.

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

5 Comments

  • Turcas said

    I just could not depart your site prior to suggesting that I actually loved the usual info a person provide to your guests?
    Is gonna be back ceaselessly in order to inspect new posts

  • Serie said

    I enjoy what you guys are usually up too. This kind of clever work and exposure!
    Keep up the excellent works guys I've incorporated you guys to our blogroll.

  • Serie said

    Hey just wanted to give you a quick heads up.
    The text in your post seem to be running off the screen in Safari.

    I'm not sure if this is a formatting issue or something to do with internet
    browser compatibility but I figured I'd post to let you know.
    The design look great though! Hope you get the problem solved soon. Thanks

Add a Comment