KustoPowershell.psm1
|
<#
README ======================== Before invoking any functions in this module, "Import-Kusto-Package" needs to be invoked first to load required Kusto assembly. Successful loading then imports needed binary namespaces and functions. -------------- What is returned This module sets at the script scope: var "importkusto" to 1 if Import-Kusto-Package ends with exception; var "kustoquery" to 1 if Invoke-Kusto-Query errors out. The user can leverage in the calling script a if conditional to judge if a function here returns error: if ($null -ne VAR) {function errors out!} Invoke-Kusto-Query normally returns a "datatable" assembly class containing the query result. Then foreach can be used to iterate through all data rows. See below example for illustration. -------------- Below is a short snippet demostrating a simple invoke to perform a query: Import-Kusto-Package if ($null -ne $importkusto) { Write-Error "Kusto Package failed to be imported. Exiting" exit 1 } $query = "Snapshot | where name == `"myvm1`"" $dataview = Invoke-Kusto-Query -query $query -cluster mykustocluster.local -databaseName vmdb if ($null -ne $dataview) { foreach ($row in $dataview) { $vmname = $row["name"] $rg = $row["resource_group"] $lastSeen = $row["TIMESTAMP"] } } #> function Import-Kusto-Package { # Check if Nuget is available as an installed package provider, add it if not: Write-Host "Checking for Nuget package Microsoft.Azure.Kusto.Tools..." $isNugetAvailable = Get-PackageSource -ProviderName Nuget -Location "https://www.nuget.org/api/v2" if (!$isNugetAvailable) { Write-Host "Nuget V2 not found as a Package Source. Adding it now..." Register-PackageSource -Name NuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet } # Check if Nuget is installed as an installed package, add it if not: $isKqlDllInstalled = Get-Package -Name "Microsoft.Azure.Kusto.Tools" if (!$isKqlDllInstalled) { Write-Host "Microsoft.Azure.Kusto.Tools package not installed. Installing now..." # This requires Admin. Check if Admin, kill if not: $isAdmin = [bool](([System.Security.Principal.WindowsIdentity]::GetCurrent()).groups -match "S-1-5-32-544") if ($isAdmin -eq $false) { Write-Host "Installation of a new Nuget package requires Admin. Please re-run this script as an administrator" $Script:importkusto = 1 Return } else { Write-Host "Package Microsoft.Azure.Kusto.Tools not yet installed! Installing..." $isKqlDllInstalled = Install-Package "Microsoft.Azure.Kusto.Tools" -Source "https://www.nuget.org/api/v2" -Force } } else { $version = $isKqlDllInstalled.Version Write-Host "Package Microsoft.Azure.Kusto.Tools (Version $version) already installed! Proceeding..." } $packagePath = $isKqlDllInstalled.Source $packageRoot = $packagePath.Substring(0, ($packagePath).lastIndexOf('\')) Get-ChildItem $packagesRoot\* | Unblock-File [System.Reflection.Assembly]::LoadFrom("$packageRoot\tools\net472\Kusto.Data.dll") } Export-ModuleMember -Function Import-Kusto-Package Function Invoke-Kusto-Query { [cmdletbinding()] param( [parameter(Mandatory=$True,ValueFromPipeline=$true)] $query, $cluster, $databaseName ) Process { $clusterUrl = "https://"+$cluster+";Fed=True" $kcsb = New-Object Kusto.Data.KustoConnectionStringBuilder ($clusterUrl, $databaseName) $queryProvider = [Kusto.Data.Net.Client.KustoClientFactory]::CreateCslQueryProvider($kcsb) $crp = New-Object Kusto.Data.Common.ClientRequestProperties $crp.ClientRequestId = "MyPowershellScript.ExecuteQuery." + [Guid]::NewGuid().ToString() $crp.SetOption([Kusto.Data.Common.ClientRequestProperties]::OptionServerTimeout, [TimeSpan]::FromSeconds(60)) # Execute the query $reader = $queryProvider.ExecuteQuery($query, $crp) if (-not $?) { $Script:kustoquery = 1 Return } # 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] $result = New-Object System.Data.DataView($dataTable) } end { Return $result } } Export-ModuleMember -Function Invoke-Kusto-Query |