Using the .NET Client Libraries from PowerShell

The Azure Data Explorer .NET client libraries can be used by PowerShell scripts through PowerShell's built-in integration with arbitrary (non-PowerShell) .NET libraries.

Getting the .NET Client Libraries for scripting with PowerShell

To start working with the Azure Data Explorer .NET client libraries using PowerShell:

  1. Download the Microsoft.Azure.Kusto.Tools NuGet package.
  2. Extract the contents of the 'tools' directory in the package (using 7-zip, for example).
  3. Call [System.Reflection.Assembly]::LoadFrom("path") from powershell, to load the required library.
    • The "path" parameter to the command should indicate the location of the extracted files.
  4. Once all dependent .NET assemblies are loaded, create a Kusto connection string, instantiate a query provider or an admin provider, and run the queries or commands (as shown in the examples below).

For detailed information see the Azure Data Explorer client libraries.

Examples

Initialization

#  Part 1 of 3
#  ------------
#  Packages location - This is an example to the location where you extract the Microsoft.Azure.Kusto.Tools package.
#  Please make sure you load the types from a local directory and not from a remote share.
$packagesRoot = "C:\Microsoft.Azure.Kusto.Tools\Tools"

#  Part 2 of 3
#  ------------
#  Loading the Kusto.Client library and its dependencies
dir $packagesRoot\* | Unblock-File
[System.Reflection.Assembly]::LoadFrom("$packagesRoot\Kusto.Data.dll")

#  Part 3 of 3
#  ------------
#  Defining the connection to your cluster / database
$clusterUrl = "https://help.kusto.windows.net;Fed=True"
$databaseName = "Samples"

#   Option A: using AAD User Authentication
$kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName)
 
#   Option B: using AAD application Authentication
#     $applicationId = "application ID goes here"
#     $applicationKey = "application key goes here"
#     $authority = "authority goes here"
#     $kcsb = $kcsb.WithAadApplicationKeyAuthentication($applicationId, $applicationKey, $authority)

Example: Running an admin command

$adminProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslAdminProvider($kcsb)
$command = [Kusto.Data.Common.CslCommandGenerator]::GenerateDiagnosticsShowCommand()
Write-Host "Executing command: '$command' with connection string: '$($kcsb.ToString())'"
$reader = $adminProvider.ExecuteControlCommand($command)
$reader.Read() # this reads a single row/record. If you have multiple ones returned, you can read in a loop 
$isHealthy = $Reader.GetBoolean(0)
Write-Host "IsHealthy = $isHealthy"

And the output is:

IsHealthy = True

Example: Running a query

$queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($kcsb)
$query = "StormEvents | limit 5"
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

And the output is:

StartTime EndTime EpisodeId EventId State EventType InjuriesDirect InjuriesIndirect DeathsDirect DeathsIndirect
2007-12-30 16:00:00 2007-12-30 16:05:00 11749 64588 GEORGIA Thunderstorm Wind 0 0 0 0
2007-12-20 07:50:00 2007-12-20 07:53:00 12554 68796 MISSISSIPPI Thunderstorm Wind 0 0 0 0
2007-09-29 08:11:00 2007-09-29 08:11:00 11091 61032 ATLANTIC SOUTH Waterspout 0 0 0 0
2007-09-20 21:57:00 2007-09-20 22:05:00 11078 60913 FLORIDA Tornado 0 0 0 0
2007-09-18 20:00:00 2007-09-19 18:00:00 11074 60904 FLORIDA Heavy Rain 0 0 0 0