Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Last active May 11, 2023 23:44

Revisions

  1. SQLvariant revised this gist Sep 8, 2022. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -111,6 +111,11 @@ function Invoke-KqlQuery
    # "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
    if($Host.Name -eq 'Visual Studio Code Host'){
    $dataView | Format-Table -AutoSize
    }
    else{
    $dataView
    }
    }
    }
  2. SQLvariant revised this gist Sep 8, 2022. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -38,6 +38,15 @@ function Invoke-KqlQuery
    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')]
  3. SQLvariant revised this gist Sep 7, 2022. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -31,6 +31,13 @@ function Invoke-KqlQuery
    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.
    #>
    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
  4. SQLvariant revised this gist Aug 25, 2022. 1 changed file with 18 additions and 3 deletions.
    21 changes: 18 additions & 3 deletions Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,8 @@ function Invoke-KqlQuery
    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.
    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.
    @@ -24,6 +25,12 @@ function Invoke-KqlQuery
    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.
    #>
    [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
    @@ -35,7 +42,10 @@ function Invoke-KqlQuery
    [String]$DatabaseName = "Samples",

    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [String]$Query = "StormEvents | limit 5"
    [String]$Query = "StormEvents | limit 5",

    [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)]
    [String]$AccessToken
    )

    begin {
    @@ -61,9 +71,14 @@ function Invoke-KqlQuery
    process {
    InstallAndLoadKustoData

    if($clusterUrl -notmatch ';Fed=True$'){$clusterUrl = "$clusterUrl;Fed=True"}
    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)

  5. SQLvariant revised this gist Aug 25, 2022. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -42,8 +42,9 @@ function Invoke-KqlQuery
    function InstallAndLoadKustoData {

    $Package = Get-Package Microsoft.Azure.Kusto.Tools
    if($Package.Source){"Already There"
    if($Package.Source){
    $packagesRoot = Join-Path -Path (Split-Path $Package.Source) -ChildPath "\tools\net5.0\Kusto.Data.dll"
    "Successfully loaded $packagesRoot"
    }
    else {
    "Installing KustoData"
  6. SQLvariant revised this gist Aug 19, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -60,7 +60,7 @@ function Invoke-KqlQuery
    process {
    InstallAndLoadKustoData

    if (!$ClusterUrl.EndsWith(';Fed=True')) {$ClusterUrl = $ClusterUrl + ';Fed=True'}
    if($clusterUrl -notmatch ';Fed=True$'){$clusterUrl = "$clusterUrl;Fed=True"}
    # Option A: using Azure AD User Authentication
    $kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)

  7. SQLvariant revised this gist Aug 19, 2022. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -59,7 +59,8 @@ function Invoke-KqlQuery

    process {
    InstallAndLoadKustoData


    if (!$ClusterUrl.EndsWith(';Fed=True')) {$ClusterUrl = $ClusterUrl + ';Fed=True'}
    # Option A: using Azure AD User Authentication
    $kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)

  8. SQLvariant revised this gist Aug 11, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ function 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"
    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.
  9. SQLvariant created this gist May 16, 2022.
    83 changes: 83 additions & 0 deletions Invoke-KqlQuery.ps1
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,83 @@
    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
    }
    }