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.

    .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.
    
    #>
    [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"
    )

    begin {
        function InstallAndLoadKustoData {

            $Package = Get-Package Microsoft.Azure.Kusto.Tools
            if($Package.Source){"Already There"
            $packagesRoot = Join-Path -Path (Split-Path $Package.Source) -ChildPath "\tools\net5.0\Kusto.Data.dll"
            }
            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

    #   Option A: using Azure AD User Authentication
    $kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)

    $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)
    $dataView | Sort StartTime -Descending | Format-Table -AutoSize
    }
}