Build Dynamic Parameter List

PowerShell Help
1

I have a small script that will report Azure SQL Databases on 1 server. I’d like to be able to loop through a list of multiple subscriptions, multiple resource groups and list all the servers and databases. I’m assuming I need a nested loop, but unsure how to build the parameter list as shown below.

[pre]

$subscriptionID = ‘subID’

$resourceGroup = ‘rgname’

$serverName = ‘server’

set context

Set-AzContext -Subscription $subscriptionID

$params = @{

ServerName = $serverName;

ResourceGroupName = $resourceGroup;

}

get database list

Get-AzSqlDatabase @params |

Select-Object -Property (

‘ServerName’,

‘DatabaseName’,

‘Status’,

‘CreationDate’

)

[/pre]

2

I have the following loop to return all subscriptions and all resource groups tied to that subscription. The only problem is it’s returning all the subscriptions, but only the resource groups for one subscription.

[pre]

$subscriptions = Get-AzSubscription | Select-Object -ExpandProperty SubscriptionID

$subResults = ForEach ($sub in $subscriptions) {

$resourceGroup = Get-AzResourceGroup | Select-Object -ExpandProperty ResourceGroupName
foreach ($rg in $resourceGroup) {
Write-Host $sub, $rg
}
#Select-AzSubscription | select -property $sub.SubscriptionId

}

[/pre]

3

Try it this way:

$subscriptions = Get-AzSubscription | Select-Object -ExpandProperty SubscriptionID
$subResults = ForEach ($sub in $subscriptions) {
    Set-AzureRmContext -SubscriptionId $Sub
    $resourceGroup = Get-AzResourceGroup | Select-Object -ExpandProperty ResourceGroupName
    foreach ($rg in $resourceGroup) {
        [PSCustomObject]@{
            Subscription = $sub
            ResourceGroup = $rg
        }
    }
}
$subResults
4

It’s returning the same. So it will return the 3 subscriptions, but all of the resource groups of one subscription.

I think I need a way to change the context in which the script is running, i.e., change the subscription during the loop but i’m still trying to figure that out.

5

I’m learning as I’m writing here as well … changed the code above … I cannot try it because I don’t have access to more than one subscription.

6

Gotcha! I appreciate your help!

I realized you definitely need to set the context to pull resource groups. However, the ‘Get-AzResourceGroup’ cmdlet doesn’t not need a Subscription value to retrieve information. I’d still like to loop through the ‘Set-AzContext’ cmdlet to set the subscription in order to pull the resource groups.

I wrote this to actually test the loop to pull SQL Servers, which works, but not truly dynamically.

[pre]

#$resoureGroups = Get-AzResourceGroup | Select-Object -ExpandProperty ResourceGroupName

Set-AzContext -Subscription use2-sub-dev

$resourceGroups = @(“rg1”,“rg2”, “rg”)
$rgResults = foreach ($rg in $resourceGroups) {
$servers = Get-AzSqlServer -ResourceGroupName $rg | Select-Object -ExpandProperty ServerName
foreach ($server in $servers) {
[PSCustomObject]@{
ResourceGroup = $rg
Server = $server
}
}
}
$rgResults

[/pre]

I’m still trying to make this a more dynamic script. By dynamic, I mean pull all values without manually entering the resource groups.

 

7

Did you try the code I changed?

8

Yes, the first one you posted returned all 3 subscriptions, but merged the resource groups of only one subscription.

9

I changed the code after you tried it first. Did you try again?

10

I actually tried to do that after your first post. It should work, but it’s not. The 3 subscriptions are prd, dev, and qa. It’s only returning the resource groups under dev.

11

Sorry, that did work. The Set-AzureRMContext cmdlet is deprecated. I had to use Set-AzContext.

In order to retrieve the servers and database on those servers, do you recommend additional nested loops? The other cmdlets I need to use is one for the servers (Get-AzSqlServers) and then the databases (Get-AzSqlDatabase). The servers cmdlet requires a resource group name and the database cmdlet requires a resource group name and server name.

 

12

Ahh … ok … cool. :wink:

I’d try what’s ever necessary to achieve my goal.

I’d just recommend to indent your code to make it easier readable. That helps to prevent errors. You might read The PowerShell Best Practices and Style Guide.

13

I’d just recommend to indent your code to make it easier readable.

This times 1000.

The number of times i’ve fixed bugs in my code by going back and doing proper formatting I can’t even begin to count. In the beginning it seemed silly to care, but once you get over just a few lines its vital.

 

14

Yep, thx for you help! I’m sure I’ll be posting again to this forum trying to retrieve the results.

Ha, I do indent. It’s actually not pasting correctly. Not sure what I’m doing wrong.

15

What editor do you use to write your scripts?

16

Here’s what I have so far:

[pre]

$subscriptions = Get-AzSubscription | Select-Object -ExpandProperty SubscriptionID
$subResults = ForEach ($sub in $subscriptions) {
Set-AzContext -SubscriptionId $Sub
$resourceGroup = Get-AzResourceGroup | Select-Object -ExpandProperty ResourceGroupName
foreach ($rg in $resourceGroup) {
[PSCustomObject]@{
Subscription = $sub
ResourceGroup = $rg
}
}
}
$subResults

$d = $subResults.ResourceGroup

$rgResults = foreach ($rg in $d) {
$servers = Get-AzSqlServer -ResourceGroupName $rg | Select-Object -ExpandProperty ServerName
foreach ($server in $servers) {
[PSCustomObject]@{
ResourceGroup = $rg
Server = $server
}
}
}
$rgResults

[/pre]

However, I will receive the following output if there is no server associated with a resource group:

[pre]

Get-AzSqlServer : Resource group ‘use2-prd-cold-rg’ could not be found.
At line:3 char:16

  • $servers = Get-AzSqlServer -ResourceGroupName $rg | Select-Object ...

  •            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : CloseError: (:slight_smile: [Get-AzSqlServer], CloudException
  • FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Server.Cmdlet.GetAzureSqlServer
    ResourceGroup Server

use2-sqldw-rg dac-dw-qa

[/pre]

Is there a way I can ignore that message or return nothing?