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
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.
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]]$kqlResultwhile($true){if($skipResult-gt0){$graphResult=Search-AzGraph-Query$kqlQuery-first$batchSize-SkipToken$graphResult.SkipToken}else{$graphResult=Search-AzGraph-Query$kqlQuery-first$batchSize}$kqlResult+=$graphResult.dataif($graphResult.data.Count-lt$batchSize){break;}$skipResult+=$skipResult+$batchSize}
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.
Top comments (4)
Since which PS version
'+='has become an equivalent for.Add()/AddRange()methods ofList<T>?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 toList<T>.I'd say it's far from optimal.
Alternative pagination example:
Thanks @omiossec for this simple startup guidance - I always wanted to have a look into Resource Graph with PowerShell.