PowerShell: How can I list/document all the items in my azure subscription?

The following script will create an Excel file with each Resource Group as a tab.

First, I created a password file to reference from the script (no passwords in clear text please)

"xxxx" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "C:\Temp\Password.txt" 

Here is the function - it will create seperate CSV files per resource group and then merge them intoa a single Excel file at the end.

BTW, you may want to change the hard coded directories, username and filenames as required..... enjoy

# create a function to merge the csv files we have created

Function Merge-CSVFiles 

{ 

                Param( 

                                $CSVPath = "C:\csv\", ## Soruce CSV Folder 

                                $XLOutput="c:\Temp\csv\data.xlsx" ## Output file name 

                ) 

  

                $csvFiles = Get-ChildItem ("$CSVPath\*") -Include *.csv 

                $Excel = New-Object -ComObject excel.application 

                $Excel.visible = $false 

                $Excel.sheetsInNewWorkbook = $csvFiles.Count 

                $workbooks = $excel.Workbooks.Add() 

                $CSVSheet = 1 

  

                Foreach ($CSV in $Csvfiles) 

                { 

                                $worksheets = $workbooks.worksheets 

                                $CSVFullPath = $CSV.FullName 

                                $SheetName = ($CSV.name -split "\.")[0] 

                                $worksheet = $worksheets.Item($CSVSheet) 

                                $worksheet.Name = $SheetName 

                                $TxtConnector = ("TEXT;" + $CSVFullPath) 

                                $CellRef = $worksheet.Range("A1") 

                                $Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef) 

                                $worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True 

                                $worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1 

                                $worksheet.QueryTables.item($Connector.name).Refresh() 

                                $worksheet.QueryTables.item($Connector.name).delete() 

                                $worksheet.UsedRange.EntireColumn.AutoFit() 

                                $CSVSheet++ 

                } 

  

                $workbooks.SaveAs($XLOutput,51) 

                $workbooks.Saved = $true 

                $workbooks.Close() 

                [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null 

                $excel.Quit() 

                [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null 

                [System.GC]::Collect() 

                [System.GC]::WaitForPendingFinalizers() 

  

} 

# define the working folder for the csv files  

$targetDir = "c:\temp\csv" 

  

# Extract the credential using password.txt as the password source 

$user = "richard.leeman@mycustomdomain.com" 

# the location of the password file created above

$file = "C:\Temp\Password.txt" 

$myCred=New-Object -TypeName System.Management.Automation.PSCredential  -ArgumentList $user, (Get-Content $file | ConvertTo-SecureString) 

Connect-AzureRmAccount -Credential $mycred 

  

# get all the assets 

$data = Get-AzureRmResource | Sort-Object ResourceGroupName, ResourceType 

  

# get a distinct list of resource groups 

$distinctRG = $data | Select-Object ResourceGroupName -Unique 

  

# export the data to CSV 

$len = $distinctRG.Length; 

  

$a = New-Object -comobject Excel.Application 

$a.Visible = $False 

  

for ($i=0;$i -lt $len; $i++) { 

                $name = $distinctRG[$i] 

                $filename = "$targetDir\$($name.ResourceGroupName).csv" 

                

                # Export the data to a file - PowerShell is awesome

                $data | Where-Object -FilterScript { $_.ResourceGroupName -eq $name.ResourceGroupName} | Sort-Object ResourceType, Name |  Export-Csv -Path $filename 

} 

# merge all the files into the final document

Merge-CSVFiles -CSVPath $targetDir -XLOutput "$targetDir\MyCloudAssets.xlsx"