Kusto Query Language (KQL) is a powerful tool for analyzing telemetry data in Business Central. With KQL, you can efficiently filter, aggregate, and visualize data to gain deep insights into system performance and user behavior. Whether you’re tracking long-running AL methods, monitoring system health, or analyzing user interactions, KQL provides the flexibility and precision needed to make data-driven decisions.

We will split process of writing KQL into four parts:

  • Part 1 – where – filter the data
  • Part 2 – project – limit the number of columns
  • Part 3 – extends – add new columns
  • Part 4 – render – render chart

In this blog post we will cover Part 1 and Part 2.

Table of contents

Part 1 of KQL query – where – filter the data

The where statement in Kusto Query Language (KQL) is used to filter rows in a dataset based on specified conditions. It’s similar to the WHERE clause in SQL. Here’s a detailed look at how you can use the where statement in KQL, especially for telemetry data in Business Central:

Basic Syntax

The basic syntax for the where statement is:

| where <condition>

You can use various operators to define your conditions, such as ==, !=, >, <, >=, <=, and logical operators like and, or, and not.

Examples

  1. Filtering by Timestamp To filter telemetry data to only include entries from the last 7 days:traces | where timestamp > ago(7d)
  2. Filtering by Specific Value To filter data where a specific column matches a certain value:traces | where customDimensions.eventId == 'RT0018'
  3. Combining Multiple Conditions You can combine multiple conditions using logical operators:traces | where timestamp > ago(7d) and customDimensions.eventId == 'RT0018'
  4. Using in Operator To filter rows where a column’s value is in a specified list:traces | where customDimensions.eventId in ('RT0018', 'RT0019', 'RT0020')

Practical Use in Business Central

Filtering by signals:

| where customDimensions.eventId == 'RT0018'

More about signals, you can find in previous blog post regarding telemetry: Telemetry cost control in Business Central – Stefan’s BC Blog (ssosic.com)

Filtering by environment type, whether it’s production or sandbox:

| where tostring(customDimensions.environmentType) == "Production“

Filtering by checking if AL Stack Trace contains something:

| where customDimensions.alStackTrace contains XZY

XYZ – could be some Object Id for example

Filtering for tasks which are not executed in the background:

| where tostring (customDimensions.clientType) != "Background“

Filtering by Azure Tenant ID:

| where not (tostring (customDimensions.aadTenantId) contains “aadTenatID")

Filtering by Extension Version:

| where (tostring (customDimensions.extensionVersion) contains “1.2.3.4")

Filtering by AL Object Type:

| where tostring(customDimensions.alObjectType) != ‘Codeunit'

Tips for Using where Statement

  • Performance: Place the where statement as early as possible in your query to reduce the amount of data processed.
  • Clarity: Use meaningful names for your columns and variables to make your queries easier to understand.
  • Testing: Test your conditions incrementally to ensure they filter data as expected.

Part 2 of KQL query – project – limit the number of columns

The project statement in Kusto Query Language (KQL) is used to select specific columns from a dataset and optionally rename them. This is particularly useful when you want to focus on certain aspects of your telemetry data in Business Central.

Basic Syntax

The basic syntax for the project statement is:

| project <column1>, <column2>, <column3>

You can also rename columns using the following syntax:

| project NewColumnName = OldColumnName

Examples

  1. Selecting Specific Columns To select only the timestamp, message, and customDimensions columns:traces | project timestamp, message, customDimensions
  2. Renaming Columns To rename the customDimensions column to EventDetails:traces | project timestamp, message, EventDetails = customDimensions
  3. Combining Selection and Renaming You can combine both selection and renaming in a single project statement:traces | project EventTime = timestamp, LogMessage = message, EventDetails = customDimensions

Practical Use in Business Central

| project timestamp 
// in which environment/company did it happen 
, aadTenantId = customDimensions.aadTenantId 
, environmentName = customDimensions.environmentName 
, environmentType = customDimensions.environmentType 
, companyName = customDimensions.companyName 
// in which extension/app 
, extensionId = customDimensions.extensionId 
, extensionName = customDimensions.extensionName 
, extensionVersion = customDimensions.extensionVersion 
, extensionPublisher = customDimensions.extensionPublisher 
// in which object 
, alObjectId = customDimensions.alObjectId 
, alObjectName = customDimensions.alObjectName 
, alObjectType = customDimensions.alObjectType

Tips for Using project Statement

  • Consistency: Maintain consistent naming conventions across your queries for better readability and maintenance.
  • Clarity: Use meaningful names for your columns to make your queries easier to understand.
  • Efficiency: Select only the columns you need to reduce the amount of data processed and improve query performance.

In next blog post we will cover Part 3 and 4, where we will cover extends statement and proceed to rendering different kinds of charts for your dashboards. 🚀

Categorized in:

Telemetry