I have several PowerShell scripts that pull specific lists of resources from the Azure Portal (e.g. all virtual machines that have been stopped, all unused IP addresses, all unattached disks).
I use the csv outputs of these scripts to perform analysis and see how much these resources cost.
To do the analysis, I have to run the PowerShell script to get the list of resources. I also have to download the Azure bill (an Excel file). I then copy the Azure bill onto a new sheet in the script output file. I add a new column to the script output file called 'Cost'. This is a lookup against the bill file, and provides the individual cost of each resource. The formula used is
=SUMIF('Bill File'!AB:AB,G2,'Bill File'!V:V)
I would like to automate this piece of analysis so that I can just input the bill file into the PowerShell script and have an output file that contains the Cost column
Here is an example of one of the PowerShell scripts that I use (this one gives a list of all virtual machines that have been stopped):
Set-AzContext -TenantId "f8a46a38-2d3d-4672-ae57-34e8f266feb4"
and PowerState =~ 'stopped'
$qry = Search-AzGraph -Query "Resources | where type =~ 'Microsoft.Compute/VirtualMachines' and properties.extended.instanceView.powerState.code =~ 'PowerState/stopped' | project PowerState = tostring(properties.extended.instanceView.powerState.code), provisionstate = tostring(properties.provisioningState),subscriptionId , name , resourceGroup , id" -First 1000
$qry.Data | Export-Csv "C:\Users\sumra\Documents\PWSH Scripts NRF\Outputs\stoppedvms.csv" -Append