locked
Select-AzureRmSubscription : Please provide a valid tenant or a valid subscription. RRS feed

  • Question

  • Hi Guys,

    I wanted to export list of all azure SQL databases and some of their attributes in all of my subscriptions to csv file and im trying below powershell which is failing with below error.

    I have access to all subscriptions and simple select-azureRMSubscription works without any issues.

    Select-AzureRmSubscription : Please provide a valid tenant or a valid subscription.
    At line:6 char:1
    + Select-AzureRmSubscription -SubscriptionId "$subscription"
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : CloseError: (:) [Set-AzureRmContext], ArgumentException
        + FullyQualifiedErrorId : Microsoft.Azure.Commands.Profile.SetAzureRMContextCommand

    SCRIPT:

    Login-AzureRmAccount
    $subscriptions=Get-AzureRmSubscription | select-object SubscriptionId
    foreach($subscription in $subscriptions)
    {
    $subscription
    Select-AzureRmSubscription -SubscriptionId "$subscription"
    $resourcegroupnames= Get-AzureRmSqlServer | select-object ResourceGroupName
        foreach($resourcegroup in $resourcegroupnames)
        {
        $sqlservers=Get-AzureRmSqlServer | select-object ServerName
        foreach($sqlserver in $sqlservers)
          { 
        Get-AzureRMSqlDatabase -ServerName "$sqlserver" -ResourceGroupName "$resourcegroup"  | select-object resourcegroupname,servername,databasename,capacity,creationdate | export-csv -Append -Path "H:\software\azuresqlnew.csv" -Encoding ascii -NoTypeInformation
          }
        }
    }
    
    

    Thanks

    Tuesday, July 10, 2018 1:07 PM

Answers

  • This would be the better method for doing this.

    Login-AzureRmAccount
    
    Get-AzureRmSubscription | 
        ForEach-Object{
            Select-AzureRmSubscription -SubscriptionId $_.ID
            Get-AzureRmSqlServer | 
                ForEach-Object { 
                    Get-AzureRMSqlDatabase -ServerName $_.ServerName -ResourceGroupName $_.ResourceGroupName
                }
        } |
        select-object resourcegroupname,servername,databasename,capacity,creationdate | 
            export-csv H:\software\azuresqlnew.csv -NoTypeInformation
    


    \_(ツ)_/

    • Marked as answer by udhayan Wednesday, July 11, 2018 9:52 AM
    Tuesday, July 10, 2018 3:50 PM

All replies

  • I don't have access to Azure so I cannot reproduce or check but I suspect your variable $subscription, which you don't need to enclose in quotes btw., does not fit to the expected type of the parameter -SubscriptionId. You should re-review the help for the cmdlet Select-AzureRmSubscription to learn how to use and to know what type of parameter is expected. And of course you should take a close look into your variable what it conains.

    Edit: I suspect it expects a string but you provide an object. Tryto replace you line 2 with this:

    $subscriptions=Get-AzureRmSubscription | 
        Select-Object -ExpandProperty SubscriptionId


    Best regards,

    (79,108,97,102|%{[char]$_})-join''




    • Edited by BOfH-666 Tuesday, July 10, 2018 2:54 PM
    • Proposed as answer by jrv Tuesday, July 10, 2018 3:42 PM
    Tuesday, July 10, 2018 2:47 PM
  • This would be the better method for doing this.

    Login-AzureRmAccount
    
    Get-AzureRmSubscription | 
        ForEach-Object{
            Select-AzureRmSubscription -SubscriptionId $_.ID
            Get-AzureRmSqlServer | 
                ForEach-Object { 
                    Get-AzureRMSqlDatabase -ServerName $_.ServerName -ResourceGroupName $_.ResourceGroupName
                }
        } |
        select-object resourcegroupname,servername,databasename,capacity,creationdate | 
            export-csv H:\software\azuresqlnew.csv -NoTypeInformation
    


    \_(ツ)_/

    • Marked as answer by udhayan Wednesday, July 11, 2018 9:52 AM
    Tuesday, July 10, 2018 3:50 PM
  • What if instead of using the SubscriptionID, you use the tenantId.

    Login-AzureRmAccount
    $subscriptions= Get-AzureRmSubscription 
    foreach($subscription in $subscriptions){
    Write-Host -ForegroundColor Green -BackgroundColor Black "Working with TenantId $($subscription.TenantId)"
    Select-AzureRmSubscription -TenantId $($subscription.TenantId)
    $resourcegroupnames= Get-AzureRmSqlServer | select-object ResourceGroupName
        foreach($resourcegroup in $resourcegroupnames)
        {
        $sqlservers=Get-AzureRmSqlServer | select-object ServerName
        foreach($sqlserver in $sqlservers)
          { 
        Get-AzureRMSqlDatabase -ServerName "$sqlserver" -ResourceGroupName "$resourcegroup"  | select-object resourcegroupname,servername,databasename,capacity,creationdate | export-csv -Append -Path "H:\software\azuresqlnew.csv" -Encoding ascii -NoTypeInformation
          }
        }
    }

    The rest of the script is untouched, the code is tested and working.


    • Edited by j0rt3g4 Tuesday, July 10, 2018 4:28 PM
    Tuesday, July 10, 2018 4:28 PM
  • This Works like a charm. Thanks much 

    I still dont understand whats wrong in my logic though :-(

    Wednesday, July 11, 2018 9:52 AM
  • Thanks much for your time. Your script Using tenanID fixed the error that i had posted but script is not traversing through all the available subscriptions , In fact everytime it goes back to my visual studio professional subscription alone.

    Wednesday, July 11, 2018 9:55 AM
  • Thanks much for your time. Your script Using tenanID fixed the error that i had posted but script is not traversing through all the available subscriptions , In fact everytime it goes back to my visual studio professional subscription alone.

    Toom many issues to comment.  Start by spending some time learning basic PowerShell and you will begin to see how you ran off the tracks.


    \_(ツ)_/

    Wednesday, July 11, 2018 10:12 AM
  • And what is the error or output you're getting?

    Jose

    Tuesday, July 17, 2018 3:46 PM
  • Hello,

    It is not giving any error. As i said earlier, its not looping through all the subscriptions.

    It just enters my visual studio professional subscription, since i dont have any azure SQL DBs there it does not log anything and again connects to my visual studio professional subscription and goes on for sometime and script ends without any errors.

    Thanks

    Wednesday, July 18, 2018 9:24 AM