PowerCLI: Virtual Machine Disk (VMDK) info v2: Analyze data with Excel


In my previous post about this subject I created a script to view the information on the PowerCLI console. With the v2 script you are able to export the data to a CSV file.

$myCol = @()
$vms = get-view -ViewType VirtualMachine  | Where-Object `
{-not $_.config.template} 
foreach($vm in $vms){
    foreach($dev in $vm.config.hardware.Device){
    $MYInfo = "" | Select-Object VMName, DeviceLabel, `
    FileName, DiskMode, ThinProvisioned

       $MYInfo.VMName = $vm.Name

           if($dev.GetType().Name -eq "VirtualDisk"){
                   $MYInfo.DeviceLabel = $dev.DeviceInfo.Label
                $MYInfo.FileName = $dev.Backing.FileName
                $MYInfo.DiskMode = $dev.Backing.DiskMode
                $MYInfo.ThinProvisioned = "True"}
                else{$MYInfo.ThinProvisioned = "False"}

                $myCol += $MYInfo
$myCol | Export-CSV -NoTypeInformation "D:\scripts\vmdkinfo.csv"

When the script is finished, you can import the CSV file into Excel.  After the import, we can analyze the data with just a simple filter. With a few clicks,  you’re able to view al the VM’s without Thin Provisioned disks.


Now we have a list with all the VM’s with Thin Provisioned disks:


So with a small PowerCLI script and the help of Microsoft Excel, you’re able to generate a report with just the information you need. The best part is that it will only cost you couple of minutes of your time 🙂 .

Powershell: Working with Excel and error HRESULT: 0x80028018


If you want to use Microsoft Excel in your Powershell scripts.

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()


You can run into the following error:


The work around for this issue was changing the Regional Options back to United States International.


Microsft created a work around. More info can be found in in: KB320369

guillermooo has created a port to Powershell. Just copy the following code and you should be able to open Excel and create a new workbook.

I didn’t test this script block so let me know if it works 😉

$ci = new-object system.globalization.cultureinfo "en-US"

