Cross-Cluster and Cross-Service querying with Azure Data Explorer and Azure Log Analytics in Azure Data Studio

This blog post covers examples of cross-cluster and cross-service querying, including handy syntax, code snippets and notebooks that you can use in Azure Data Studio.

As some of you may already know, Kusto (KQL) extension is available in Azure Data Studio, which allows you to explore Azure Data Explorer (ADX) more natively. ADX also supports cross-cluster and cross-service queries between ADX, Azure AppInsights and Azure Log Analytics. This cross- service query preview feature is documented in Query data in Azure Monitor using Azure Data Explorer.

Cross-Cluster querying

The syntax for cross-cluster querying is:

cluster('<cluster-name>;.<region-name>').database('<database-name>')

This query example below joins the StormEvents data in the Help ADX cluster with the StatePopulation data in my ADX cluster, hence “Cross-Cluster” querying. You can run this both as a KQL query and as a Kusto notebook in Azure Data Studio.

let stormSummarized = StormEvents 
| summarize EventCount = count() by State = toupper(State);
cluster('myADXcluster.westus').database('mykustodb').StatePopulation
| project State = toupper(State), Population
| join (stormSummarized) on State
| project State, Population, EventCount
| sort by Population 
| take 3

Here’s a notebook to illustrate this experience. jubilant-data-wizards/Demo-NativeKusto-CrossClusterQuery.ipynb at main · MsSQLGirl/jubilant-data-wizards (github.com)

Tip: in Azure Data Studio, you can hit Ctrl + O, and paste the raw path of the above Github notebook path, i.e https://raw.githubusercontent.com/MsSQLGirl/jubilant-data-wizards/main/Simple Demo/KQL Notebooks/Demo-NativeKusto-CrossClusterQuery.ipynb, to launch the notebook directly on the client.

Cross-Service querying

With Kusto (KQL) extension in Azure Data Studio, you can also leverage the ADX cross-service query feature to connect to your Log Analytics workspace and do interesting data mesh (joins) between the two services.

The syntax to reference Log Analytics workspace is:

cluster(https://ade.loganalytics.io/subscriptions/<subscription-id>/resourcegroups/<resource-group-name>/providers/microsoft.operationalinsights/workspaces/<workspace-name>').database('<workspace-name>)

Here is an example of my query to connect to my Log Analytics workspace via the ADX Help cluster connection. You can run this both as a KQL query and as a Kusto notebook in Azure Data Studio.

cluster('https://ade.loganalytics.io/subscriptions/88a1234b-6cc7-1234-a015-a123bc123456/resourcegroups/myresourcegroup/providers/microsoft.operationalinsights/workspaces/mssqlgirlla').database('mssqlgirlla').AzureDiagnostics | take 10
| summarize count() by action_name_s, succeeded_s
| take 10

Here’s a notebook to illustrate this experience. jubilant-data-wizards/Demo-NativeKusto-CrossServiceQuery.ipynb at main · MsSQLGirl/jubilant-data-wizards (github.com)

Tip: in Azure Data Studio, you can hit Ctrl + O, and paste the raw path of the above Github notebook path, i.e https://raw.githubusercontent.com/MsSQLGirl/jubilant-data-wizards/main/Simple Demo/KQL Notebooks/Demo-NativeKusto-CrossServiceQuery.ipynb, to launch the notebook directly on the client.

Wrap Up

That’s it! Give it a go and let us know what you think!

My presentation on Kusto Query Language (KQL) in Azure Data Studio at SQL Saturday Singapore #1019 this year covered this topic. To see the slide deck and other session notes, please head to: jubilant-data-wizards/Notebooks Presentations/SQL Saturday 1019 SSingapore at main · MsSQLGirl/jubilant-data-wizards (github.com). You can also watch the recording on YouTube.

Happy Holidays!