VMware Cloud Community
juliennas
Contributor
Contributor
‎12-04-2012 10:32 PM
Jump to solution

Powercli to report VM Nics AdapterType

Hi all,

I'm trying to make a report of my VMs (400). I'm managing 2 Vcenters in linked mode, and I ve seen some errors in the VM creation process, e1000, Vmxnet2 cards on servers.

I use Powershell/Powercli for a couple of years from now, but I m facing some problems ... If servers have more than 1 NIC, my report just write 1 card ...

If you could help me, I guess my script is not correctly written ...

Here it is :

##### CREATE EXCEL #####
$Excel = New-Object -Com Excel.Application
$Excel.visible = $true
$Excel = $Excel.Workbooks.Add(1)

$VM = $Excel.WorkSheets.Item(1)
$VM.Name = "Network Type"
$VM.Cells.Item(1,1) = "VM Name"
$VM.Cells.Item(1,2) = "OS Name"
$VM.Cells.Item(1,3) = "IP Address"
$VM.Cells.Item(1,4) = "VM HOST"
$VM.Cells.Item(1,5) = "VM STATUS"
$VM.Cells.Item(1,6) = "Adapter Type"
$VM.Cells.Item(1,7) = "Adapter Name"
$VM.Cells.Item(1,8) = "DataCenter"


$WorkBook = $VM.UsedRange
$WorkBook.Interior.ColorIndex = 0
$WorkBook.Font.ColorIndex = 5
$WorkBook.Font.Bold = $True
$WorkBook.HorizontalAlignment = -4108
$VmRange = $VM.range("A1:Z500")
$Vmrange.font.size = 8
$intRow = 2

##### CONNECTING VCENTER #####
Connect-VIServer -server $VCenter -User $User -Password $PWD -AllLinked:$true

$VMs = Get-VM  | Sort-Object Name

Foreach ($ColVM in $VMs) {
    #Gathering Info on $ColVM   
    $VMsItems     = Get-VM -Name $ColVM | Select Name, VMHost, PowerState,
            @{N="OSFullName";E={($_ | Get-VMGuest).OSFullName}},
            @{N="IPAddress";E={($_ | Get-VMGuest).IPAddress}},
            @{N="AdapterType";E={($_ | Get-NetworkAdapter).Type}},
            @{N="AdapterName";E={($_ | Get-NetworkAdapter).Name}},
            @{N="Datacenter";E={($_ | Get-Datacenter).Name}},
            @{N="CardCount";E={($_ | Get-NetworkAdapter).Count}}

    #Filling Excel Report
    Foreach ($Name in ($VMsItems.Name)){
        $VM.Cells.Item($intRow,1) = [STRING]$Name.Name
        $VM.Cells.Item($intRow,2) = [STRING]$Name.OSFullName
        $VM.Cells.Item($intRow,3) = [STRING]$Name.IPAddress
        $VM.Cells.Item($intRow,4) = [STRING]$Name.VMHost
        $VM.Cells.Item($intRow,5) = [STRING]$Name.PowerState
            If        ($VMsItems.PowerState -eq "PoweredOn"){$VM.Cells.Item($intRow,5).Interior.ColorIndex = 4}
            Else    {$VM.Cells.Item($intRow,5).Interior.ColorIndex = 3}
        $VM.Cells.Item($intRow,8) = [STRING]$Name.Datacenter
        $VM.Cells.Item($intRow,6) = [STRING]$Name.AdapterType
            If        ($Name.AdapterType -eq "Vmxnet3")    {$VM.Cells.Item($intRow,6).Interior.ColorIndex = 4}
            Else     {$VM.Cells.Item($intRow,6).Interior.ColorIndex = 3}
        $VM.Cells.Item($intRow,7) = [STRING]$Name.AdapterName
        $IntRow ++    }
    }   
$WorkBook.EntireColumn.AutoFit()
$VM.Columns.Item(2).ColumnWidth = 25

##### SAUVEGARDE #####
$Excel.SaveAs($FILENAME)

##### DECONNEXION VCENTER & KILL EXCEL PROCESS #####
Disconnect-VIServer -server * -Confirm:$false -Force:$true
#Stop-Process -processname excel

Thanks,

Julien

0 Kudos
29 Replies
LucD
Leadership
Leadership
‎12-06-2012 02:14 AM
Jump to solution

Did you also try

$VMs =  Get-ResourcePool -Name "REC" | Get-VM

That should only give you the VMs in that specific resourcepool.

And you can have more than 1 name on the -Name parameter


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
juliennas
Contributor
Contributor
‎12-06-2012 02:39 AM
Jump to solution

LucD,

It looks better now Smiley Happy

here is the full script, if you see some way to make it run faster ... it's quite long (20-25 minutes) for 400 VMs

Thanks a lot for your help and the time spent on this  Smiley Wink


CLS
#Init
$VCenter         = ""
$User              = ""
$PWD             = ""
$Date              = Get-Date -format yyyy-MM-dd
$ExportPath    = "C:\"
$ReportName   = $Date  + "--VMsNicTypeReport.xls"
$FILENAME     = $ExportPath + $ReportName

##### CREATE EXCEL #####
$Excel = New-Object -Com Excel.Application
$Excel.visible = $false
$Excel = $Excel.Workbooks.Add(1)

$VM = $Excel.WorkSheets.Item(1)
$VM.Name = "Network Type"
$VM.Cells.Item(1,1) = "VM Name"
$VM.Cells.Item(1,2) = "OS Name"
$VM.Cells.Item(1,3) = "IP Address"
$VM.Cells.Item(1,4) = "VM HOST"
$VM.Cells.Item(1,5) = "VM STATUS"
$VM.Cells.Item(1,6) = "Adapter Type"
$VM.Cells.Item(1,7) = "Adapter Name"
$VM.Cells.Item(1,8) = "Folder"
$VM.Cells.Item(1,9) = "DataCenter"

$WorkBook = $VM.UsedRange
$WorkBook.Interior.ColorIndex = 0
$WorkBook.Font.ColorIndex = 5
$WorkBook.Font.Bold = $True
$WorkBook.HorizontalAlignment = -4108
$VmRange = $VM.range("A1:Z500")
$Vmrange.font.size = 8
$IntRow = 2

##### Functions          #####
Function ExcelReport {
#Gathering Info on $ColVM   
    $VMsItems     = Get-VM -Name $ColVM | Select Name, VMHost, PowerState,
            @{N="OSFullName"    ;E={($_ | Get-VMGuest).OSFullName}},
            @{N="IPAddress"        ;E={($_ | Get-VMGuest).IPAddress}},
            @{N="Folder"        ;E={$_.Folder.Name}},
            @{N="AdapterName"    ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Name})))}},
            @{N="AdapterType"    ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Type})))}}
       
#Filling Excel Report
    $VM.Cells.Item($IntRow,1) = $ColVM.Name
    $VM.Cells.Item($IntRow,2) = [STRING]$VMsItems.OSFullName
    $VM.Cells.Item($IntRow,3) = [STRING]$VMsItems.IPAddress
    $VM.Cells.Item($IntRow,4) = [STRING]$ColVM.VMHost
    $VM.Cells.Item($IntRow,5) = [STRING]$ColVM.PowerState
        If        ($ColVM.PowerState -eq "PoweredOn")            {$VM.Cells.Item($IntRow,5).Interior.ColorIndex = 4}
        Else    {$VM.Cells.Item($IntRow,5).Interior.ColorIndex = 3}
    $VM.Cells.Item($IntRow,6) = [STRING]$VMsItems.AdapterType
        If        ($VMsItems.AdapterType -like "*Vmxnet3*")    {$VM.Cells.Item($IntRow,6).Interior.ColorIndex = 4}
        Else     {$VM.Cells.Item($IntRow,6).Interior.ColorIndex = 3}
    $VM.Cells.Item($IntRow,7) = [STRING]$VMsItems.AdapterName
    $VM.Cells.Item($IntRow,8) = $VMsItems.Folder
    $VM.Cells.Item($IntRow,9) = $DataCenter.Name }

##### CONNECTING VCENTER #####
Connect-VIServer -server $VCenter -User $User -Password $PWD
$DataCenters     = Get-Datacenter | Sort-Object Name

Foreach ($DataCenter in $DataCenters){
    If         ($DataCenter -match "Datacenter1")    {
        $VMs = Get-ResourcePool -Location $DataCenter -Name "REC" | Get-VM | Sort Name
        Foreach ($ColVM in $VMs) { ExcelReport ; $IntRow ++ }}
    ElseIf    ($DataCenter -match "Datacenter2")    {
        $VMs = Get-VM -Location $DataCenter | Sort Name
        Foreach ($ColVM in $VMs) { ExcelReport ; $IntRow ++ }}
}
$WorkBook.EntireColumn.AutoFit()
$VM.Columns.Item(2).ColumnWidth = 25

##### Saving Report #####
$Excel.SaveAs($FILENAME)

##### Disconnect VCs & Kill Excel Process #####
Disconnect-VIServer -server * -Confirm:$false -Force:$true
Stop-Process -processname excel

0 Kudos
RvdNieuwendijk
Leadership
Leadership
‎12-06-2012 02:57 AM
Jump to solution

You can make your script faster by doing a calculation only once and by using properties instead of cmdlets. Try changing:

    $VMsItems     = Get-VM -Name $ColVM | Select Name, VMHost, PowerState, 
            @{N="OSFullName"    ;E={($_ | Get-VMGuest).OSFullName}},
            @{N="IPAddress"        ;E={($_ | Get-VMGuest).IPAddress}},
            @{N="Folder"        ;E={$_.Folder.Name}},
            @{N="AdapterName"    ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Name})))}},
            @{N="AdapterType"    ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Type})))}}

 
 

into:

    $VMs = Get-VM -Name $ColVM
    $NetworkAdapters = $VMs | Get-NetworkAdapter
    $VMsItems     = $VMs | Select Name, VMHost, PowerState, 
            @{N="OSFullName"    ;E={$_.Guest.OSFullName}},
            @{N="IPAddress"     ;E={$_.Guest.IPAddress}},
            @{N="Folder"        ;E={$_.Folder.Name}},
            @{N="AdapterName"   ;E={([STRING]::JOIN(',',($NetworkAdapters | %{$_.Name})))}},
            @{N="AdapterType"   ;E={([STRING]::JOIN(',',($NetworkAdapters | %{$_.Type})))}}

Blog: https://rvdnieuwendijk.com/ | Twitter: @rvdnieuwendijk | Author of: https://www.packtpub.com/virtualization-and-cloud/learning-powercli-second-edition
0 Kudos
LucD
Leadership
Leadership
‎12-06-2012 02:59 AM
Jump to solution

You can change the function like this

Function ExcelReport {
  param(
    [VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImpl]$vm
  )
  
  #Gathering Info on $vm   
  $VMsItems = Select -InputObject $vm -Property Name, VMHost, PowerState,
  @{N="OSFullName" ;E={($_ | Get-VMGuest).OSFullName}},
  @{N="IPAddress" ;E={($_ | Get-VMGuest).IPAddress}},
  @{N="Folder" ;E={$_.Folder.Name}},
  @{N="AdapterName" ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Name})))}},
  @{N="AdapterType" ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Type})))}}

  #Filling Excel Report
  $VM.Cells.Item($IntRow,1) = $vm.Name
  $VM.Cells.Item($IntRow,2) = [STRING]$VMsItems.OSFullName
  $VM.Cells.Item($IntRow,3) = [STRING]$VMsItems.IPAddress
  $VM.Cells.Item($IntRow,4) = [STRING]$vm.VMHost
  $VM.Cells.Item($IntRow,5) = [STRING]$vm.PowerState
  If ($vm.PowerState -eq "PoweredOn") {$VM.Cells.Item($IntRow,5).Interior.ColorIndex = 4}
  Else {$VM.Cells.Item($IntRow,5).Interior.ColorIndex = 3}
  $VM.Cells.Item($IntRow,6) = [STRING]$VMsItems.AdapterType
  If ($VMsItems.AdapterType -like "*Vmxnet3*") {$VM.Cells.Item($IntRow,6).Interior.ColorIndex = 4}
  Else {$VM.Cells.Item($IntRow,6).Interior.ColorIndex = 3}
  $VM.Cells.Item($IntRow,7) = [STRING]$VMsItems.AdapterName
  $VM.Cells.Item($IntRow,8) = $VMsItems.Folder
  $VM.Cells.Item($IntRow,9) = $DataCenter.Name 
}

That will at least save you a Get-VM for each VM you pass to the function.

But the biggest gain can be achieved by creating the XLS with my Export-Xls function I mentioned earlier.

Doing it cell by cell is very slow (as you have noticed).


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
juliennas
Contributor
Contributor
‎12-06-2012 05:23 AM
Jump to solution

Thanks for all those remarks, I'm currently testing all these new ways to run the script faster, even your excel function LucD.

Julien

0 Kudos
juliennas
Contributor
Contributor
‎12-06-2012 05:31 AM
Jump to solution

LucD,

It seems I do not have the module 

VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImp 

Where is it from ? Powercli 5 by defaut or somewhere else ? 

Julien
0 Kudos
LucD
Leadership
Leadership
‎12-06-2012 05:39 AM
Jump to solution

There was an L missing, it's corrected now.

It should say

VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImpl

And that is the objecttype for objects returned by Get-VM, its part of PowerCLI


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
juliennas
Contributor
Contributor
‎12-10-2012 03:08 AM
Jump to solution

Hi LucD,

Sorry to bother you again, but I have a new problem on the script.

My Excel report is not filled by the command ...

I've made some changes to get all VMs, not just VM in specific resource pool. I was asked to ...

So now, I get all VMs, and foreach VMs, I enter the function ExcelReport... All infos on the VM are gathering correctly (I can see if I put my mouse on $VMsItems in PowerGui). Even just a write-host $VMsItems.name in the 1st line in the filling report shows nothing ...

If you can have a look, because now, I guess I can't see where is located the error ...

param( [VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImpl]$VM ) makes the script runs in less than 5 minutes ! Great Smiley Happy

I'm also checking on a way to include your Excel function in my script, so it can run faster Smiley Wink

CLS
##### Function #####
Function ExcelReport {
  param( [VMware.VimAutomation.ViCore.Impl.V1.Inventory.VirtualMachineImpl]$VM )
  # Gathering Info on $VM #
  $VMsItems = Select -InputObject $VM -Property  Name, VMHost, Powerstate, Folder, Notes,
              @{N="OSFullName"     ;E={$_.Guest.OSFullName}},
            @{N="IPAddress"     ;E={$_.Guest.IPAddress}},
            @{N="Datacenter"     ;E={$_.Datacenter.Name}},
            @{N="AdapterName"     ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Name})))}},
            @{N="AdapterType"     ;E={([STRING]::JOIN(',',($_ | Get-NetworkAdapter | %{$_.Type})))}}

  # Filling Excel Report #
  $VMExcel.Cells.Item($IntRow,1) = $VMsItems.Name
  $VMExcel.Cells.Item($IntRow,2) = $VMsItems.OSFullName
  $VMExcel.Cells.Item($IntRow,3) = $VMsItems.IPAddress
  $VMExcel.Cells.Item($IntRow,4) = $VMsItems.VMHost
  $VMExcel.Cells.Item($IntRow,5) = $VMsItems.PowerState
      If ($VMsItems.PowerState -eq "PoweredOn")     {$VMExcel.Cells.Item($IntRow,5).Interior.ColorIndex = 4}
      Else                                             {$VMExcel.Cells.Item($IntRow,5).Interior.ColorIndex = 3}
  $VMExcel.Cells.Item($IntRow,6) = $VMsItems.AdapterType
      If ($VMsItems.AdapterType -like "*Vmxnet3*")    {$VMExcel.Cells.Item($IntRow,6).Interior.ColorIndex = 4}
      Else                                             {$VMExcel.Cells.Item($IntRow,6).Interior.ColorIndex = 3}
  $VMExcel.Cells.Item($IntRow,7) = $VMsItems.AdapterName
  $VMExcel.Cells.Item($IntRow,8) = $VMsItems.Folder
  $VMExcel.Cells.Item($IntRow,9) = $VMsItems.Datacenter
  $VMExcel.Cells.Item($IntRow,10)= $VMsItems.Notes }
 
