Accelerate Azure PowerShell with Kusto Query Language

Kusto Query Language (KQL) is a powerful query language that is used to query large datasets in Azure. KQL is used in Azure Monitor, Azure Data Explorer, and Azure Resource Graph. PowerShell is a popular scripting language for automation tasks in Azure. In this article, we’ll explore how to use KQL in PowerShell scripts and the benefits of doing so.

You can learn more about Resource Graph Exlorer and KQL in one of my older blog post: Exploring Azure Resources with Azure Graph Explorer

Benefits of Using Kusto Query in PowerShell

Using Kusto query in PowerShell provides several benefits:

1. Greater Flexibility: Kusto query language is very powerful and flexible, allowing us to perform complex queries and analysis of Azure resources. By using Kusto query in PowerShell, we can easily automate various tasks related to Azure resources.

2. Improved Performance: Kusto query is a highly optimized language, designed to handle large volumes of data efficiently. By using Kusto query in PowerShell, we can take advantage of this optimization and perform resource-intensive tasks more quickly and efficiently.

Well written Kusto Query will return results almost immediately, while the PowerShell script for the same tasks might take several minutes to complete. This is because Kusto is designed to work with large amounts of data and can execute queries in parallel across multiple nodes, while PowerShell is more suited to general-purpose scripting tasks and may not be as optimized for working with large data sets.

However, there may be cases where using PowerShell to retrieve data can be beneficial. For example, if you need to perform additional processing or filtering on the data after retrieving it, it may be faster to retrieve the data using PowerShell and then process it locally.

1. Better Control: By using Kusto query in PowerShell, we have more control over the data we retrieve from Azure. We can filter, sort, and group the data based on our specific requirements, and only retrieve the data we need.

Prerequisites

Before we dive into using KQL in PowerShell, we need to make sure we have the necessary prerequisites installed:

1. Install the Azure PowerShell module if you haven’t already done so. You can install it using the following command:

Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force

1. Install the Az.ResourceGraph module if you haven’t already done so. You can install it using the following command:

Install-Module -Name Az.ResourceGraph -Scope CurrentUser -Repository PSGallery -Force

1. Optionally you might also need Az.OperationalInsights module:

Install-Module -Name Az.OperationalInsights -Scope CurrentUser -Repository PSGallery

1. Connect to your Azure subscription using Connect-AzAccount and Set-AzContext. You’ll need to provide your Azure credentials when prompted.

Using KQL in PowerShell

Now that we have the prerequisites installed, let’s explore how to use KQL in PowerShell scripts. We’ll use the Search-AzGraph cmdlet to run KQL queries and save the results to PowerShell objects.

Example 1: Querying Azure Resource Graph

The first example demonstrates how to use KQL to query Azure Resource Graph and save the results to a PowerShell object. This query returns the name, id, type, and resourceGroup properties of all resources in the selected scope.

1
2
3
4
5
6
7
8
9
10

# Define KQL query
$query = @"
resources | project name, id, type, resourceGroup
"@

# Run KQL query and save results to PowerShell object
$results = Search-AzGraph -Query $query

# Display results
$results

In this example, we define the KQL query using a here-string (@” and “@). We then use the Search-AzGraph cmdlet to run the query and save the results to the $results variable. Finally, we display the results using $results.

Example 2: Querying Azure Monitor

The second example demonstrates how to use KQL to query Azure Monitor and save the results to a PowerShell object. This query returns the Computer, TimeGenerated, and SourceSystem properties of the last 10 log entries from the Event table.

1
2
3
4
5
6
7
8
9
10
11
12

# Define KQL query
$query = @"
Event
| top 10
| project Computer, TimeGenerated, SourceSystem
"@

# Run KQL query and save results to PowerShell object
$results = Search-AzGraph -Query $query -WorkspaceId <workspace-id>

# Display results
$results

Note that we need to provide the ID of the Azure Monitor workspace using the -WorkspaceId parameter. Finally, we display the results using $results.

Example 3: Using Azure Data Explorer cluster

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Install the Az.OperationalInsights module if not already installed
Install-Module -Name Az.OperationalInsights -Scope CurrentUser -Repository PSGallery

# Connect to the Azure Data Explorer cluster
$clusterName = "mykustocluster"
$databaseName = "mydatabase"
$clusterUri = "https://$clusterName.kusto.windows.net"
$tenantId = "mytenantid"
$clientAppId = "myclientappid"
$clientSecret = "myclientsecret"
$creds = Get-AzAccessToken -ResourceUrl $clusterUri
$workspaceId = (Get-AzOperationalInsightsWorkspace).WorkspaceId
$connectionString = "Data Source=$clusterUri;AAD Federated Security=True;Initial Catalog=$databaseName;User ID=$($creds.UserId);Password=$($creds.AccessToken);"
$connectionContext = New-AzOperationalInsightsConnection -WorkspaceId $workspaceId -ConnectionAccessToken $creds.AccessToken

# Query the cluster and retrieve the results as an object
$query = @"
MyTable | take 10
"@
$results = Invoke-AzOperationalInsightsQuery -Connection $connectionContext -Query $query

# Display the results in the console
$results | Format-Table

Working with data

Once we have the Kusto query result as a PowerShell object, we can easily perform various actions on it. For example, we can filter the results based on certain conditions, sort the results, group them by specific fields, and much more.

Here’s an example of filtering the Kusto query results based on a specific resource type and returning only the resource name and resource group:

1
2
3
4
5
6
7

$resourceType = "Microsoft.Compute/virtualMachines"

# Filter resources by specific resource type
$result = $result | Where-Object { $_.type -eq $resourceType }

# Select only the required properties
$result = $result | Select-Object name, resourceGroup

Vukasin Terzic