Automation

Hi,

I am having issue getting the VM information from below script. Some of the folder has sub folders, when the report is generated, I see duplicates like, one tab would have main folder and sub folder vms and again another tab would have sub folder VMs.

I would like to get individual folder wise VMs details. Please help. I am using vCenter 6.5 and ESXi 6.5

Script:

function get-mysummary() {

  param(

   [VMware.VimAutomation.ViCore.Types.V1.Inventory.Folder]$Folder

  )

  $report = @()

  $report += Get-VM -Location $Folder |

  Select Folder, Name,

  @{N = "IP Address"; E = {@($_.guest.IPAddress[0])}},

  @{N="VM PowerState";E={@($_.PowerState)}},

  @{N="OS"; E={@($_.guest.OSFullName)}},

  @{N = "CPU"; E = {@($_.NumCPU)}},

  @{N = "Memory (GB)"; E = {@($_.MemoryGB)}},

  @{N = "Provisioned (GB)"; E = {[math]::Round($_.provisionedspacegb)}}

  $vmcount = $report | Measure-Object "Name"

  $sum = $report | Measure-Object -sum "CPU", "Memory (GB)", "Provisioned (GB)"

  $row1 = "" | Select "Folder", "Name", "CPU", "Memory (GB)", "Provisioned (GB)"

  $report += $row1

  $row2 = "" | Select "Folder", "Name", "CPU", "Memory (GB)", "Provisioned (GB)"

  #$row2."Name" = 'Total VMs'

  $row2."CPU" = 'Total CPU'

  $row2."Memory (GB)" = 'Total Memory (GB)'

  $row2."Provisioned (GB)" = 'Total Provisioned(GB)'

  $report += $row2

  $row3 = "" | Select "Folder", "Name", "CPU", "Memory (GB)", "Provisioned (GB)"

  #$row3.Name = $vmcount | where {$_.Property -eq 'Name'} | select -ExpandProperty Count

  $row3.CPU = $sum | where {$_.Property -eq 'CPU'} | select -ExpandProperty Sum

  $row3."Memory (GB)" = $sum | where {$_.Property -eq "Memory (GB)"} | select -ExpandProperty Sum

  $row3."Provisioned (GB)" = $sum | where {$_.Property -eq "Provisioned (GB)"} | select -ExpandProperty Sum

  $report += $row3

  $row4 = "" | Select "Folder", "Name"

  $row4."Folder" = 'Total VMs'

  $row4.Name = $vmcount | where {$_.Property -eq 'Name'} | select -ExpandProperty Count

  $report += $row4

  $row5 = "" | Select "Folder", "Name"

  $row5."Folder" = 'Powered On VMs'

  $row5.Name = (Get-VM -Location $Folder | where {$_.PowerState -eq 'PoweredOn'}).count

  $report += $row5

  $row6 = "" | Select "Folder", "Name"

  $row6."Folder" = 'Powered Off VMs'

  $row6.Name = (Get-VM -Location $Folder | where {$_.PowerState -eq 'PoweredOff'}).count

  $report += $row6

  $row7 = "" | Select "Folder", "Name"

  $row7."Folder" = 'Windows VMs'

  $row7.Name = (Get-VM -Location $Folder | Get-View | where {$_.summary.config.guestFullName -match 'Windows'}).count

  $report += $row7

  $row8 = "" | Select "Folder", "Name"

  $row8."Folder" = 'CentOS VMs'

  #$row8.Name = (Get-VM -Location $Folder | Get-View | where {$_.summary.config.guestFullName -match 'CentOS'}).count

  $row8.Name = (Get-VM -Location $Folder | Get-View | where {$_.summary.config.guestFullName -match 'CentOS' -or $_.summary.config.guestFullName -match 'Red Hat'}).count

  $report += $row8

  $report

}

foreach ($fld in Get-Folder -Type VM) {

  get-mysummary -Folder $fld |

   Export-Excel -Path $reportlocation -AutoFilter -AutoSize -WorksheetName $fld.Name

}

Can you try changing this line

Hi LucD,

That worked but first tab shows blank, before, I used to get the list of all VMs in first tab. now it is shows blank.

Probably because there are no VMs in that folder.

Is that correct?

We could exclude the folder when there are no VMs in there.

Hi LucD,

The first tab would be like summary page, where it would list all the VMs in the vCenter and then next tab would be based on folder.

Here, the I am not seeing the list of all the VMs of the vcenter in the first tab.

Before making the above change, I was getting all the VMs details in the first tab, but now it is not showing

Try something like this.
PS: I add the datacenter, to cope with the possibility that there is more than 1 datacenter in the vCenter.

Thank you very much Champ :smileyhappy: