Azure Resource Graph, and PowerShell survival guide.
Tags: azure, kql, powershell
Published: 2022-05-12 18:39:40

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

```powershell
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. 

```powershell
class AzResourceGraphException : Exception {
    [string] $additionalData

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

To use it 

```powershell
$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 @" "@ 

```powershell
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.

```powershell
$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](https://docs.microsoft.com/en-us/powershell/module/az.resourcegraph/?view=azps-7.5.0&WT.mc_id=AZ-MVP-5003620)