Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Last active May 11, 2023 23:44
Show Gist options
  • Save SQLvariant/101e3020d81adb3666026da76358ec51 to your computer and use it in GitHub Desktop.
Save SQLvariant/101e3020d81adb3666026da76358ec51 to your computer and use it in GitHub Desktop.
A PowerShell function to run a KQL query against an Azure Data Explorer cluster. If the Microsoft.Azure.Kusto.Tools NuGet package does not exist, this command will attempt to install the latest version of it.
function Invoke-KqlQuery
{ <#
.SYNOPSIS
This command runs a KQL Query against an Azure Data Explorer cluster.
.DESCRIPTION
This command runs a KQL Query against an Azure Data Explorer cluster using the Azure AD User
Authentication method, unless an access token is passed in with the -AccessToken parameter.
.PARAMETER ClusterUrl
Specify the full URL of the Azure Data Explorer cluster being queried.
.PARAMETER DatabaseName
Specify the Database withing the Azure Data Explorer cluster to be queried.
.PARAMETER Query
Specify the query to be run against the the Azure Data Explorer database.
.EXAMPLE
Invoke-KqlQuery
This will run a query against the StormEvent table using the default connection.
.EXAMPLE
Invoke-KqlQuery -ClusterUrl "https://help.kusto.windows.net;Fed=True" -DatabaseName "Samples" -Query "StormEvents | limit 5"
This will run a query against the StormEvent table using the connection information dpecified.
.EXAMPLE
$token = (Get-AzAccessToken -ResourceUrl https://help.kusto.windows.net).Token
Invoke-KqlQuery -ClusterUrl "https://help.kusto.windows.net" -DatabaseName "Samples" -Query "StormEvents | limit 5" -AccessToken $token
This will run a query against the StormEvent table using the connection information dpecified.
.EXAMPLE
$Cluster = 'https://help.kusto.windows.net'
$token = (Get-AzAccessToken -ResourceUrl $Cluster).Token
Invoke-KqlQuery -ClusterUrl $Cluster -DatabaseName "Samples" -Query "StormEvents | limit 5" -AccessToken $token
This will run a query against the StormEvent table using the connection information dpecified.
.EXAMPLE
$SynapseWorkspace = 'https://my-synapse-workspace.kusto.azuresynapse.net'
$DataPoolUri = 'https://MyDataPool.my-synapse-workspace.kusto.azuresynapse.net'
$token = (Get-AzAccessToken -ResourceUrl $SynapseWorkspace).Token
Invoke-KqlQuery -ClusterUrl $DataPoolUri -DatabaseName "Samples" -Query "StormEvents | limit 5" -AccessToken $token
When running the `Invoke-KqlQuery` function against a Data Pool in a Synapse Workspace you need to grab the token using the
URL of the Synapse Workspace itself, but query the Data Pool using the full URI of the endpoint.
#>
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param (
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[String]$ClusterUrl = "https://help.kusto.windows.net;Fed=True",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[String]$DatabaseName = "Samples",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[String]$Query = "StormEvents | limit 5",
[Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
[String]$AccessToken
)
begin {
function InstallAndLoadKustoData {
$Package = Get-Package Microsoft.Azure.Kusto.Tools
if($Package.Source){
$packagesRoot = Join-Path -Path (Split-Path $Package.Source) -ChildPath "\tools\net5.0\Kusto.Data.dll"
"Successfully loaded $packagesRoot"
}
else {
"Installing KustoData"
#$null = Register-PackageSource -Name nuget.org -Location http://www.nuget.org/api/v2 -Force -Trusted -ProviderName NuGet;
$install = Install-Package Microsoft.Azure.Kusto.Tools -ProviderName NuGet -Force;
$packagesRoot = Join-Path -Path $install.Payload.Directories[0].Location -ChildPath $install.Payload.Directories[0].Name -AdditionalChildPath "\tools\net5.0\Kusto.Data.dll";
}
Add-Type -LiteralPath $packagesRoot
}
}
process {
InstallAndLoadKustoData
if(!$AccessToken){
if($clusterUrl -notmatch ';Fed=True$'){$clusterUrl = "$clusterUrl;Fed=True"}
}
# Option A: using Azure AD User Authentication
$kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)
if($AccessToken){
$kcsb = $kcsb.WithAadUserTokenAuthentication($AccessToken)
}
$queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($kcsb)
Write-Host "Executing query: '$query' with connection string: '$($kcsb.ToString())'"
# Optional: set a client request ID and set a client request property (e.g. Server Timeout)
$crp = New-Object Kusto.Data.Common.ClientRequestProperties
$crp.ClientRequestId = "MyPowershellScript.ExecuteQuery." + [Guid]::NewGuid().ToString()
$crp.SetOption([Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout, [TimeSpan]::FromSeconds(30))
# Execute the query
$reader = $queryProvider.ExecuteQuery($query, $crp)
# Do something with the result datatable, for example: print it formatted as a table, sorted by the
# "StartTime" column, in descending order
$dataTable = [Kusto.Cloud.Platform.Data.ExtendedDataReader]::ToDataSet($reader).Tables[0]
$dataView = New-Object System.Data.DataView($dataTable)
if($Host.Name -eq 'Visual Studio Code Host'){
$dataView | Format-Table -AutoSize
}
else{
$dataView
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment