Skip to content

Josh's IT-Blog

Information Technology, and other interesting things …

  • Home
  • About
  • Contact
  • Links

discover informations about all virtual machines to Excel

Posted on 29. December 201129. December 2011 By Burkard Josh No Comments on discover informations about all virtual machines to Excel
VMware

I had the task to list all existing virtual machines in our vSphere vCenter to an Excel-List. Cause i have alot of VM’s and they change often, i wrote a powershell-script, which creates an excel-file like this:

The requirements for this script are Microsoft Excel and VMware PowerCLI 5. Look at the script or download it here:

# ===========================================================================================
#
#  Script Information
#
#  Title:              create-vmOverview.ps1
#  Author:             Josh Burkard
#  Date:               29.12.2011
#
#  Requirements:       - Windows Powershell
#                      - vmWare PowerCLI V5
#                      - Microsoft Excel
#
#  Usage:              .\create-vmOverview.ps1
#
#  Description:        Get Informations from all existing VM's in a vCenter and writes it
#                      to an Excel-File
#
#===========================================================================================

# VARIABLES
$VCServer = "vCenter Name or IP-Address"    # Virtual Center Server

$outputFile = "D:\VMDiskOverview_" + $VCServer + ".xlsx"   # Output file path

$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]

# SCRIPT

# Add the VI-Snapin if it isn't loaded already
if ((Get-PSSnapin -Name "VMware.VimAutomation.Core" -ErrorAction SilentlyContinue) -eq $null )
{
	Add-PSSnapin -Name "VMware.VimAutomation.Core"
}

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$WorkBook = $objExcel.Workbooks.Add()
$WorkSheet = $WorkBook.Worksheets.Item(1)
$WorkSheet.Cells.Item(1, 1) = "DataCenter:"
$WorkSheet.Cells.Item(1, 2) = "VM:"
$WorkSheet.Cells.Item(1, 3) = "Description:"
$WorkSheet.Cells.Item(1, 4) = "Disk:"
$WorkSheet.Cells.Item(1, 5) = "DiskType:"
$WorkSheet.Cells.Item(1, 6) = "DataStore:"
$WorkSheet.Cells.Item(1, 7) = "DSFolder:"
$WorkSheet.Cells.Item(1, 8) = "CapacyityGB:"
$WorkSheet.Cells.Item(1, 9) = "vCPU:"
$WorkSheet.Cells.Item(1,10) = "vRAM:"

$intRow = 2

Clear
$VC = Connect-VIServer $VCServer    # Connect to Virtual Center
$myCol = @()    # Prepare output collection
$VMs = Get-VM | Sort Name    # Get all VMs (sorted)
$counter = 0    # Initialize counter for progress bar
ForEach ($Datacenter in Get-DataCenter)
{
	ForEach ($VM in Get-DataCenter $DataCenter | Get-VM | Sort Name)    # Loop through VMs
	{
		$counter++    # Increase counter for progress bar
		Write-Progress -Activity "Gathering disk information" -Status "Processing VM $VM" -PercentComplete (100*($counter/$VMs.count))    # Display progress bar
		$VMHost = Get-VMHost -VM $VM    # Get this VM's host
		$VMHostView = $VMHost | Get-View    # Get advanced properties of host

		$WorkSheet.Cells.Item($intRow, 1) = $DataCenter.Name    							# DataCenter Name
		$WorkSheet.Cells.Item($intRow, 2) = $VM.Name    									# Virtual Machine Name
		$WorkSheet.Cells.Item($intRow, 3) = $VM.Notes     									# Virtual Machine Description
		$WorkSheet.Cells.Item($intRow, 9) = $VM.NumCpu										# NumCpu
		$WorkSheet.Cells.Item($intRow,10) = $VM.MemoryMB									# MemoryMB

		$intDisks = 0
		ForEach ($DISK in Get-Harddisk -VM $VM)    # Loop through VM's harddisks
		{
			$WorkSheet.Cells.Item($intRow + $intDisks, 4) = $DISK.Name    										# Virtual Disk Name
			$WorkSheet.Cells.Item($intRow + $intDisks, 5) = ""+$DISK.DiskType
			If ($DISK.DiskType -eq "RawPhysical")
			{
				$Lun = Get-SCSILun $DISK.SCSICanonicalName -VMHost (Get-VM $VM).VMHost
				$WorkSheet.Cells.Item($intRow + $intDisks, 6) = $Lun.CanonicalName	    						# Datastore Name
				$WorkSheet.Cells.Item($intRow + $intDisks, 7) = ""
			} else {
				$DataStoreName = $DISK.Filename.Split("[]")[1]
				$WorkSheet.Cells.Item($intRow + $intDisks, 6) = $DataStoreName									# Datastore Name
				$DS = Get-Datastore -Name $DataStoreName
				If ($DS.ParentFolder.Name -eq "datastore")
				{
					$WorkSheet.Cells.Item($intRow + $intDisks, 7) = ""											# DSFolder
				} else {
					$WorkSheet.Cells.Item($intRow + $intDisks, 7) = $DS.ParentFolder.Name						# DSFolder
				}
			}
			$WorkSheet.Cells.Item($intRow + $intDisks, 8) = $DISK.CapacityKB / 1024 / 1024						# CapacityKB

			Clear-Variable DISK -ErrorAction SilentlyContinue

			$intDisks = $intDisks + 1
		}
		if ($intDisks -gt 1)
		{
			$intLastRow = $intRow + $intDisks - 1
			$MergeCells = $WorkSheet.Range("A" + $intRow + ":A" + $intLastRow)
			$MergeCells.Merge()

			$MergeCells = $WorkSheet.Range("B" + $intRow + ":B" + $intLastRow)
			$MergeCells.Merge()

			$MergeCells = $WorkSheet.Range("C" + $intRow + ":C" + $intLastRow)
			$MergeCells.Merge()

			$MergeCells = $WorkSheet.Range("I" + $intRow + ":I" + $intLastRow)
			$MergeCells.Merge()

			$MergeCells = $WorkSheet.Range("J" + $intRow + ":J" + $intLastRow)
			$MergeCells.Merge()
		}
		$intRow = $intRow + $intDisks
	}
}

$WorkSheet.Range("A1:J1").Font.Bold = $true
$WorkSheet.Range("A1:G" + $intRow).HorizontalAlignment = $xlConstants::xlLeft
$WorkSheet.Range("A1:J" + $intRow).VerticalAlignment = $xlConstants::xlTop
$WorkSheet.Range("H2:H" + $intRow).Numberformat="0.00"
$WorkSheet.application.activewindow.splitcolumn = 2
$WorkSheet.application.activewindow.splitrow = 1
$WorkSheet.application.activewindow.freezepanes = $true

$Range = $WorkSheet.Range("A1:J1")
$Range.EntireColumn.AutoFit() | Out-Null
$WorkBook.SaveAs($outputFile)
$objExcel.Workbooks.close()
$objExcel.Quit()

Share this:

  • Facebook
  • Twitter
  • Email
  • Print
Tags: PowerShell VMware

Post navigation

❮ Previous Post: Collect warranty informations with SCCM
Next Post: Installing HP ESXi Offline Bundle for VMware ESXi 5.0 ❯

Leave a Reply Cancel reply

About

Author Image
My name is Josh Burkard.
I'm a DevOps Engineer working with one of swiss largest telecom and full-service hosting provider. in my work I have a lot to do with Microsoft server operating systems, System Center, VMware, Microsoft Azure Cloud and other software.
On this site I will write some posts about different technology problems and their solutions.
please note also my tweets and retweets from this area.

Follow me on Twitter

My Tweets

Categories

  • General (13)
  • Hardware (9)
    • Network (8)
      • Cisco (2)
    • Storage (2)
  • Microsoft Azure (1)
    • Automation (1)
  • PowerShell (1)
  • Software (1)
    • Excel (1)
  • System Center (19)
    • SCCM (3)
    • SCDPM (1)
    • SCOM (13)
    • SCSM (1)
    • SMA (1)
  • VMware (8)
  • Windows 2008 R2 (10)
    • Active Directory (7)
  • Windows 2012 R2 (1)
  • Windows 2016 (1)
  • Windows 7 (4)
    • BitLocker (1)
  • WordPress (1)

Links

  • Burkard-Fingerlin Family
  • Swisscom (Schweiz) AG
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

About

Author Image
My name is Josh Burkard.
I'm a DevOps Engineer working with one of swiss largest telecom and full-service hosting provider. in my work I have a lot to do with Microsoft server operating systems, System Center, VMware, Microsoft Azure Cloud and other software.
On this site I will write some posts about different technology problems and their solutions.
please note also my tweets and retweets from this area.

Follow me on Twitter

My Tweets

FOLLOW ME ON GITHUB

joshburkard (Josh Burkard)

Josh Burkard

joshburkard
Belgium
https://www.burkard.it
Joined on Jul 10, 2015
13 Public Repositories
0 Public Gists

Copyright © 2023 Josh's IT-Blog.

Theme: Oceanly by ScriptsTown

 

Loading Comments...
 

    loading Cancel
    Post was not sent - check your email addresses!
    Email check failed, please try again
    Sorry, your blog cannot share posts by email.