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.

Invoke-KqlQuery.ps1

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
        }
    }
}