DEV Community

Cover image for Introduction to KQL and Basic Commands
Bala Madhusoodhanan
Bala Madhusoodhanan

Posted on

1 1 1 1 1

Introduction to KQL and Basic Commands

Intro:
Kusto Query Language (KQL) is a powerful query language used primarily to interact with Azure Data Explorer and Azure Monitor logs. It allows users to retrieve, filter, and analyze large datasets efficiently. KQL is designed to be easy to read and write, making it accessible for both technical and non-technical users.

Importance of KQL in Azure App Insights: KQL plays a crucial role in Azure Application Insights by enabling users to query and analyze telemetry data. This helps in monitoring application performance, diagnosing issues, and gaining insights into user behavior. With KQL, you can create custom queries to extract meaningful information from your application logs, making it an essential tool for maintaining and improving your applications.

The table listing some common entities/tables in Azure infrastructure

** Table/Entity ** ** Description ** ** How It Helps with Azure Systems **
** customEvents ** Stores custom events tracked in your application. Helps in tracking specific events and user interactions.
** requests ** Logs all incoming requests to your application. Useful for monitoring application performance and diagnosing issues.
** traces ** Contains trace logs for your application. Assists in debugging and understanding application behavior.
** dependencies ** Logs dependencies your application relies on, such as databases or APIs. Helps in identifying and troubleshooting dependency issues.
** exceptions ** Stores exception details from your application. Crucial for error tracking and resolution.
** availabilityResults ** Logs results of availability tests. Ensures your application is available and performing as expected.
** performanceCounters ** Contains performance metrics from your application. Monitors application performance and resource usage.
** pageViews ** Logs page views in your web application. Provides insights into user behavior and page performance.
** customMetrics ** Stores custom metrics defined by your application. Allows tracking of specific performance indicators.
** appEvents ** Logs application-specific events. Helps in understanding application-specific activities.
*appRequests * Contains details about application requests. Useful for analyzing request patterns and performance.
** appDependencies ** Logs dependencies specific to your application. Identifies and resolves issues with application dependencies.
** appExceptions ** Stores application-specific exception details. Essential for tracking and fixing application errors.
** appPerformanceCounters ** Contains performance counters specific to your application. Monitors and optimizes application performance.
** appPageViews ** Logs page views specific to your application. Analyzes user interactions and page load times.
** appCustomMetrics ** Stores custom metrics defined by your application. Tracks key performance indicators unique to your application.

These tables/entities are integral to monitoring, diagnosing, and optimizing applications running on Azure infrastructure. They provide detailed insights into various aspects of application performance, user interactions, and system dependencies, enabling you to maintain high availability and performance standards.

Basic Commands:

KQL offers a variety of commands to manipulate and analyze data. Here are some of the most commonly used commands:

where: Filters rows based on a condition. The query below is exploring all the customevents logged where timestamp is marked in the last 30 days.

customEvents
| where timestamp > ago(30d)
Enter fullscreen mode Exit fullscreen mode

Use Case: Identifying failed requests in application logs.

Image description

summarize: Aggregates data.The bin function is used to group the timestamp into daily interval

customEvents
| summarize count() by bin(timestamp, 1d)
Enter fullscreen mode Exit fullscreen mode

Use Case: Counting the number of requests per hour.

Image description

project: Selects specific columns.

customEvents
| project appName, customDimensions, name
Enter fullscreen mode Exit fullscreen mode

Use Case: Displaying only the timestamp, name, and duration of requests.

Image description

extend: Adds calculated columns.

let prm_month = dynamic(["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]);
customEvents
| where timestamp > ago(70d)
| summarize count() by bin(timestamp, 1d)
| extend monthofyear = monthofyear(bin(timestamp, 1d))
| extend monthofyear_name = prm_month[monthofyear-1]
Enter fullscreen mode Exit fullscreen mode

Use Case: Converting request duration from milliseconds to seconds.

Image description

Further Read:
Kusto Query Language

👋 While you are here

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (0)

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay