Build Dynamic Parameter List

Welcome Forums General PowerShell Q&A Build Dynamic Parameter List

Viewing 15 reply threads
  • Author
    Posts
    • #218556
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

      
      $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’
      
      )
      
      
      • This topic was modified 1 month, 1 week ago by anelliaf38.
    • #218868
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

      
      $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
      
      }
      
      
    • #218871
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      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
    • #218886
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

    • #218892
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      … 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.

      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.

    • #218907
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

      
      #$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
      
      

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

       

    • #218910
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

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

      Did you try the code I changed?

    • #218913
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

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

    • #218916
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

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

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

    • #218919
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

    • #218937
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

       

    • #218940
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

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

      Ahh … ok … cool. 😉

      In order to retrieve the servers and database on those servers, do you recommend additional nested loops?

      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.

    • #218955
      Participant
      Topics: 24
      Replies: 172
      Points: 557
      Helping Hand
      Rank: Major Contributor

      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.

       

    • #218964
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      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.

    • #218967
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

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

      What editor do you use to write your scripts?

    • #218982
      Participant
      Topics: 20
      Replies: 41
      Points: 290
      Rank: Contributor

      Here’s what I have so far:

      
      $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
      
      

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

      
      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: (:) [Get-AzSqlServer], CloudException
      + FullyQualifiedErrorId : Microsoft.Azure.Commands.Sql.Server.Cmdlet.GetAzureSqlServer
      ResourceGroup          Server
      ————-          ——
      use2-sqldw-rg dac-dw-qa
      
      

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

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