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
}
#>
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
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>
[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
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