8 Replies Latest reply on Jan 21, 2015 5:34 AM by mark.chuman

    Include ESXi host name in csv output

    mark.chuman Hot Shot

      Trying to export AMS version data for a patching event and having trouble figuring out an elegant way to including the ESXi host name in the csv output file.  Any help is appreciated.

       

      ---

       

      $GetHosts = Get-VMHost | Where {($_.ConnectionState -eq “Connected”) -and ($_.Manufacturer -eq "HP")}

      ForEach ($VMHost in $GetHosts) {

      $ESXCLI = Get-EsxCli -VMHost $VMHost

      $version = $ESXCLI.software.vib.list() | Where {$_.Name -eq “hp-ams”} | Select Name, Version | Export-Csv -NoTypeInformation -Append -Force -Path ./output.csv

       

       

      }

       

      Currently the output is this:

       

      NameVersion
      hp-ams550.10.0.1-07.1198610
      hp-ams550.10.0.1-07.1198610
        • 1. Re: Include ESXi host name in csv output
          sneddo Hot Shot

          Try something like this (changes in bold):

           

          $GetHosts = Get-VMHost | Where {($_.ConnectionState -eq “Connected”) -and ($_.Manufacturer -eq "HP")}

          ForEach ($VMHost in $GetHosts) {

             $ESXCLI = Get-EsxCli -VMHost $VMHost

             $ESXCLI.software.vib.list() | Where {$_.Name -eq “hp-ams”} | Select @{Name="VMHost";Expression={$VMHost.Name}}, Name, Version | Export-Csv -NoTypeInformation -Append -Force -Path ./output.csv

          }

          • 2. Re: Include ESXi host name in csv output
            LucD Guru
            vExpertCommunity WarriorsUser Moderators

            You will be overwriting the same CSV for each VMHost I'm afraid

            • 3. Re: Include ESXi host name in csv output
              sneddo Hot Shot

              Not with the -Append parameter....but you will potentially duplicate results if the output file already exists.

              • 4. Re: Include ESXi host name in csv output
                LucD Guru
                User ModeratorsCommunity WarriorsvExpert

                Provided you are running PowerShell v3 or higher

                • 5. Re: Include ESXi host name in csv output
                  Wh33ly Hot Shot

                  Something like this then?

                   

                  $report = @()

                  $GetHosts = Get-VMHost | Where {($_.ConnectionState -eq “Connected”) -and ($_.Manufacturer -eq "HP")

                  foreach($VMhost in $GetHosts){

                  $esxcli = Get-EsxCli -VMHost $VMhost

                  $list = $esxcli.software.vib.list()|?{$_.name -eq "hp-ams"}

                  $row = ""|Select Host,Model,Name,Vendor,Version,Id

                  $list  = $esxcli.software.vib.list()|?{$_.name -eq "hp-ams"} |select @{N="Host";E={$VMhost.Name}},@{N="Model";E={$VMhost.Model}},Name,vendor,version,id

                  $row.Host = $list.Host

                  $row.Model = $list.model

                  $row.Name = $list.Name

                  $row.Vendor = $list.Vendor

                  $row.Version = $list.Version

                  $row.ID = $list.ID

                  $report += $row

                  }

                  $report|sort Host |Export-Csv D:\HP-AMS_Drivers.csv -NoTypeInformation -useculture

                  }

                   

                   

                  ps I noticed when a host returns an error on the ESXCLI command it will give a duplicate entry..still need to look to make the script error free and clean a bit

                  • 6. Re: Include ESXi host name in csv output
                    mark.chuman Hot Shot

                    Looking at this  now guys, but seeing these on the script in the latest reply.

                     

                    PowerCLI C:\Scripts\misc\1_21> .\ams.ps1

                    You cannot call a method on a null-valued expression.

                    At C:\Scripts\misc\1_21\ams.ps1:3 char:1

                    + $list  = $esxcli.software.vib.list()|?{$_.name -eq "hp-ams"} |select @{N="Host"; ...

                    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : InvokeMethodOnNull

                     

                     

                    Property 'Host' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:4 char:1

                    + $row.Host = $list.Host

                    + ~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                     

                     

                    Property 'Model' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:5 char:1

                    + $row.Model = $list.model

                    + ~~~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                     

                     

                    Property 'Name' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:6 char:1

                    + $row.Name = $list.Name

                    + ~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                     

                     

                    Property 'Vendor' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:7 char:1

                    + $row.Vendor = $list.Vendor

                    + ~~~~~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                     

                     

                    Property 'Version' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:8 char:1

                    + $row.Version = $list.Version

                    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                     

                     

                    Property 'ID' cannot be found on this object; make sure it exists and is settable.

                    At C:\Scripts\misc\1_21\ams.ps1:9 char:1

                    + $row.ID = $list.ID

                    + ~~~~~~~~~~~~~~~~~~

                        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

                        + FullyQualifiedErrorId : PropertyNotFound

                    • 7. Re: Include ESXi host name in csv output
                      mark.chuman Hot Shot

                      Still getting property not found errors, but the first error was resolved by adding in $esxcli below in bold.

                       

                      $GetHosts = Get-VMHost | Where {($_.ConnectionState -eq “Connected”) -and ($_.Manufacturer -eq "HP")}

                      foreach($VMhost in $GetHosts){

                      Write-Host $VMhost.Model

                      $esxcli = Get-EsxCli -VMHost $VMHost

                      $list  = $esxcli.software.vib.list()|?{$_.name -eq "hp-ams"} |select @{N="Host";E={$VMhost.Name}},@{N="Model";E={$VMhost.Model}},Name,vendor,version,id

                      $row.Host = $list.Host

                      $row.Model = $list.model

                      $row.Name = $list.Name

                      $row.Vendor = $list.Vendor

                      $row.Version = $list.Version

                      $row.ID = $list.ID

                      $report += $row

                      }

                      $report|sort Host | Export-Csv C:\HP-AMS_Drivers.csv -NoTypeInformation -useculture

                      • 8. Re: Include ESXi host name in csv output
                        mark.chuman Hot Shot

                        First script worked well guys, so moving on to the next thing on my list .  Many thanks!  I'll post this