Using the Dynamic Data Type in KQL

Intro

Lately I’ve been working more with telemetry, Application Insights, the excellent Power BI report and samples from Microsoft and writing a little KQL. Back in the days when you actually knew where your SQL server was I used to write a lot of ad-hoc SQL for analysis or troubleshooting so getting into KQL has been nice.

KQL sort of looks like SQL…but with the SELECT and FROM clauses switched round…and use project instead of SELECT…and use join kind=leftouter instead of LEFT JOIN…and DON’T WRITE EVERYTHING IN CAPS.

At least WHERE is still the same (but stop shouting and use where instead of WHERE).

If you want to get into writing your own KQL I’d recommend Kusto Explorer and this post got me started with creating the connection to Application Insights.

Variables

Like with SQL you can create your own variables for use later in your script. So you probably use SET or DECLARE then? No, use let like in JavaScript.

let tenantId = "c042cd54-77a9-4b96-aa0b-fc43bd0161c7";

Then use that variable in your script.

let tenantId = "c042cd54-77a9-4b96-aa0b-fc43bd0161c7";
traces |
    where customDimensions.tenantId == tenantId

Notice:

• the semicolon between the statements, as in JavaScript (or not…digression)
• no blank line between the statements, otherwise they are interpreted as separate scripts and your where line will break
• the double equals – remember, like JavaScript, not T-SQL
• the table (traces) is piped to the where, like a scripting language

Dynamic Data Type

KQL supports a bunch of data types with built-in functions to manipulate them and convert between them but it also has a dynamic data type which is a generic container to hold any of the other types – like a variant in AL.

I’ve found this useful for creating a dictionary of values that I want to lookup later in the script. I’ve been querying the signals for scheduled tasks and job queue entries. These signals include the object ids that were executed, but not their names.

We can use the dynamic function to create a dynamic value and parse some JSON to create a dictionary of object id and object name. Later in the script we can retrieve the object name with square brackets.

let objectNames = dynamic({"50100": "Improbability Drive Calcs.", "50101": "Restart Flux Capacitor"});
traces
| where
...
| project
...
objectName = objectNames[tostring(customDimensions.alJobQueueObjectId)]

Notice:

• You can name the column in the projected output with columnName =
• Use tostring() to cast the dynamic value in customDimensions to a string

DataTable

Creating a dictionary was enough for me because I’m mostly dealing with codeunits and I don’t care if there are pages or tables with the same id. If you do care then you could create a table instead with the datatable operator. Like this:

let objectNames = datatable(objectType: string, objectId: int, objectName: string)[
    "Table", 18, "Customer",
    "Codeunit", 80, "Sales-Post"
];
objectNames
| where objectType == "Table" and objectId == 18
| project objectName

Resources

• Loads more about KQL (far more than I’m pretending to know) here: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query
• Telemetry Event IDs: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/telemetry-event-ids
• Business Central Usage Power BI Report on AppSource: https://appsource.microsoft.com/en-US/product/power-bi/microsoftdynsmb.bc_telemetry_pbi_app?tab=Overview

Check james’s original post https://jpearson.blog/2023/08/22/using-the-dynamic-data-type-in-kql/ on jpearson.blog which was published 2023-08-22 17:21:00