##### Parameters #####
$VCenter         = ""
$User             = ""
$PWD            = ""
$Date            = Get-Date -format yyyy-MM-dd
$ExportPath        = "C:\"
$ReportName        = $Date  + "--VMsNicTypeReport.xls"
$FILENAME        = $ExportPath + $ReportName
$IntRow         = 2

##### Create Excel Report #####
$Excel             = New-Object -Com Excel.Application
$Excel.visible     = $true
$Excel             = $Excel.Workbooks.Add(1)

$VMExcel                  = $Excel.WorkSheets.Item(1)
$VMExcel.Name              = "Network Type"
$VMExcel.Cells.Item(1,1) = "VM Name"
$VMExcel.Cells.Item(1,2) = "OS Name"
$VMExcel.Cells.Item(1,3) = "IP Address"
$VMExcel.Cells.Item(1,4) = "ESX HOST"
$VMExcel.Cells.Item(1,5) = "VM Status"
$VMExcel.Cells.Item(1,6) = "Adapter Type"
$VMExcel.Cells.Item(1,7) = "Adapter Name"
$VMExcel.Cells.Item(1,8) = "Folder"
$VMExcel.Cells.Item(1,9) = "DataCenter"
$VMExcel.Cells.Item(1,10)= "Description"

$WorkBook                         = $VMExcel.UsedRange
$WorkBook.Interior.ColorIndex     = 0
$WorkBook.Font.ColorIndex         = 5
$WorkBook.Font.Bold             = $True
$WorkBook.HorizontalAlignment     = -4108
$VmRange                         = $VMExcel.Range("A1:Z500")
$VmRange.Font.Size                 = 8

##### Start Of the Script #####
    # Connecting VC #
Connect-VIServer $VCenter -User $User -Password $PWD #-AllLinked:$true
    # Getting VMs in VC #
$VMs    = Get-VM | Sort Name
Foreach ($VM in $VMs) { ExcelReport ; $intRow ++ }

    # Disconnecting VC #
Disconnect-VIServer * -Confirm:$false -Force    | Out-Null

    # Arrange & Save Report #
$WorkBook.EntireColumn.AutoFit()
$VMExcel.Columns.Item(2).ColumnWidth = 25
$Excel.SaveAs($FILENAME) | Out-Null
Sleep -Seconds 5
#Stop-Process -processname excel
##### End Of the Script #####

0 Kudos
LucD
Leadership
Leadership
‎12-10-2012 04:04 AM
Jump to solution

You should pass a VM to the function.

This

Foreach ($VM in $VMs) { ExcelReport ; $intRow ++ }

should be

Foreach ($VM in $VMs) { ExcelReport -VM $VM; $intRow ++ }


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
juliennas
Contributor
Contributor
‎12-10-2012 05:21 AM
Jump to solution

Just Great !!

That's it, thanks LucD

0 Kudos