Search-AzGraph for All Subscriptions

A while ago I wrote about Search-AzGraph here. Since then, I have used it quite a bit and learned that depending on how you connect you may not query all subscriptions

Connecting Specifying a Subscription

To illustrate this behavior, let's say you have a service principal with access to 3 subscriptions, so to connect you would use the cmdlet Connect-AzAccount specifying the first subscription context

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
    Subscription     = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
}
Connect-AzAccount @paramAz

Then you can query the resource count in all subscriptions with this Kusto query

$query = "
Resources
| summarize count() by subscriptionId
"

As expected, the cmdlet Search-AzGraph returned the resource count in all 3 subscriptions

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

However, if you change the context to the second subscription using Set-AzContext, you will see that now Search-AzGraph only shows the resource count for the second subscription

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338

Moreover, if you change the context to the third subscription, you will see that Search-AzGraph shows the resource count for the second and third subscriptions

Set-AzContext -Subscription 'zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

The reason is that Search-AzGraph uses the cumulative context when a subscription context is set in the connection, Get-Context shows the two subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

A workaround is to pass to Search-AzGraph a list of all subscriptions using Get-AzSubscription

$subIds = (Get-AzSubscription).Id

Search-AzGraph -Query $query -Subscription $subIds

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Connecting Without Specifying a Subscription

In this scenario, you would use the cmdlet Connect-AzAccount without specifying a subscription

$paramAz = @{
    ServicePrincipal = $true
    TenantId         = 'tttttttt-tttt-tttt-tttt-tttttttttttt'
    Credential       = Get-Credential
}
Connect-AzAccount @paramAz

Next, you change the context to the second subscription using Set-AzContext, you will see that Search-AzGraph shows the resource count for all subscriptions, unlike our previous case

Set-AzContext -Subscription 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy'

Search-AzGraph -Query $query 

subscriptionId                       count_
--------------                       ------
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx    577
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy    338
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz    111

Because a subscription context is not set in the connection, Get-Context will show all the subscriptions Search-AzGraph will use

(Get-AzContext).Account.ExtendedProperties.Subscriptions

xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,
yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy,
zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz

Conclusion

If you specify a subscription context in the connection, Search-AzGraph will not look in all subscriptions when you change the context later. You can override this behavior by passing a subscription list. On the other hand, if you do not specify the subscription context in the connection, Search-AzGraph will look in all subscriptions regardless of changing the context.

Nesting Invoke-SqlCmd in the Pipeline

If you need to collect data from an list of SQL Servers stored in a table, you would write a PowerShell script that uses Invoke-SqlCmd to query the list from the table and then loop through each instance to execute another query. However, PowerShell 5 does not support nesting Invoke-SqlCmd in the pipeline. The workaround is to use an intermediate variable or use PowerShell 7. 

Scenario 

For example you have a table with the SQL instance names stored in SQL1

create table instances(name varchar(255))
insert instances values ('SQL1')
insert instances values ('SQL2')
insert instances values ('SQL3')

To query the list, you use Invoke-SqlCmd in PowerShell 5

$query = "select name from instances"
Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query

To loop through each instance, you pipe the results and use Invoke-SqlCmd to execute another query to let's say get the version

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

The script will fail with this error

# Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be 
# called from outside the overrides of the BeginProcessing, ProcessRecord,     
# and EndProcessing methods, and they can only be called from within the same thread.
# Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer 
# Support Services.

Workaround 

Use a variable $instances to hold the results of the first Invoke-SqlCmd and then pipe it

$instances = Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query 
$instances |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

or use PowerShell 7

Invoke-Sqlcmd -ServerInstance 'SQL1' -Query $query |
ForEach-Object {
    $query2 = "select instance = serverproperty('InstanceName'), version = serverproperty('ProductVersion')"
    Invoke-Sqlcmd -ServerInstance $_.name -Query $query2
}

# instance     version
# --------     -------
# SQL1         13.0.5865.1
# SQL2         13.0.5865.1
# SQL3         13.0.5492.2