PowerShell script that performs a SUMIF against an Excel file

Question

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
Write-Host($qry.Data.Count)
$qry.Data | Export-Csv "C:\Users\sumra\Documents\PWSH Scripts NRF\Outputs\stoppedvms.csv" -Append

Comments

For clarity, do you want to use VLOOKUP() or SUMIF()? The two functions do different things. And is your output just a single data row (with the "cost" column added)? Or do you need to fetch multiple cost values? From the same bill file or from different ones?

– Ansgar Wiechers 2022-09-14

---

Apologies- I said VLOOKUP in the title but I meant to say SUMIF

The output is the original output from the script but with an additional column added for the Cost. This is the SUMIF calculation from the same bill file

(The original output from the script already consists of multiple columns (PowerState, provisionstate, subscriptionId, name, resourceGroup, id, ResourceId). I want the 'Cost' column to be added next to these)

– sjh95 2022-09-14 ✍

Answer 1

I'd probably do the calculation in the bill file.

1. Open the bill file in Excel.
2. Add a new worksheet.
3. Enter the formula into the new worksheet.
4. Fetch the result.
5. Quit Excel without saving the input file.

Something like this:

$xl = New-Object -ComObject Excel.Application
$wb = $xl.Workbooks.Open($args[0])

$ws = $wb.Sheets.Add()
$ws.Range('A1').Formula = "=SUMIF('Bill File'!AB:AB,G2,'Bill File'!V:V)"

$cost = $ws.Range('A1').Value

$wb.Close($false)  # close without saving
$xl.Quit()

# cleanup
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
Remove-Variable xl

Then add $cost to your data row with a calculated property before appending the data to the output file:

$qry.Data |
  Select-Object -Property *,@{n='Cost';e={$cost}} |
  Export-Csv 'C:\path\to\output.csv' -Append

Beware that for the additional field to appear in the output file, the CSV must already have that field, so you may need to edit your output CSV first and add that column. Otherwise PowerShell would omit the (unknown) field from the output when appending to the file.

Comments

thank you! I will have a go and let you know if it works

– sjh95 2022-09-16 ✍