Need help with PowerCLI to get VM info for each VCenter and output to specific sheet in excel

[vinnie967]

Hi everyone,

I would like to pull the following info from these example vcenters:

VDCNPRAZ

NACNPRAZ

PLCNPR8T

I would like to have it exported to a excel workbook name 'test.xlsm' and into the sheet name 'vmware'.  I would like to pull the following data from each vCenter and have it in the following example report layed out in the following format. I am new to PowerCLI and I would like to learn how to used it to pull the following data.

VIRTUAL HOSTNAME

PHYSICAL HOSTNAME

CLUSTER

DATA CENTER NAME

VCENTER NAME

HARDWARE VENDOR

SERVER MODEL

OPERATING SYSTEM

CPU TYPE

VIRTUAL CPU

PHYSICAL CPU SOCKET

PHYSICAL CORES PER SOCKET

SERVER FRAME

VIRTUAL MEMORY

PHYSICAL MEMORY

SERVER NOTES

PHYSICAL HOST SERIAL NUMBER

SLVPR23

SUCNPR01

DTVT01

DATACNTR1

VDCNPRAZ

HP

PROLIANT DL360P GEN8

WINDOWS 2008 ENTEPRISE EDITION

INTEL(R) XEON(R) CPU E5-2640 0 @ 2.50GHZ

2

2

6

SVERFRM1

65501

TEST SERVER

MXQ31WW60K

WLVPR45

SUCNPR02

PRVT01

DATACNTR2

NACNPRAZ

HP

PROLIANT DL360P GEN8

REDHAT LINUX 5 ENTERPRISE

INTEL(R) XEON(R) CPU E5-2640 0 @ 2.50GHZ

2

2

6

SVERFRM2

65501

WAS SERVER

MXQ312360J

PRVPR46

172.27.0.200

CLSTR1

DATACNTR3

PLCNPR8T

HP

PROLIANT BL460C G7

WINDOWS 2003 ENTEPRISE EDITION

INTEL(R) XEON(R) CPU X5650 @ 2.67GHZ

2

2

6

SVERFRM3

98293

ORACLE DB SERVER

DXQ312360J

Any help on this would be greatly appreciated.

Thank you,

v

[JohnMcKeown]

I too was learning to script a few months ago. If you are starting you should start with one liners like: 


http://virtualizeme.net/power-cli-one-liners/


This will get you used to understanding commandlets like Get-VMhost


Also try to break down your query into different parts. Most data manipulation is with CSVs. Although you can export to excel its additional work that you can just do with Excel yourself. 


There are loads of examples of host reports that you can tailor yourself. Here is a good example. https://communities.vmware.com/message/1697364 

$report = @()

Get-VMHost | ForEach-Object {

  $VMHost = $_

  $row = "" | Select-Object -Property `

      VCENTER,ESXName ,Cluster, Datacenter, `

      VMs, VMCount,Vendor, Model, `

      PowerState, ConnectionState, InMaintenanceMode, BootTime, OverallStatus, `

      Version, Build, FullName, CpuModel

  $row.VCENTER = $VMHost.ExtensionData.Client.ServiceUrl.Split('/')[2]

  $row.ESXName = $VMHost.ExtensionData.name

  $row.Cluster = $VMHost.Parent

  $row.Datacenter= (Get-Datacenter -Cluster $VMHost.Parent).Name

  $VMs = ""

  $VMHost | Get-VM | ForEach-Object {$VMs += "$($_.Name),"}

  $row.VMs = $VMs.TrimEnd(",")

  $row.VMCount = @($VMHost | Get-VM).Count

  $row.Vendor = $VMHost.ExtensionData.Summary.Hardware.vendor

  $row.Model = $VMHost.ExtensionData.Summary.Hardware.model

  $row.PowerState = $VMHost.ExtensionData.Summary.runtime.powerstate

  $row.ConnectionState = $VMHost.ExtensionData.Summary.runtime.connectionState

  $row.InMaintenanceMode = $VMHost.ExtensionData.Summary.runtime.inMaintenanceMode

  $row.BootTime = $VMHost.ExtensionData.Summary.runtime.bootTime

  $row.OverallStatus = $VMHost.ExtensionData.Summary.overallStatus

  $row.Version = $VMHost.ExtensionData.Config.Product.version

  $row.Build = $VMHost.ExtensionData.Config.Product.build

  $row.FullName = $VMHost.ExtensionData.Config.Product.fullName

  $row.CpuModel = $VMHost.ExtensionData.Summary.Hardware.CpuModel

  $report += $row

}

$report | export-csv "c:\test.csv" -noTypeInformation-UseCulture

Copy it into powercli and it will output what you are looking for and a bit more. I did not change the sorting because you can do that yourself in excel or If you look at the properties above and the corresponding lines of code you can arrange to your liking.

[meistermn]

Maybe http://www.robware.net/  is faster for what you need?

[meistermn]

Here is a goog a example for rvtools and excel pivot.

http://www.maentz.net/pensieve/yfq0uvj7vp1ybfj5wm8h7e4b9t3gk6