PowerShell function to query Azure Data Explorer

The PowerShell function Invoke-AzOperationalInsightsQuery _{docs, source} allows me to pass arbitrary an KQL string to an App Insights resource.

Set-AzContext -Subscription "my-sub"
$workspaceName = "vl-loganalytics-workspace"
$workspaceRG = "vl-loganalytics"

$WorkspaceID = (Get-AzOperationalInsightsWorkspace -Name $workspaceName -ResourceGroupName $workspaceRG).CustomerID

$query = "print current_cluster_endpoint()"

$kqlQuery = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $query
$kqlQuery.Results

_{HT learningbydoing.cloud}

Does an equivalent method exist for querying an Azure Data Explorer cluster directly? No public function is listed in the Az.Kusto module as of version 2.1.0, but perhaps there is a community module or blog post documenting an ad-hoc method for this?

Solution

Referencing Kusto .NET client libraries from PowerShell, this is possible with the below helper code after downloading & unzipping the Microsoft.Azure.Kusto.Tools NuGet package

$clusterUrl = 'https://help.kusto.windows.net;Fed=True'
# DatabaseName may be null, empty, or incorrect
$databaseName = 'Samples'
$query = 'print current_cluster_endpoint()'

$packagesRoot = Resolve-Path "tools\net6.0"
[System.Reflection.Assembly]::LoadFrom("$packagesRoot\Kusto.Data.dll")

$kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)

$queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($kcsb)

$crp = New-Object Kusto.Data.Common.ClientRequestProperties
$crp.ClientRequestId = "MyPowershellScript.ExecuteQuery." + [Guid]::NewGuid().ToString()
$crp.SetOption([Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout, [TimeSpan]::FromSeconds(30))

$reader = $queryProvider.ExecuteQuery($query, $crp)
$dataTable = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader).Tables[0]
$dataView = New-Object System.Data.DataView($dataTable)

$dataView

Note that $databaseName does not need to correspond to an existing database to establish a connection. This can cause errors if you typo a database name; or it can be helpful if the command you wish to execute does not require a database context.