$e = New-Object -COM "Excel.Application"
$e.Visible = $True
$e.UserControl= $True
$books = $e.Workbooks
$books.PSBase.GetType().InvokeMember( `
       "Add", `
       [system.reflection.bindingflags]::InvokeMethod, `
       $null, $books, $null, $ci)

Source: http://stackoverflow.com/questions/687891/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add

PowerCLI: Generate an Excel sheet with VM info


The following script is part of another script but I wanted to show you some nice Powershell stuff in combination with Microsoft Excel.

This script will generate an Excel sheet with some VM information. It will color the cell red if the Powerstate equals to NotRunning.

$vCenter = Read-Host "Enter your vCenter servername"

Connect-VIServer $vCenter

$xlCSV = 6
$xlXLS = 56
$csvfile = "C:\beforeHWchange.csv"
$xlsfile = "C:\beforeHWchange.xls"

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)
$Sheet.Cells.Item(1,1) = "Status"
$Sheet.Cells.Item(1,2) = "VMName"
$Sheet.Cells.Item(1,3) = "VMHostname"
$Sheet.Cells.Item(1,4) = "IPAddress"
$Sheet.Cells.Item(1,5) = "MacAddress"
$Sheet.Cells.Item(1,6) = "TotalNics"
$Sheet.Cells.Item(1,7) = "vNicType"
$Sheet.Cells.Item(1,8) = "NetworkName"
$Sheet.Cells.Item(1,9) = "vNicConnected"
$Sheet.Cells.Item(1,10) = "ToolsVersion"
$Sheet.Cells.Item(1,11) = "ToolsStatus"
$Sheet.Cells.Item(1,12) = "ToolsRunningStatus"
$Sheet.Cells.Item(1,13) = "OS"
$Sheet.Cells.Item(1,14) = "ESXHost"

$intRow = 2

$WorkBook = $Sheet.UsedRange
$WorkBook.Interior.ColorIndex = 19
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

#$vms = Get-Folder Lab | Get-VM
$vms = Get-VM

foreach($vm in $vms){

  $vmnic = Get-NetworkAdapter -VM $vm
  $vmview = get-VM $vm | Get-View

if($vm.Guest.State -eq "NotRunning"){
  $Sheet.Cells.Item($intRow, 1) = [String]$vm.Guest.State
  $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 3
elseif($vm.Guest.State -eq "Unknown"){
  $Sheet.Cells.Item($intRow, 1) = [String]$vm.Guest.State
  $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 48
  $Sheet.Cells.Item($intRow, 1) = [String]$vm.Guest.State
  $Sheet.Cells.Item($intRow, 1).Interior.ColorIndex = 4

$Sheet.Cells.Item($intRow, 2) = $vmview.Name 
$Sheet.Cells.Item($intRow, 3) = $vmview.Guest.HostName
$Sheet.Cells.Item($intRow, 4) = [String]$vm.Guest.IPAddress
$Sheet.Cells.Item($intRow, 5) = $vmnic.MacAddress
$Sheet.Cells.Item($intRow, 6) = $vmview.Guest.Net.Count
$Sheet.Cells.Item($intRow, 7) = [String]$vmnic.Type
$Sheet.Cells.Item($intRow, 8) = $vmnic.NetworkName 
$Sheet.Cells.Item($intRow, 9) = $vmnic.ConnectionState.Connected

if($vmview.Config.Tools.ToolsVersion -eq "8193"){
  $Sheet.Cells.Item($intRow, 10) = [String]$vmview.Config.Tools.ToolsVersion
  $Sheet.Cells.Item($intRow, 10).Interior.ColorIndex = 4
  $Sheet.Cells.Item($intRow, 10) = [String]$vmview.Config.Tools.ToolsVersion
  $Sheet.Cells.Item($intRow, 10).Interior.ColorIndex = 3

if($vmview.Guest.ToolsStatus -eq "toolsNotInstalled"){
  $Sheet.Cells.Item($intRow, 11) = [String]$vmview.Guest.ToolsStatus
  $Sheet.Cells.Item($intRow, 11).Interior.ColorIndex = 48

elseif($vmview.Guest.ToolsStatus -eq "toolsNotRunning"){
  $Sheet.Cells.Item($intRow, 11) = [String]$vmview.Guest.ToolsStatus
  $Sheet.Cells.Item($intRow, 11).Interior.ColorIndex = 3    
elseif($vmview.Guest.ToolsStatus -eq "toolsOld"){
  $Sheet.Cells.Item($intRow, 10) = [String]$vmview.Config.Tools.ToolsVersion
  $Sheet.Cells.Item($intRow, 10).Interior.ColorIndex = 45
  $Sheet.Cells.Item($intRow, 11) = [String]$vmview.Guest.ToolsStatus
  $Sheet.Cells.Item($intRow, 11).Interior.ColorIndex = 45    
  $Sheet.Cells.Item($intRow, 11) = [String]$vmview.Guest.ToolsStatus
  $Sheet.Cells.Item($intRow, 11).Interior.ColorIndex = 4

if($vmview.Guest.ToolsRunningStatus -eq "guestToolsRunning"){
  $Sheet.Cells.Item($intRow, 12) = $vmview.Guest.ToolsRunningStatus
  $Sheet.Cells.Item($intRow, 12).Interior.ColorIndex = 4
  $Sheet.Cells.Item($intRow, 12) = $vmview.Guest.ToolsRunningStatus
  $Sheet.Cells.Item($intRow, 12).Interior.ColorIndex = 3

$Sheet.Cells.Item($intRow, 13) = $vmview.Guest.GuestFamily
$Sheet.Cells.Item($intRow, 14) = $vm.Host.Name

$intRow = $intRow + 1}


sleep 5


Disconnect-VIServer -Confirm:$false


The output will look like this:


VMware: VM Disk space calculator

This post is a copy from Cody Bunch his blogpost. Cody thanks for sharing!!!:

This is a tool that I created a while back to assist in sizing needed disk space in a deployment. Straight forward to use, the totals are calculated as follows: VMDK Size + Ram Size * 1.1 + 12Gb = Total Needed. While the VMDK may be obvious, the others are just as important. Ram Size is included, as ESX will create a swap file on the disk where the VM’s configuration resides (unless you specify otherwise) and needs to be included.  The * 1.1 is to add 10% to the overall solution, to allow for snapshots. This can likely be adjusted up or down depending on your specific requirements, but I’ve found that at least 10% works best.  The last number, 12GB. This one may seem like a mystery, and likely it is. This was originally included to allow for the ESX install on a local host, and some additional room for it’s related overhead.  If your VM’s are on SAN, this number can likely be left out, but… a little more overhead never hurt anyone.


Download link and source: http://professionalvmware.com/2008/12/08/virtual-machine-disk-sizing-tool/