VM Billing script based on tags. Nested foreach no...

eric_silberberg · ‎12-11-2019

I may be going crazy but a V billing script based on tags from a few years back isn't working for me anymore. My innermost nested loop returns the first result even

My first loop pulls customers based on Category SLACustomer. Works fine.

My second loop pulls all the vm's for that customer. Works fine.

My third loop pulls the billing code from the customer list and summarizes them. I get no errors, but it processes just the first item from the list.

My quasi local variable that I used for increment isn't behaving. My foreach builtin variable returns nothing. I'm wondering if this is due to changes in powershell or powercli versions in the last few years.

It could be something simple like my loop counters, or foreach vs foreach object but I can't find it.

Please pardon the umpteen debugging write-hosts I have for tracking where I am in the script.

#sla costs ver 1.0 11/16/16

#Revisions ver 1.1 11/17/16 multiple tabs on xls workbook

#Revisions ver 1.2 11/18/16 summary data under each customer, pull vms that have the tag set for category slacustomername and report on the cost

#rev 1.3 11/29/16 delete file if it exists, sort by ouc, folder and vmname. still failing on totals row summary for mutiple ouc codes, need to trap for ouc blank

#rev 1.4 11/30/16 fixed failing on totals row summary for mutiple ouc codes, still need to trap for ouc blank, fix formatting for subtotals

#rev 1.5 12/01/16 added full folderpath, fixed sort order for vmname.added ouctotal

#rev 1.6 12/02/16 formatting for $ fields, used an extra variable in initial results, 1 for calc, 1 for display

#added separate sheets per customer for carrie, figuring out where to keep contact info

#rev 1.7 1/19 #add tag data for billingcategory and a tab for allvms as well

#rev 1.8 Setup for vcpp license cost calculation. Updated constants for FY 19-20. Adduing customer total as well as folder totals

#next features: get datastore name and type to report for multiplestorage types within a single vm close excel if open, gather a list of all vm's in the farm and show which don't have key tags set

#dependent on metadata tags being imported. starts with chargeback as primary filter, Prices on internal google sheets uses different rounding and have more significant digits.

#needed modules the amazing lucd get-folderpath http://www.lucd.info/2012/05/18/folder-by-path/, import-excel https://www.powershellgallery.com/packages/ImportExcel/7.0.0

$credential = Get-Credential -Message "Please input username and password"

Connect-VIServer -server vcenter.ncsu.edu -credential $credential

<#################

License costs based on memory.

Formula <=24G 0.52*(rounddown((((.5*vMem)*hrs)/1024)/hrs)*5),0)

Formula if<=24G, else >24G IFS(vMem <= 24576, 0.52*(rounddown(((((0.5*vMEM)*hrs-month)/1024)/hrs-month)*5,0)), vMEM >(24*1024), 0.52*(rounddown(((((24*1024)*hrs-month)/1024)/hrs-month)*5,0)) )

#####>

$ouctotal = $null

$loopcounter = $null

$totalvmcount = 1

$totalsrow = $null

$outercount = $null

$increment = $null

#rates

$diskrate = .78125

$CapIO = .49

$StandardIO = .78

$PerfIO = 1.07

$FTERate = .25

$FTECalc = (1 + $FTERate)

$cpurate = 51.54310 * $FTECalc

$memrate = 32.85836 * $FTECalc

$Allvms = Get-Vm

$DiskTags = Get-TagAssignment -Category DiskTier #| select tag, entity #call all machines with an disk set and place into an array

$Diskhash = @{ } #create hashtable for datastore and disk tier

foreach ($disktagshash in $disktags)

{ $diskhash.add($disktagshash.entity.name, $disktagshash.tag.name) } #populate hash table with key and value cleanly

$CustomerCategories = Get-Tag -Category slacustomername #call all machines with a sla name set set and place into an array

#may change to all machines, may change to chargeback pre set

#customertags above servers a different purpose, need another line for customerhash below

$Customertags = Get-TagAssignment -Category slacustomername | select tag, entity

$Customerhash = @{ }

foreach ($customertagshash in $customertags)

{ $customerhash.add($customertagshash.entity.name, $customertagshash.tag.name) } #populate hash table with key and value cleanly

$OUCtags = Get-TagAssignment -Category OUC | select tag, entity #call all machines with an OUC set and place into an array

$OUChash = @{ } #create hashtable for vm and ouc

foreach ($outagshash in $ouctags)

{ $ouchash.add($outagshash.entity.name, $outagshash.tag.name) } #populate hash table with key and value cleanly

$FAStags = Get-TagAssignment -Category ProjectFAS | select tag, entity #call all machines with an FAS set and place into an array

$FAShash = @{ } #create hashtable for vm and FAS

foreach ($fastagshash in $FAStags)

{ $FAShash.add($fastagshash.entity.name, $fastagshash.tag.name) } #populate hash table with key and value cleanly

$Billingtags = Get-TagAssignment -Category BillingCategory | select tag, entity #call all machines with an FAS set and place into an array

$Billinghash = @{ } #create hashtable for vm and Billing category

foreach ($billingtagshash in $billingtags)

{ $billinghash.add($billingtagshash.entity.name, $billingtagshash.tag.name) } #populate hash table with key and value cleanly

$date = Get-Date -format M-d-yyyy

$exportfile = "C:\temp\sla-$date.xls"

if (test-path $exportfile) { remove-item $exportfile }

#Loop 1, loop through all customer groups based on Category SLACustomerName

foreach ($tag in $CustomerCategories) {

$tagname = $tag.name

#write-host "press enter to start customer $tagname" # enable for debugging a single customer

#pause # enable for debugging a single customer

write-host "Beginning customer $tagname"

$hastags = get-vm -tag $tag #get a list of each vm and it's details

$tagcount = $hastags.Count

write-host "There are $tagcount vm's for $tagname"

$results = @()

#optional scope limiter for debug or different datacenters ex. get-vm -location (Get-Datacenter -Name "Deployment")

#Loop 2 Loop through all vm's for the active customer

foreach ($item in $hastags) {

$scopevm=$item.Name

$mainitem=$ouchash.keys

if ($mainitem -contains $scopevm) { $ouclookup=$OUChash[$item.name]} Else {$ouclookup="NotSet"} #find all vm's in the loop in the hash table and compare to array to see if ouc set

write-host "Processing VM $item with ouc $ouclookup"

write-host "Processing VM $item"

$fullfolderpath = (get-folderpath $item.Folder | select path) #use lucD get-folderpath function

$details = [pscustomobject]@{

Customer = $tagname

Folder = $item.Folder

FolderPath = $fullfolderpath.path

VMName = $item.Name

CpuCount = [math]::Round($item.NumCpu,2)

MemoryUsed = [math]::Round($item.memorygb,2)

#DiskUsed = [math]::Round($item.ProvisionedSpaceGB,2) - $item.memorygb # changing from {int} for rounding errors testing. changed on line below and 2 susequent lines

DiskUsed = [int]$item.ProvisionedSpaceGB - $item.memorygb #subtracting ram amount since fy 19-20 worksheet doesn't include ram disk costs

CPUCost = ($item.NumCpu * $cpurate).ToString('c')

MemCost = ($item.memorygb * $memrate).ToString('c')

DiskCost = (([int]$item.ProvisionedSpaceGB- $item.memorygb) * $diskrate).ToString('c')

#LicenseCost = (((($item.MemoryGB * .5) * 5) * 0.52) * 12).ToString('c') #original syntax

LicenseCost = $(if ($item.memorygb -le 24) { (((($item.MemoryGB * .5) * 5) * 0.52) * 12).ToString('c')} elseif ($item.memorygb -gt 24) {((((24) * 5) * 0.52) * 12).ToString('c')}) #regular calc elseif $item.memorygb -ge 24 {24 * regular calc}-le

VMCost = ((((($item.MemoryGB * .5) * 5) * 0.52) * 12) + ($item.NumCpu * $cpurate) + ($item.memorygb * $memrate) + (([int]$item.ProvisionedSpaceGB- $item.memorygb) * $diskrate)).ToString('c')

OUC= $ouclookup

FAS = $fashash.item($item.name)

Billing = $billinghash.item($item.name)

PowerState = $item.PowerState

CPUCostCalc = [math]::Round(($item.NumCpu * $cpurate),2,[MidpointRounding]::AwayFromZero)

MemCostCalc = [math]::Round(($item.memorygb * $memrate),2,[MidpointRounding]::AwayFromZero)

DiskCostCalc = [math]::Round((([int]$item.ProvisionedSpaceGB - $item.memorygb) * $diskrate),2,[MidpointRounding]::AwayFromZero)

#LicenseCostCalc = [math]::Round((((($item.MemoryGB * .5) * 5) * 0.52) * 12),2,[MidpointRounding]::AwayFromZero) original syntax

LicenseCostCalc = $(if ($item.memorygb -le 24) { (((($item.MemoryGB * .5) * 5) * 0.52) * 12)} elseif ($item.memorygb -gt 24) {((((24) * 5) * 0.52) * 12)}) #regular calc elseif $item.memorygb -ge 24 {24 * regular calc}-le

VMCostCalc = [math]::Round($(if ($item.memorygb -le 24) { (((($item.MemoryGB * .5) * 5) * 0.52) * 12)} elseif ($item.memorygb -gt 24) {((((24) * 5) * 0.52) * 12)}),2,[MidpointRounding]::AwayFromZero) + [math]::Round(($item.NumCpu * $cpurate),2,[MidpointRounding]::AwayFromZero) + [math]::Round(($item.memorygb * $memrate),2,[MidpointRounding]::AwayFromZero) + [math]::Round((([int]$item.ProvisionedSpaceGB - $item.memorygb) * $diskrate),2,[MidpointRounding]::AwayFromZero)

}

$results += $details #enumerate each vm into an array

}

$outercount = $results.Count #total number of vm's for that customer

Write-Host "pre export outercount is $outercount, totalvmcount is $totalvmcount"

$subresultcount = $null

#export each customer into an excel spreadsheet, each customer on its own tab

$results | select Folder, FolderPath, VMName, CpuCount, MemoryUsed, DiskUsed, CPUCost, MemCost, DiskCost, LicenseCost, VMCost, OUC, FAS, Billing, PowerState | Sort-Object ouc, folderpath, vmname | Export-Excel -Path "C:\temp\sla-$date.xls" -WorkSheetname $tagname -AutoSize -BoldTopRow -KillExcel #place each customer in it's own tab

$results | select Folder, FolderPath, VMName, CpuCount, MemoryUsed, DiskUsed, CPUCost, MemCost, DiskCost, LicenseCost, VMCost, OUC, FAS, Billing, Customer, PowerState | Sort-Object ouc, folderpath, vmname | Export-Excel -Path "C:\temp\sla-$date.xls" -WorkSheetname "All-VMs" -AutoSize -NoHeader -startrow $totalvmcount -KillExcel #place all customers in 1 tab

$results | select Folder, FolderPath, VMName, CpuCount, MemoryUsed, DiskUsed, CPUCost, MemCost, DiskCost, LicenseCost, VMCost, OUC, FAS, Billing, PowerState|Sort-Object ouc, folderpath, vmname |Export-Excel -Path "C:\temp\sla-$tagname-$date.xls" -AutoSize -BoldTopRow -KillExcel #place each customer in it's own excel sheet

$loopcounter += 1

$totalvmcount += $outercount

Write-Host "post export outercount is $outercount, totalvmcount is $totalvmcount"

$customerouclist = ($results | select ouc -Unique)

$customerouclist

$CustomerOucCount = ($CustomerOucList | measure).count #use measure method since single or objects can have trouble with the .count property as they may be system.object instead of system.array

$totalsrow = $null

$customertotal=@()

$increment=0

$subresults = @()

#Loop 3 Loop through all OUC's for active customer

foreach ($customerouc in $customerouclist)

{

#$local:increment

$ouctotal = $customerouc.ouc

write-host "customer $tagname has $customerouccount different ouc's"

write-host "Processing totals for OUC $ouctotal for customer $tagname, pass # $loopcounter #, foreach counter is $foreach" #$foreach taken from https://ss64.com/ps/syntax-automatic-variables.html maybe deprecated????

$foldertotals = $null

$subresultcount = $null

#create variables to hold the summary info for each pieace of customer data

#$results | measure CpuCount, MemoryUsed, DiskUsed,diskcost,memcost -sum

$subresults = $results | where { $_.ouc -eq $ouctotal } #find just the active ouc in the loop

$subresultcount = ($subresults | measure).count #user measure method since single or objects can have trouble with the .count property as they may be system.object instead of system.array

#IF ($subresultcount -eq '1') {write-host "just 1"} # error check for system.object

Write-host "OUC $ouctotal has $subresultcount vms"

$NumCpuSum = ($subresults | measure CpuCount -sum).sum

$MemoryGBSum = ($subresults | measure MemoryUsed -sum).sum

$ProvisionedSpaceGBSum = ($subresults | measure DiskUsed -sum).sum

$CPUCostSum = ($subresults | measure CPUCostCalc -sum).sum

$MemCostSum = ($subresults | measure MemCostCalc -sum).sum

$DiskCostSum = ($subresults | measure DiskCostCalc -sum).sum

$LicenseCostSum = ($subresults | measure LicenseCostCalc -sum).sum

$OUCTotalCost = $DiskCostSum + $MemCostSum + $CPUCostSum + $LicenseCostSum

write-host "Counter Values:outercount is $outercount, subsresultcount is $subresultcount, and increment is $increment and totalsrow location before OUC iterate is $totalsrow"

$totalsrow = $outercount + 3 + $increment

$increment++

write-host "outercount is $outercount, subsresultcount is $subresultcount and totalsrow location after iterate is $totalsrow"

#write-host "press enter to start customer $tagname" # enable for debugging a single customer

#create custom object to hold the summary info for each cutomer

$foldertotals = [pscustomobject]@{

#Customer=$tagname

VMTotal = $subresultcount

CpuTotal = $NumCpuSum

MemoryTotal = $memorygbsum

DiskTotal = $ProvisionedSpaceGBsum

CPUCostTotal = $CPUCostsum.ToString('c')

MemCostTotal = $MemCostsum.ToString('c')

DiskCostTotal = $DiskCostsum.ToString('c')

LicenseCostTotal = $LicenseCostsum.ToString('c')

OUCTotalCost = $OUCTotalCost.ToString('c')

OUC = $customerouc.ouc

}

#export each customer summary into customer tab, of excel spreadhseet, place 2 rows after data ends and 5 columns in.

$subresults | Format-Table -GroupBy ouc

$loopcounter += 1

write-host "Writing sub-totals to row $totalsrow"

$foldertotals | Export-Excel "C:\temp\sla-$date.xls" -WorkSheetname $tagname -AutoSize -BoldTopRow -startrow $totalsrow -StartColumn 3

$foldertotals | Export-Excel "C:\temp\sla-$tagname-$date.xls" -AutoSize -BoldTopRow -startrow $totalsrow -StartColumn 3

$customertotal += $foldertotals

$customertotalrow=$totalsrow + $CustomerOucCount + 2

$customertotal | Export-Excel "C:\temp\sla-$date.xls" -WorkSheetname $tagname -AutoSize -BoldTopRow -startrow $customertotalrow -StartColumn 3

$customertotal | Export-Excel "C:\temp\sla-$tagname-$date.xls" -AutoSize -BoldTopRow -startrow $customertotalrow -StartColumn 3

#pause enable for debugging a single customer

}

#Probably need to move customertotal down here outside OUC loop

}

#END

<#

#Misc test code

$grouparray = $results | Group-Object -Property ouc | foreach { "$($_.name) | Measure-Object -Property numcpu -sum).sum" }

$results | measure NumCpu, MemoryGB, ProvisionedSpaceGB, cpucost, memcost, diskcost -sum

$results | Format-Table -GroupBy ouc

$results | Group-Object ouc | `select-object name, @{name = 'Totals'; Expression = { ($_.group | Measure-Object NumCpu, MemoryGB, ProvisionedSpaceGB, cpucost, memcost, diskcost -sum).sum } } | format-table

foreach ($customerouc in $customerouclist) {

$ouctotal = $customerouc.ouc

Write-Host "writing customer $ouctotal"

}

$results

$results | select ouc, diskcost | Sort-Object ouc, folderpath, vmname | Export-Excel -Path "C:\temp\sla-$date.xls" -WorkSheetname $tagname -AutoSize -BoldTopRow -KillExcel

foreach ($item in $hastags) {

IF (!$ouchash.item ) { $ouctotal = "Blank" } Else { $ouctotal = $ouchash.item.Values }

write-host "VM $item ouc is $ouctotal"

}

Processing VM

LicenseCost=IF($item.memorygb<24) { (0.52 * (INT(((((0.5 * $item.MemoryGB) * 744) / 1024) / 744) * 5, 0) }

$ram = 16

$licence = 12 * (0.52 * (((((0.5 * $item.MemoryGB) * 744) / 744) * 5)))#>

################

<#

Troubleshooting notes as of 12/4

Transition-I-DC2-U650F-0-000

1. Costs need to be currency format-exportexcel has currency formatting

2. check rounding on the cost calculation

PS G:\My Drive\PowerShell> $m=[Math]::Round(352.745,2,[MidpointRounding]::AwayFromZero)^C

PS G:\My Drive\PowerShell> $m

352.75

PS G:\My Drive\PowerShell>

3. pull datastores, perhaps use has reference table, or loop for each disk.

4.memory over 24 gig needs to be capped for license-done

5. VM totals are for the last OUC not true totals, add 1 more loop or don't bother totalling by ouc

6. reports of duplicate datastores causing hash add failure. filter with unique??

7. log all screen results to text file since it ovefills buffer

IF ($item.MemoryGB -le 23) {write-host "standard license calculation"} elseif ($item.MemoryGB -ge 24) {write-host "capped license calculation"} LicenseCost = (((($item.MemoryGB * .5) * 5) * 0.52) * 12).ToString('c')

idea, import file with small.1, small.2, medium.1 to validate existing sizes against prices. could also create a templates folder if they appear

$VM = Get-VM -Name edsilber-vm.oit.ncsu.edu

$Disks = Get-HardDisk -VM $VM

foreach($disk in $Disks) {

$FileName = $Disk.FileName

$Diskname = $Disk.Name

$datastore = $FileName.split("]")[0].split("[")[1]

"{0} : {1}" -f $Diskname,$datastore

}

#>

LucD · ‎12-11-2019

Without knowing the details and having some sample data or any output from the script, writing the result in loop #3 inside the loop looks incorrect.

I suspect those 2 Export-Excel lines should be outside loop #3

Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference

eric_silberberg · ‎12-11-2019

redacted output for first 2 business unit follows:

Beginning customer TEX

There are 7 vm's for TEX

Processing VM vm1..... with ouc NotSet

Processing VM vm1...

Processing VM vm2... with ouc 180101

Processing VM vm2.....

Processing VM vm3.....with ouc 180601

Processing VM vm3.....

Processing VM vm4.....with ouc 180101

Processing VM vm4.....

Processing VM vm5.....with ouc 180101

Processing VM vm5

Processing VM vm6 with ouc 180101

Processing VM vm6....

Processing VM vm7..... with ouc 180101

Processing VM vm7...

pre export outercount is 7, totalvmcount is 1

post export outercount is 7, totalvmcount is 8

OUC : NotSet

OUC : 180101

OUC : 180601

customer TEX has 3 different ouc's

Processing totals for OUC NotSet for customer TEX, pass # 1 #, foreach counter is

OUC NotSet has 1 vms

Hit Line breakpoint on 'G:\My Drive\PowerShell\vm-billing-2019-draft.ps1:182'

[DBG]: PS G:\My Drive\PowerShell>

Counter Values:outercount is 7, subsresultcount is 1, and increment is 0 and totalsrow location before OUC iterate is

outercount is 7, subsresultcount is 1 and totalsrow location after iterate is 10

OUC: NotSet

Customer Folder FolderPath VMName CpuCount MemoryUsed DiskUsed CPUCost MemCost DiskCost

-------- ------ ---------- ------ -------- ---------- -------- ------- ------- --------

TEX TEX Deployment\Departments\TEX vm1 1 4 100 $64.43 $164.29 $78.13

Writing sub-totals to row 10

Beginning customer CED

There are 4 vm's for CED

Processing VM vm1 with ouc 131002

Processing VM vm1

Processing VM vm2 with ouc 131002

Processing VM vm2

Processing VM vm3 with ouc 131002

Processing VM vm3

Processing VM vm4 with ouc 131002

Processing VM vm4

pre export outercount is 4, totalvmcount is 8

post export outercount is 4, totalvmcount is 12

customer CED has 1 different ouc's

Processing totals for OUC 131002 for customer CED, pass # 3 #, foreach counter is

OUC 131002 has 4 vms

LucD · ‎12-11-2019

So are you saying that those Export-Excel lines need to be inside loop #3?

Since you are initialising the array you write to the .XLSX outside loop #3, I would assume that the write should occur after loop #3.
Or am I missing something?

Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference

All

VM Billing script based on tags. Nested foreach not processing all elements or enumerating