Power of KQL for Business Central Telemetry (Part 2)

Welcome back to our series on the Power of KQL for Business Central Telemetry!

Before continuing make sure you have read Part 1:

Power of KQL for Business Central Telemetry (Part 1) – Stefan’s BC Blog (ssosic.com)

In this post, we dive into Part 3 and Part 4 of our journey.

We’ll start by exploring the extend statement, which allows you to add new columns to your dataset, enabling deeper insights and more customized data analysis. Then, we’ll move on to the render statement, where you’ll learn how to create various charts to visualize your telemetry data effectively.

Get ready to enhance your KQL skills and transform your telemetry data into actionable insights with powerful visualizations!

Part 3 of KQL query – extend – add new columns

The extend statement in Kusto Query Language (KQL) is used to create new columns in your dataset by computing values based on existing columns. This is particularly useful for adding derived metrics or transforming data to suit your analysis needs.

Basic Syntax

The basic syntax for the extend statement is:

| extend NewColumnName = <expression>

You can use various expressions to define the new column’s values, such as arithmetic operations, string manipulations, or functions.

Examples

1. Creating a New Column with Arithmetic Operations To create a new column that calculates the duration in seconds from a duration column in milliseconds:traces | extend DurationInSeconds = duration / 1000
2. Combining String Columns To create a new column that combines two string columns:traces | extend FullName = strcat(firstName, " ", lastName)
3. Using Conditional Logic To create a new column based on a condition:traces | extend Severity = case(severityLevel == 3, "Error", severityLevel == 2, "Warning", "Info")

Practical Use in Business Central

Some of useful columns which you can use for your telemetry

| extend extensionVersion = tostring (customDimensions.extensionVersion)

| extend executionTime = totimespan (customDimensions.executionTime) / 1ms

| extend renderTimeInMS = totalTimeInMS – serverExecutionTimeInMS

More complex statements with where, extend, and project

| where not (tostring (customDimensions.extensionVersion) contains ‘1.12.123.1234’)

| extend version = split(extensionVersion, '.')

| extend

    baseVersion = tostring(version[0])

    , versionMaj = toint(version[1])

    , versionMin = toint(version[2])

| extend appVersion = versionMaj + versionMin

| project

    versionMajMin = strcat(versionMaj, '.', versionMin)

    , versionA = strcat(tenantId, ': (', baseVersion, ')')

Tips for Using extend Statement

• Clarity: Use descriptive names for your new columns to make your queries easier to understand.
• Efficiency: Ensure that the expressions used in extend are optimized for performance, especially when dealing with large datasets.

Part 4 of KQL query – render – render chart

The render statement in Kusto Query Language (KQL) is used to visualize query results in various chart formats. This is particularly useful for creating dashboards and reports to monitor telemetry data in Business Central.

Basic Syntax

The basic syntax for the render statement is:

| render <chart_type>

You can specify different chart types such as columnchart, areachart, barchart, piechart, and more.

Column Chart

To visualize the count of events over time as a line chart:

| render columnchart

    with (

    legend=hidden,

    xtitle="Version",

    ytitle="RT0005 Count",

    title="Long running queries by version")

Area Chart

To visualize the count of events over time as an area chart:

| render areachart

    with (

    legend=hidden,

    accumulate=true,

    kind=stacked,

    xtitle="Time",

    ytitle="Total time long-running events (seconds).",

    title="Long-running events by extension.")

Bar Chart

To display the distribution of event types as a bar chart:

| render barchart

    with (

    xcolumn = functionName,

    xtitle="Average (ms)",

    title='Top 10 execution times of functions.',

    ycolumns=sumExecutionTime,

    ytitle="Total Time (ms)",

    legend=hidden)

Pie Chart

To show the proportion of different severity levels as a pie chart:

| render piechart

    with (

    legend=hidden,

    xtitle="Version",

    ytitle="RT0005 Count",

    title="Long running queries by version")

Tips for Using render Statement

• Choose the Right Chart: Select a chart type that best represents your data and makes it easy to interpret.
• Simplify: Avoid cluttering your charts with too much information. Focus on key metrics.