I have this script and it works well for pulling this info 1 at a time for each cluster. I am trying to figure out how I can automate it so that I can pull all clusters in my vcenter at once, possibly creating a single spreadhseet with multiple tabs for each cluster. Any help would be greatly appreciated.
#########################################################
#
# PowerCLI Script to check Host Overcommitment (CPU & Memory)
#
#
#########################################################
# Select which vCenter you want to connect to
# Build array for each vCenter
Write-host "Select which vCenter to connect to:"
Write-Host ""
Write-Host "1. spvcenter11"
Write-Host "2. srvcenter11"
Write-Host "3. sdvcenter11"
$Ivcenter = read-host “Select a vCenter Server. Enter Number:“
if ($Ivcenter -match "1") {
$vcenter = "spvcenter11"
} elseif ($Ivcenter -match "2") {
$vcenter = "srvcenter11"
} else {
$vcenter = "sdvcenter11"
}
write-host ""
Write-Host "You Picked: $($vcenter)"
write-host ""
start-sleep -s 3
# connect to selected vCenter
connect-viserver $vcenter
# List Clusters
write-host ""
Write-host "Choose which Cluster you want to gather ratios on:"
write-host "(it may take a few seconds to build the list)"
write-host ""
$ICLUSTER = get-cluster | Select Name | Sort-object Name
$i = 1
$ICLUSTER | %{Write-Host $i":" $_.Name; $i++}
$HCLUSTER = Read-host "Enter the number for the host to Patch."
$SCLUSTER = $ICLUSTER[$HCLUSTER -1].Name
write-host "You have selected $($SCLUSTER)."
start-sleep -s 3
# Get CPU Overcommitment Information
&{ForEach ($esx in (get-cluster $SCLUSTER | Get-VMHost)) {
$vCPU = Get-VM -Location $esx | where {$_.PowerState -match "on"} | Measure-Object -Property NumCpu -Sum | select -ExpandProperty Sum
$esx | Select Name, @{N='pCPU cores available';E={$_.NumCpu}},
@{N='vCPU assigned to VMs';E={$vCPU}},
@{N='Ratio';E={[math]::Round($vCPU/$_.NumCpu,1)}},
@{N='CPU Overcommit (%)';E={[Math]::Round(100*(($vCPU - $_.NumCpu) / $_.NumCpu), 1)}}
# Export results to Excel
}} | Export-csv -path C:\OC\CPU_Overcommitment_$($SCLUSTER).csv
# Get Memory Overcommitment Information
&{ForEach ($esx in (get-cluster $SCLUSTER | Get-VMHost)) {
$vMem = get-vm -location $esx | where {$_.PowerState -match "on"} | measure-object -property MemoryGB -SUM | Select -Expandproperty Sum
$esx | Select Name, @{N='Total Memory Available';E={[Math]::Round($_.MemoryTotalGB),1}},
@{N='Memory Assigned to VMs';E={$vMem}},
@{N='Ratio';E={[math]::Round(100*($vMem / $_.MemoryTotalGB), 1)}},
@{N='Memory Overcommit (%)';E={[Math]::Round(100*(($vMem - $_.MemoryTotalGB) / $_.MemoryTotalGB), 1)}}
# Export results to CSV
}} | Export-csv -path C:\OC\memory_overcommitment_$($SCLUSTER).csv
# Disconnect from vCenter
Disconnect-viserver $vcenter -confirm:$false
If you install the ImportExcel module, you could something like this.
A worksheet per cluster for CPU and Memory, and all in the same spreadsheet.
The script will report on all clusters for all vCenters to which you are connected when running the script.
ForEach-Object -Process {
$reportCpu = @()
$reportMem = @()
Get-VMHost -Location $cluster -PipelineVariable esx |
ForEach-Object -Process {
$vCPU = Get-VM -Location $esx | where { $_.PowerState -match "on" } | Measure-Object -Property NumCpu -Sum | select -ExpandProperty Sum
$reportCpu += $esx | Select Name, @{N = 'pCPU cores available'; E = { $_.NumCpu } },
@{N = 'vCPU assigned to VMs'; E = { $vCPU } },
@{N = 'Ratio'; E = { [math]::Round($vCPU / $_.NumCpu, 1) } },
@{N = 'CPU Overcommit (%)'; E = { [Math]::Round(100 * (($vCPU - $_.NumCpu) / $_.NumCpu), 1) } }
$vMem = get-vm -location $esx | where { $_.PowerState -match "on" } | measure-object -property MemoryGB -SUM | Select -Expandproperty Sum
$reportMem += $esx | Select Name, @{N = 'Total Memory Available'; E = { [Math]::Round($_.MemoryTotalGB), 1 } },
@{N = 'Memory Assigned to VMs'; E = { $vMem } },
@{N = 'Ratio'; E = { [math]::Round(100 * ($vMem / $_.MemoryTotalGB), 1) } },
@{N = 'Memory Overcommit (%)'; E = { [Math]::Round(100 * (($vMem - $_.MemoryTotalGB) / $_.MemoryTotalGB), 1) } }
}
$reportCpu | Export-Excel -Path C:\OC\CPU_Overcommitment.xlsx -WorksheetName "CPU-$($cluster.Name)"
$reportMem | Export-Excel -Path C:\OC\CPU_Overcommitment.xlsx -WorksheetName "Memory-$($cluster.Name)"
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
LucD,
How can I change this to show the overcommitment per Cluster. Say for instance I changed "WorksheetName" to "CPU" and added -Append. That lists all the hosts individually on one sheet, Then I added @{N ='Cluster'; E = { $_.parent}} to the Select and I get a column for cluster.
But I want just at the cluster level, ie. One line per cluster.
Something like this?
Get-Cluster -PipelineVariable cluster |
ForEach-Object -Process {
$reportCpu = @()
$reportMem = @()
$clusterCpuOC = @()
$clusterMemOC = @()
Get-VMHost -Location $cluster -PipelineVariable esx |
ForEach-Object -Process {
$vCPU = Get-VM -Location $esx | where { $_.PowerState -match "on" } | Measure-Object -Property NumCpu -Sum | select -ExpandProperty Sum
$clusterCpuOC += ($vCPU - $esx.NumCpu) / $esx.NumCpu
$reportCpu += $esx | Select Name, @{N = 'pCPU cores available'; E = { $_.NumCpu } },
@{N = 'vCPU assigned to VMs'; E = { $vCPU } },
@{N = 'Ratio'; E = { [math]::Round($vCPU / $_.NumCpu, 1) } },
@{N = 'CPU Overcommit (%)'; E = { [Math]::Round(100 * (($vCPU - $_.NumCpu) / $_.NumCpu), 1) } }
$vMem = Get-VM -Location $esx | where { $_.PowerState -match "on" } | Measure-Object -property MemoryGB -SUM | Select -Expandproperty Sum
$clusterMemOC += ($vMem - $esx.MemoryTotalGB) / $esx.MemoryTotalGB
$reportMem += $esx | Select Name, @{N = 'Total Memory Available'; E = { [Math]::Round($_.MemoryTotalGB), 1 } },
@{N = 'Memory Assigned to VMs'; E = { $vMem } },
@{N = 'Ratio'; E = { [math]::Round(100 * ($vMem / $_.MemoryTotalGB), 1) } },
@{N = 'Memory Overcommit (%)'; E = { [Math]::Round(100 * (($vMem - $_.MemoryTotalGB) / $_.MemoryTotalGB), 1) } }
}
$cluster | Select @{N='Name';E={$cluster.Name}},
@{N='CpuOvercommit';E={[Math]::Round(100 * ($clusterCpuOC | Measure-Object -Average).Average,1)}},
@{N='MemOvercommit';E={[Math]::Round(100 * ($clusterMemOC | Measure-Object -Average).Average,1)}} |
Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName 'Cluster' -Append
$reportCpu | Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName "CPU-$($cluster.Name)"
$reportMem | Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName "Memory-$($cluster.Name)"
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
LucD, As always thanks for the quick reply. I am amazed ( and maybe a bit frustrated at times) at how easily this comes to some people..
So thanks for that update, that Cluster worksheet is almost to what I was trying to get to. And if I need to start a new post / question, just let me know.
I have added a few more columns to be able to make the sheet read better, like the CPU / Memory sheets. If there is an overcommit, see what the actual values are.
Below is my version.
$cluster | Select @{N='Name';E={$cluster.Name}},`
@{N='Hosts';E={(Get-VMHost -Location $cluster.Name).count}},
@{N='VMs';E={(get-vm -location $cluster.Name).count}},
@{N='pCPUs';E={$_.ExtensionData.Summary.NumCpuCores}},
@{N='vCPU assigned to VMs';E={}},
@{N ='CPU Ratio';E={},
@{N='CpuOvercommit';E={[Math]::Round(100 * ($clusterCpuOC | Measure-Object -Average).Average,1)}},
@{N='TotalMemory';E={$_.ExtensionData.Summary.TotalMemory}},
@{N='AllocatedMemory';E={}},
@{N='MemOvercommit';E={[Math]::Round(100 * ($clusterMemOC | Measure-Object -Average).Average,1)}} |
Export-Excel -Path $log -WorksheetName 'Cluster' -Append
And output:
Name | Hosts | VMs | pCPUs | vCPU assigned to VMs | CPU Ratio | CpuOvercommit | TotalMemory | AllocatedMemory | MemOvercommit |
working | working | working | working | Need help with expression | Need help with expression | working | Summary.TotalMemory gets a value but I guess I need to do a Math to get to GB, unless you know a better way. | Need help with expression | working |
I cant seem to come up with how to make the expressions get the info I am looking for. Thanks for your help.
Check if this brings the values you want.
Get-Cluster -PipelineVariable cluster |
ForEach-Object -Process {
$reportCpu = @()
$reportMem = @()
$clusterCpuOC = @()
$clusterMemOC = @()
Get-VMHost -Location $cluster -PipelineVariable esx |
ForEach-Object -Process {
$vCPU = Get-VM -Location $esx | where { $_.PowerState -match "on" } | Measure-Object -Property NumCpu -Sum | select -ExpandProperty Sum
$clusterCpuOC += ($vCPU - $esx.NumCpu) / $esx.NumCpu
$reportCpu += $esx | select Name, @{N = 'pCPU cores available'; E = { $_.NumCpu } },
@{N = 'vCPU assigned to VMs'; E = { $vCPU } },
@{N = 'Ratio'; E = { [math]::Round($vCPU / $_.NumCpu, 1) } },
@{N = 'CPU Overcommit (%)'; E = { [Math]::Round(100 * (($vCPU - $_.NumCpu) / $_.NumCpu), 1) } }
$vMem = Get-VM -Location $esx | where { $_.PowerState -match "on" } | Measure-Object -Property MemoryGB -Sum | select -ExpandProperty Sum
$clusterMemOC += ($vMem - $esx.MemoryTotalGB) / $esx.MemoryTotalGB
$reportMem += $esx | select Name, @{N = 'Total Memory Available'; E = { [Math]::Round($_.MemoryTotalGB), 1 } },
@{N = 'Memory Assigned to VMs'; E = { $vMem } },
@{N = 'Ratio'; E = { [math]::Round(100 * ($vMem / $_.MemoryTotalGB), 1) } },
@{N = 'Memory Overcommit (%)'; E = { [Math]::Round(100 * (($vMem - $_.MemoryTotalGB) / $_.MemoryTotalGB), 1) } }
}
$esxArr = Get-View -Id $_.ExtensionData.Host
$vmArr = Get-View -Id $esxArr.VM
$vmCpuCount = ($vmArr.Config.Hardware.NumCpu | Measure-Object -Sum).Sum
$vmMemSizeGB = ($vmArr.Config.Hardware.MemoryMB | Measure-Object -Sum).Sum / 1KB
$esxCpuCount = ($esxArr.Summary.Hardware.NumCpuCores | Measure-Object -Sum).Sum
$cluster | select @{N = 'Name'; E = { $cluster.Name } }, `
@{N = 'Hosts'; E = { $_.ExtensionData.Host.Count } },
@{N = 'VMs'; E = { (Get-View -Id $_.ExtensionData.Host).Vm.Count } },
@{N = 'pCPUs'; E = { $_.ExtensionData.Summary.NumCpuCores } },
@{N = 'vCPU assigned to VMs'; E = { $vmCpuCount } },
@{N = 'CPU Ratio'; E = { [math]::Round(100 * ($vmCpuCount / $esxCpuCount), 1) } },
@{N = 'CpuOvercommit'; E = { [Math]::Round(100 * ($clusterCpuOC | Measure-Object -Average).Average, 1) } },
@{N = 'TotalMemory'; E = { [math]::Round($_.ExtensionData.Summary.TotalMemory / 1GB, 1) } },
@{N = 'AllocatedMemory'; E = { $vmMemSizeGB } },
@{N = 'MemOvercommit'; E = { [Math]::Round(100 * ($clusterMemOC | Measure-Object -Average).Average, 1) } } |
Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName 'Cluster' -Append
$reportCpu | Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName "CPU-$($cluster.Name)"
$reportMem | Export-Excel -Path C:\Temp\CPU_Overcommitment.xlsx -WorksheetName "Memory-$($cluster.Name)"
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference