Learning KQL – Microsoft Dynamics CRM Community

FetchXML used to be the only language for querying Dynamics CRM Dataverse. When WebAPI endpoint was introduced, OData style querying also became an additional option. When SQL4CDS was released it provided a way to query Dataverse using T-SQL which is so familiar to a lot of people who came from OnPremise CRM days. SQL4CDS did some transpiling magic to convert T-SQL to FetchXML and/or WebAPI calls. That is why update operations are possible through SQL4CDS. Of course you can use T-SQL along with SQL Server Management Studio, because Microsoft TDS endpoint feature for an enabled Dataverse environment.

If you are not a Data Engineer, one of the popular options to analyze and interpret the data is to use Power BI, or just export to Microsoft Excel and do some pivot tables. There was no need to learn any other querying language other than FetchXML and T-SQL. More projects now require a mix of Azure and Power Platform, so KQL is something everyone has to learn, or at least be aware of.

Before we delve into the need for KQL, let’s briefly look as what KQL is. KQL is Kusto Query Language developed by Microsoft. If you are doing any queries in Azure Log Analytics or Application Insights, KQL is the language you use.

KQL is just like SQL

The quickest way to get learn KQL is to use the Kusto Explorer. You can download this tool from https://aka.ms/ke. After you download Kusto Explorer you can create a new connection to https://help.kusto.windows.net which is the free learning cluster provided by Microsoft with sample data. Kusto Explorer is an amazing tool. It has Intellisense.

Add Connection
Help Cluster

You also need to create a folder called Kusto in your user profile. If you don’t create this folder, you will get too many Azure AD login prompts. This workaround is required only for the current release 1.0.3.1167. Hopefully this bug would disappear in the next release.

Now, we are ready to write our KQL query. One easy way to learn KQL is to use explain to transpile SQL to KQL. It translates your SQL query into KQL query.

Transpiling SQL to KQL

In KQL we use pipe symbol like PowerShell to filter the data and hand the data over to the next statement in the pipeline. We can use multiple where statements and gradually filter down our data.

The first useful operator in KQL is getschem. This is just like sp_help in SQL. It helps us understand the table schema.

getschem

Three other operators that are also useful are make-series, sum and render. make-series and sum are ways to aggregate data and render is a way to display chart based on the data. I really love this chart feature, because there are so many choices. I also find that it makes it easy to understand the data, than looking at a boring table.

How do you learn KQL interactively? One way is to use Azure Data Explorer.

Azure Data Explorer

You can create free Azure Data Explorer cluster without any Azure subscription or credit card. You can use the samples in Get started to learn and play around with KQL, just using Microsoft provided sample data.

Once you know a bit of KQL, it is so much easy to query and analyze data in Log Analytics workspace or in App Insights. If you are exporting Dataverse Telemetry to AppInsights, or exporting Logic Apps execution details/tracked properties to Log Analytics, knowing KQL is very much needed to troubleshoot any bugs, analyze performance issues, or even do some predictions based on historical data.

You don’t need to head to Azure portal to query your App Insights Data. You can do this easily from Kusto Explorer.

AppInsights Connection

Example below shows which Dataverse tables are being used the most based on AppInsights data. The result is rendered using the treemap chart, which is one of the chart types available OOB. How amazing is that!

Quering AppInsights using Kusto Explorer

In order to connect Kusto Explorer to AppInsights you need use this format below.

https://ade.applicationinsights.io/subscriptions/%5Bsubscription-id%5D/resourcegroups/%5Bresource-group-name%5D/providers/microsoft.insights/components/%5Bai-app-name%5D

You can also connect Kusto Explorer to Log Analytics Workspace by using this format below.

https://ade.loganalytics.io/subscriptions/%5Bsubscription-id%5D/resourcegroups/%5Bresource-group-name%5D/providers/microsoft.operationalinsights/workspaces/%5Bworkspace-name%5D

Rather than remembering this format, you can also use the bookmarklets below.

javascript:prompt('App Insights URL',`https://ade.applicationinsights.io/${location.hash.split("https://community.dynamics.com/").slice(2,10).join("https://community.dynamics.com/")}` ); void 0;
javascript:prompt('Log Analytics URL',`https://ade.loganalytics.io/${location.hash.split("https://community.dynamics.com/").slice(2,10).join("https://community.dynamics.com/")}` ); void 0;

Navigate to your AppInsights or Log Analytics resource and then execute the bookmarklet. The URL shown by the bookmarklet is what you will use to connect in Kusto Explorer. This one feature has helped my daily flow so much, as I no longer need to head to portal to do my query and all the queries I need are in my local machine.

Over the past so many weeks I have been playing around with KQL and ADX. I really love what I see here and how useful there tools are to analyze data. I highly recommend that you learn some basic KQL querying as well. Happy learning!

References and further reading:

  1. https://docs.microsoft.com/en-us/azure/data-explorer/start-for-free
  2. https://github.com/rod-trent/MustLearnKQL (Highly recommended)
  3. https://www.packtpub.com/product/scalable-data-analytics-with-azure-data-explorer/9781801078542 (Highly recommended. Great book)
  4. https://github.com/Azure/azure-kusto-microhack
  5. https://docs.microsoft.com/en-us/power-platform/admin/set-up-export-application-insights
  6. https://docs.microsoft.com/en-us/azure/data-explorer/query-monitor-data#add-a-log-analyticsapplication-insights-workspace-to-azure-data-explorer-client-tools

Our Process

01


Strategy

Always remember in the jungle there’s a lot of them in there, after you overcome them, you will make it to paradise.

02


Design

You see the hedges, how I got it shaped up? It’s important to shape up your hedges, it’s like getting a haircut, stay fresh.

03


Success

We don’t see them, we will never see them. To be successful you’ve got to work hard, to make history, simple, you’ve got to make it.

04


Grow

Look at the sunset. Surround yourself with angels, positive energy, beautiful people, beautiful souls, clean heart, angel.

.

Leave a Comment