Pass output from one loop to the other

Welcome Forums General PowerShell Q&A Pass output from one loop to the other

Viewing 2 reply threads
  • Author
    Posts
    • #220050
      Participant
      Topics: 21
      Replies: 41
      Points: 301
      Rank: Contributor

      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
      
      
      • This topic was modified 1 month, 2 weeks ago by anelliaf38.
    • #221217
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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.

    • #221811
      Participant
      Topics: 21
      Replies: 41
      Points: 301
      Rank: Contributor

      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:

      
      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
      
      

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

       

Viewing 2 reply threads
  • You must be logged in to reply to this topic.