Automation

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.

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)"
}

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:

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)"
            }