Azure Resource Graph, and PowerShell survival guide.

#azure #kql #powershell

Azure resource graph is very precious when you need to access Azure Data across several subscriptions. It’s a powerful tool but using it with PowerShell can be frustrating sometimes.
Let's see how you can manage KQL requests to Azure Resource Graph with PowerShell.

AZ Resource Graph Module

The AZ PowerShell module did not include. You will need to install

Install-Module -Name Az.ResourceGraph -Scope CurrentUser

It will install these cmdlets

Search-AzGraph
Get-AzResourceGraphQuery
New-AzResourceGraphQuery
Remove-AzResourceGraphQuery
Update-AzResourceGraphQuery

The last four cmdlets allow you to manage recorded queries, the first one is used to directly query Azure Graph (Resource Graph, Monitor, …) from PowerShell.

Error management in KQL

What happens if your KQL request returns an error, like a syntax error? It will be output as a KQL error and not a PowerShell exception. In this condition, it became impossible to use standard Catch/Try to capture the exception.
To avoid that, we need to create a custom exception. To create a custom exception in PowerShell you need to create an inherited class from the exception class.

class AzResourceGraphException : Exception {
    [string] $additionalData

    AzResourceGraphException($Message, $additionalData) : base($Message) {
        $this.additionalData = $additionalData
    }
}

To use it

$resourceGraphQuery = "Resource" 

Search-AzGraph -Query $resourceGraphQuery -ErrorVariable grapherror -ErrorAction SilentlyContinue 

if ($null -ne $grapherror.Length) {

    $errorJSON = $grapherror.ErrorDetails.Message | ConvertFrom-Json

    throw [AzResourceGraphException]::new($errorJSON.error.details.code, $errorJSON.error.details.message)

}

Building a request

If you have worked with KQL, you know that KQL requests can take several lines. Multiline queries are more clear, easy to read, and to edit when needed.
How can you do the same with PowerShell?
You can use a here-string to have a multi-line variable enclosed by @" "@

kqlQuery = @"
    Resources
    | join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project subscriptionName = name, subscriptionId) on subscriptionId
    | where type =~ 'Microsoft.Compute/virtualMachines'
    | project VMResourceId = id, subscriptionId, subscriptionName, resourceGroup, resourceName = name, networkInterfaces = (properties.networkProfile.networkInterfaces)
    | mv-expand networkInterfaces
    | project VMResourceId, subscriptionId, subscriptionName, resourceGroup, resourceName, networkInterfaceId = tostring(networkInterfaces.id)
    | join kind=leftouter(
        Resources
        | where type =~ 'Microsoft.Network/networkInterfaces'
        | project id, ipConfigurations = (properties.ipConfigurations)
        | mv-expand ipConfigurations
        | project id, publicIpAddressId = tostring(ipConfigurations.properties.publicIPAddress.id), privateIp = ipConfigurations.properties.privateIPAddress
        | join kind = leftouter (
            Resources
            | where type =~ 'Microsoft.Network/publicIPAddresses'
            | project publicIpId=id, ipAddress=tostring(properties.ipAddress)
        ) on $left.publicIpAddressId == $right.publicIpId
    ) on  $left.networkInterfaceId == $right.id
    | project VMResourceId, subscriptionId, subscriptionName, resourceGroup, resourceName, ipAddress, privateIp
    | order by subscriptionId, subscriptionName, resourceGroup, resourceName
"@

Limitations

Extracting data with KQL often mean joining multiple tables to extract needed data. In the previous example, 3 tables were joined.
But there is a limit, you can’t use more than 4 joins in a query. Using more joins will raise an error.

Also, by default the size of the dataset is limited, only the first 100 results are returned. If you want more, you will need to the –First parameter. It defines the maximum number of rows to return, but it’s limited to 1000. If the result contains more than 1000 rows you will need to use pagination.

Pagination

You can retrieve more than 1000 rows per query, but it is possible to retrieve the dataset by using pagination.
One of the techniques is to use the –SkipToken parameter. It is used to get the next page of the result.
The object returned by Search-AzGraph contains a SkipToken property. This token, a string, can be used in the next query to skip the result of the previous one.
To work, the result must contain an ID.

$kqlQuery = @"
Resources 
| join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project subscriptionName = name, subscriptionId) on subscriptionId 
| where type =~ 'Microsoft.Compute/virtualMachines' 
| project VMResourceId = id, subscriptionName, resourceGroup, name 
"@

$batchSize = 1000
$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
}

You can find the module doc here

Top comments (4)

Max B • Jun 7 '22

Since which PS version '+=' has become an equivalent for .Add()/AddRange() methods of List<T>?

Max B • Jun 7 '22

Ok, just checked, it seems it hasn't.
So what PowerShell does in this case - it takes the <List<T> collection, changes it to array (object[]), re-creates it with a new element, casts back to List<T>.
I'd say it's far from optimal.

Max B • Jun 7 '22

Alternative pagination example:

[List[Object]]$Results = @()

$SkipToken = $null
$Response = $null
$Count = 500

do {
  $Response = 
  if (-not $SkipToken) 
  { Search-AzGraph -Query $Query -First $Count }
  else 
  { Search-AzGraph -Query $Query -First $Count -SkipToken $SkipToken }

  $SkipToken = $Response.SkipToken

  $Results.AddRange($Response)
} while ($SkipToken)