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

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Okay let's go

Community matters

Top comments (0)

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay