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 custom...
See more...
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 } #>