Home » Export Azure Resources via Powershell to CSV

Export Azure Resources via Powershell to CSV

This post shows a Powershell script that connects to Azure and exports all resources from multiple subscriptions to a CSV file. It also shows how this script can be used inside of a scheduled task which creates the CSV file on a daily base.

Exporting all the resources can be achieved with the following commandlets:

Add-AzureRmAccount                                  # login to your azure account
Set-AzureRmContext -SubscriptionID $subscriptionId  # set/change the subscription
Get-AzureRmResource | Export-CSV "c:\temp\data.csv" # get the resources and export it to CSV file

This script just exports the data of one subscription and simply writes it to a csv file. If we want to have a reusable script which exports the data of all of my subscriptions, then we should extend it:

# settings
$defaultPath = "c:\Temp\azureresources.csv"
$csvDelimiter = ';'
 
# set azure account
[void] (Login-AzureRmAccount)
 
# receive all subscriptions
$subscriptions = Get-AzureRmSubscription
$subscriptions | ft SubscriptionId, @{Name="Name";Expression={if(!$_.SubscriptionName) { $_.Name; } else { $_.SubscriptionName } } }
 
# select azure subscriptions that you want to export
"Please enter subscription ids (comma separated, leave empty to use all subscriptions)"
$subscriptionIds = read-host
 
if([String]::IsNullOrWhiteSpace($subscriptionIds)) {
    $subscriptionIds = @($subscriptions | select -ExpandProperty SubscriptionId)
}
elseif($subscriptionIds.Contains(',')) {
    $subscriptionIds = $subscriptionIds.Split(',')
}
else {
    $subscriptionIds = @($subscriptionIds)
}
 
# configure csv output
"Enter destination path - leave it empty to use $defaultPath"
$path = read-host
if([String]::IsNullOrWhiteSpace($path)) {
    $path = $defaultPath
}
 
if (Test-Path $path) { 
    "File $path already exists. Delete? y/n [Default: y)"
    $remove = read-host
    if([String]::IsNullOrWhiteSpace($remove) -or $remove.ToLower().Equals('y')) {
        Remove-Item $path
    }
}
 
"Start exporting data..."
foreach($subscriptionId in $subscriptionIds) {
    # change azure subscription
    [void](Set-AzureRmContext -SubscriptionID $subscriptionId)
    # read subscription name as we want to see it in the exported csv
    $currentSubscription = ($subscriptions | Where { $_.SubscriptionId -eq $subscriptionId })
    $subscriptionName = $currentSubscription.SubscriptionName
    if([String]::IsNullOrEmpty($subscriptionName)) {
        $subscriptionName = $currentSubscription.Name
    }
     
    $subscriptionSelector = @{ Label="SubscriptionName"; Expression={$subscriptionName} }
    $tagSelector =  @{Name="Tags";Expression={ if($_.Tags -ne $null) { $x = $_.Tags.GetEnumerator() | %{ "{ `"" + $_.Name + "`" : `"" + $_.Value + "`" }, " }; ("{ " + ([string]$x).TrimEnd(", ") + " }") } }}
    #get resources from azure subscription
    $export = Get-AzureRmResource | select *, $subscriptionSelector, $tagSelector -ExcludeProperty "Tags"
    $export | Export-CSV $path -Delimiter $csvDelimiter -Append -Force -NoTypeInformation
    "Exported " + $subscriptionId + " - " + $subscriptionName
}
 
"Export done!"

This script reads resources from one or more Azure subscriptions and exports them to a CSV file. It’s easy to reuse it and it doesn’t require to enter a filepath, subscription id or anything else.

It’s just important the the latest version of Azure Powershell (https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/) is installed and works.

Run the script in a scheduler

If we want to run the script e.g. on a daily base via timerjob or task scheduler, then we just need to modify it a bit:

# settings
$path = "c:\Temp\azureresources" + [DateTime]::Now.ToString("yyyyMMdd") + ".csv"
$csvDelimiter = ';'
$azureProfilePath = "c:\Temp\azureprofile.json"

# set azure account
Select-AzureRmProfile -Path $azureProfilePath

# receive all subscriptions
$subscriptions = Get-AzureRmSubscription
$subscriptions | ft SubscriptionId, @{Name="Name";Expression={if(!$_.SubscriptionName) { $_.Name; } else { $_.SubscriptionName } } }

$subscriptionIds = @($subscriptions | select -ExpandProperty SubscriptionId)

if (Test-Path $path) { 
    "File $path already exists. Delete"
    Remove-Item $path 
}

"Start exporting data..."
foreach($subscriptionId in $subscriptionIds) {
    # change azure subscription
    [void](Set-AzureRmContext -SubscriptionID $subscriptionId)
    # read subscription name as we want to see it in the exported csv
    $currentSubscription = ($subscriptions | Where { $_.SubscriptionId -eq $subscriptionId })
    $subscriptionName = $currentSubscription.SubscriptionName
    if([String]::IsNullOrEmpty($subscriptionName)) {
        $subscriptionName = $currentSubscription.Name
    }
    
    $subscriptionSelector = @{ Label="SubscriptionName"; Expression={$subscriptionName} }
    $tagSelector =  @{Name="Tags";Expression={ if($_.Tags -ne $null) { $x = $_.Tags.GetEnumerator() | %{ "{ `"" + $_.Name + "`" : `"" + $_.Value + "`" }, " }; ("{ " + ([string]$x).TrimEnd(", ") + " }") } }}
    #get resources from azure subscription
    $export = Get-AzureRmResource | select *, $subscriptionSelector, $tagSelector -ExcludeProperty "Tags"
    $export | Export-CSV $path -Delimiter $csvDelimiter -Append -Force -NoTypeInformation
    "Exported " + $subscriptionId + " - " + $subscriptionName
}

"Export done!"

This generates a file – e.g. azureresources_20160428.csv – in the folder c:\Temp. Now we need to ensure that Powershell knows how to connect to Azure. To achieve that, we store our profile on the filesystem and load it from there. This can be done by executing the following commandlets:

Add-AzureRmAccount
Save-AzureRmProfile -Path "c:\temp\azureprofile.json"

After checking if the file exists, the following (line 7 of the previous script) should load the azure profile:

Select-AzureRmProfile -Path $azureProfilePath

Update June 8, 2016

I modified the script a bit so that it exports tags in the correct way. I also added -NoTypeInformation to the csv export and replaced the default delimiter for CSV with ‘;’ so that it’s automatically in the right format to open it with excel.

Update Mar 24, 2018

I updated the script so that it works with the current version and exports tags in the correct way. There was also an issue with the subscription name, so I modified that too.

7 comments

  1. Matthew F says:

    Great script, thank you very much! I modified it slightly in order to export all my Subscriptions and their related assigned roles.

    1. No need to set context
    2. Replace the first $export line:
    $export = Get-AzureRmRoleAssignment -IncludeClassicAdministrators -Scope /subscriptions/$subscriptionId | select *, $subscriptionSelector

  2. Mike Tong says:

    Thanks very useful! For some reason, the subscription name was not returned from this script. I have to modify the script to get it to work. It appears the property name has changed to “Name” from “SubscriptionName”. That worked for me.

  3. Quaid says:

    This is awesome.
    I Had the same issue as Mike Tong, Not sure how to edit the subscriptionname to name as when I try it it doesnt have any effect.
    Also, can we change the delimiters from / to ; for the resources properties? they come out as /

    As a side, would we be able to add public ip if a resource has a public ip? | Get-AzureRmPublicIpAddress
    Not sure how to add.
    This would be very useful for security folks.

  4. Andy Omphson says:

    This does not give Tags anymore and I like Quaid’s suggestion regarding Get-AzureRmPublicIpAddress.

Leave a Reply

Your email address will not be published. Required fields are marked *