Pass output from one loop to the other

PowerShell Help
1

The output for $rgResults returns a few rows of Resource Groups and their respective servers. I’d like to pass that information into the next ‘foreach’ loop, but I’m receiving an error.

Here’s the code:

$rgResults = foreach ($rg in $rgs) {

$servers = Get-AzSqlServer -ResourceGroupName $rg -ErrorAction SilentlyContinue | Select-Object -ExpandProperty ServerName

foreach ($server in $servers) {
[PSCustomObject]@{
ResourceGroup = $rg
Server = $server

} # End object creation

} # end server loop

} # end resource group loop

$dbs = $rgResults

$dbResults = @()
foreach($db in $dbs) {
$dbs = Get-AzSqlDatabase -ServerName $dbs.Server -ResourceGroupName $dbs.ResourceGroup
$obj = New-Object -TypeName psobject
Add-Member -InputObject $obj -MemberType NoteProperty -Name ResourceGroup -Value $db.ResourceGroupName
Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $db.ServerName
Add-Member -InputObject $obj -MemberType NoteProperty -Name Database -Value $db.DatabaseName
Add-Member -InputObject $obj -MemberType NoteProperty -Name CreationDate -Value $db.CreationDate
$dbResults += $obj
} # EndForEach

$dbResults | Format-Table -AutoSize

Error:
Get-AzSqlDatabase : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'ServerName'. Specified method is not
supported.
At line:3 char:50
+ $dbs = Get-AzSqlDatabase -ServerName $dbs.Server -Resourc ...
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [Get-AzSqlDatabase], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgument,Microsoft.Azure.Commands.Sql.Database.Cmdlet.GetAzureSqlDatabase
2

Where is the rest of this post? Didn’t I already comment on this one? It stands to reason if you change

$dbResults = @()
foreach($db in $dbs) {
$dbs = Get-AzSqlDatabase -ServerName $dbs.Server -ResourceGroupName $dbs.ResourceGroup
$obj = New-Object -TypeName psobject
Add-Member -InputObject $obj -MemberType NoteProperty -Name ResourceGroup -Value $db.ResourceGroupName
Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $db.ServerName
Add-Member -InputObject $obj -MemberType NoteProperty -Name Database -Value $db.DatabaseName
Add-Member -InputObject $obj -MemberType NoteProperty -Name CreationDate -Value $db.CreationDate
$dbResults += $obj
} # EndForEach

to

$dbResults = @()
foreach($db in $dbs) {
$database = Get-AzSqlDatabase -ServerName $db.Server -ResourceGroupName $db.ResourceGroup
$obj = New-Object -TypeName psobject
Add-Member -InputObject $obj -MemberType NoteProperty -Name ResourceGroup -Value $database.ResourceGroupName
Add-Member -InputObject $obj -MemberType NoteProperty -Name ServerName -Value $database.ServerName
Add-Member -InputObject $obj -MemberType NoteProperty -Name Database -Value $database.DatabaseName
Add-Member -InputObject $obj -MemberType NoteProperty -Name CreationDate -Value $database.CreationDate
$dbResults += $obj
} # EndForEach

This should help. Let us know.

3

I think this was an older thread. I’ll continue with what I have now. What I’m trying to do is loop through multiple Azure subscriptions, retrieve each resource group in those subscriptions, then under each resource group look for a Azure SQL Server, and finally list all databases associated with those servers.

The script is partly successful in returning Subscriptions and Resource Groups. However, once it reaches the server loop, it only lists servers in the QA subscription and ignores the servers in PRD and DEV subscriptions. I think the context gets dropped for PRD and DEV and stays with QA.

Somehow I need to get the script to set the context at each subscription, loop through resource groups, servers and databases for one subscription and then loop through the next.

Here’s the script I have so far:

[pre]

function Get-SQLDatabases {
[cmdletbinding()]
param (
#[string] $name = “Azure”
)
}

$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

} # End object creation

} # End rg loop

} # End subResults loop

$rgs = $subResults.ResourceGroup

$server =
foreach ($r in $rgs) {
$sn = Get-AzSqlServer -ResourceGroupName $r -ErrorAction SilentlyContinue |
Select-Object -ExpandProperty ServerName
[PSCustomObject]@{
ResourceGroupName = $r
ServerName = $sn
}
}

$dbs =
foreach ($s in $server){
$db = Get-AzSqlDatabase -ServerName $s.ServerName -ResourceGroupName $s.ResourceGroupName -ErrorAction SilentlyContinue
[PSCustomObject]@{
ResourceGroupName = $db.ResourceGroupName
ServerName = $db.ServerName
DatabaseName = $db.DatabaseName
CreationDate = $db.CreationDate
}
}

$dbs | Format-Table -AutoSize

[/pre]

Any help would be appreciated. Also, unsure why my scripts is being left aligned and not formatted.