Skip to content

Kusto Query Language (KQL) for Azure Resource Graph

Azure Graph is a powerful tool provided by Microsoft to query data across all your Azure resources. It uses the Kusto Query Language (KQL), a read-only language similar to SQL, designed to query vast amounts of data in Azure services.

Important

Only a subset of KQL is supported in Azure Resource Graph. For more information, see the Azure Resource Graph Supported KQL Language Elements.

What is KQL?

KQL stands for Kusto Query Language. It's a request to process data and return results. The syntax is easy to read and author, making it ideal for data exploration and ad-hoc data mining tasks.

Using KQL with Azure Graph

Azure Graph allows you to use KQL to create complex queries that fetch information from your Azure resources. You can filter, sort, aggregate, and join data from different resources using KQL.

Here's an example of how you might use KQL to query Azure Graph:

Resources
| where type =~ 'microsoft.web/sites'
| project name, location, resourceGroup

This query retrieves all Azure Web Apps (websites) and projects their name, location, and resourceGroup.

Key Characteristics of KQL

  1. Case Sensitivity: Unlike SQL, KQL is case-sensitive. So 'Name' and 'name' would be considered different identifiers.
  2. Schema-Free: Kusto (Azure Data Explorer) doesn't require a fixed schema, allowing storage of diverse types of data.
  3. Extensibility: While KQL has a wide array of functions, you can also create custom functions as per your needs.

Common Operators in KQL

  • | : This operator creates a pipeline where the output of one command becomes the input of another.
  • where : Filters rows based on specified conditions.
  • summarize : Groups rows and calculates aggregate expressions.
  • join : Combines rows from two tables based on a common column.
  • project : Selects specific columns from the input.
  • extend : Adds new columns to the input.
  • order by : Sorts rows based on specified columns.

KQL Query Examples

1. List all Azure resources in a subscription

Resources

2. List all Azure resources in a resource group

Resources
| where resourceGroup == 'myResourceGroup'

3. List all Azure resources of a specific type

Resources
| where type =~ 'Microsoft.Compute/virtualMachines'

Pagination in KQL

KQL supports pagination using the limit and offset operators. You can use these operators to control the number of rows returned and skip a certain number of rows.

Resources
| limit 10
| offset 5

If you exceed payload limits, you can paginate Azure Resource Graph query results with powershell:

$kqlQuery = "policyResources | where type =~'Microsoft.Authorization/PolicySetDefinitions' or type =~'Microsoft.Authorization/PolicyDefinitions' | project definitionId = tolower(id), category = tostring(properties.metadata.category), definitionType = iff(type =~ 'Microsoft.Authorization/PolicysetDefinitions', 'initiative', 'policy'),PolicyDefinition=properties"

$batchSize = 5
$skipResult = 0

[System.Collections.Generic.List[string]]$kqlResult

while ($true) {

  if ($skipResult -gt 0) {
    $graphResult = Search-AzGraph -Query $kqlQuery -First $batchSize -SkipToken $graphResult.SkipToken
  }
  else {
    $graphResult = Search-AzGraph -Query $kqlQuery -First $batchSize
  }

  $kqlResult += $graphResult.data

  if ($graphResult.data.Count -lt $batchSize) {
    break;
  }
  $skipResult += $skipResult + $batchSize
}

Best Practices for Writing KQL Queries

  1. Use project to Select Columns: Only select the columns you need to reduce the amount of data returned.
  2. Use where to Filter Rows: Apply filters to reduce the number of rows processed.
  3. Use summarize to Aggregate Data: Aggregate data to reduce the number of rows returned.
  4. Use join to Combine Data: Combine data from different tables using the join operator.
  5. Use order by to Sort Data: Sort data based on specific columns to make it easier to read.

Limitations of KQL

  1. No DDL Operations: KQL doesn't support Data Definition Language (DDL) operations like creating tables or indexes.
  2. No DML Operations: KQL doesn't support Data Manipulation Language (DML) operations like inserting, updating, or deleting data.
  3. Limited Data Types: KQL has a limited set of data types compared to SQL.
  4. No Transactions: KQL doesn't support transactions, so you can't group multiple operations into a single transaction.

Conclusion

KQL is a potent tool for querying large datasets in Azure. Its SQL-like syntax makes it accessible for anyone familiar with SQL, and its rich set of features makes it a flexible solution for a variety of data processing needs. Practice writing KQL queries to uncover valuable insights from your Azure resources!

References