Learning KQL

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 lot of people who come 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 enabled TDS endpoint feature for Dataverse environment.

If you are not a Data Engineer, one of the popular options to analyse 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.

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.

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.

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 getschema. This is just like sp_help in SQL. It helps us understand the table schema.

Three other operators that are also useful are make-series, summarize and render. make-series and summarize 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.

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 analyse 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, analyse 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.

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!

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('/').slice(2,10).join('/')}` ); void 0;

javascript:prompt('Log Analytics URL',`https://ade.loganalytics.io/${location.hash.split('/').slice(2,10).join('/')}` ); 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 analyse 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