Wednesday, 1 August 2018

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"