Category: Virtualization

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
}
else{
  $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
}
else{
  $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    
}
else{
  $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
}
else{
  $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}

$WorkBook.EntireColumn.AutoFit()

sleep 5

$Sheet.SaveAs($xlsfile,$xlXLS)
$Sheet.SaveAs($csvfile,$xlCSV) 

Disconnect-VIServer -Confirm:$false

The output will look like this: