Last active
May 11, 2023 23:44
Revisions
-
SQLvariant revised this gist
Sep 8, 2022 . 1 changed file with 6 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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) if($Host.Name -eq 'Visual Studio Code Host'){ $dataView | Format-Table -AutoSize } else{ $dataView } } } -
SQLvariant revised this gist
Sep 8, 2022 . 1 changed file with 9 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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')] -
SQLvariant revised this gist
Sep 7, 2022 . 1 changed file with 7 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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')] -
SQLvariant revised this gist
Aug 25, 2022 . 1 changed file with 18 additions and 3 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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, 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", [Parameter(Mandatory = $false, ValueFromPipelineByPropertyName = $true)] [String]$AccessToken ) begin { @@ -61,9 +71,14 @@ function Invoke-KqlQuery 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) -
SQLvariant revised this gist
Aug 25, 2022 . 1 changed file with 2 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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){ $packagesRoot = Join-Path -Path (Split-Path $Package.Source) -ChildPath "\tools\net5.0\Kusto.Data.dll" "Successfully loaded $packagesRoot" } else { "Installing KustoData" -
SQLvariant revised this gist
Aug 19, 2022 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -60,7 +60,7 @@ function Invoke-KqlQuery process { InstallAndLoadKustoData if($clusterUrl -notmatch ';Fed=True$'){$clusterUrl = "$clusterUrl;Fed=True"} # Option A: using Azure AD User Authentication $kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName) -
SQLvariant revised this gist
Aug 19, 2022 . 1 changed file with 2 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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) -
SQLvariant revised this gist
Aug 11, 2022 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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" This will run a query against the StormEvent table using the connection information dpecified. -
SQLvariant created this gist
May 16, 2022 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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 } }