1 2 3 Previous Next 38 Replies Latest reply on May 10, 2012 10:12 AM by bradley4681

    Combine Script ouput of ESX, VM, Network, Storage and output into Excel

    Sureshadmin Hot Shot

      hi,

       

      I have 6 scripts(written by Luc and Robert) which are used to extract information from ESX box as given below. I want to combine the scripts into one and output into excel. I have a csv file with esx server names in it. The combined script should take input of esx server name from csv and run all these scripts and export the information to one excel sheet. I need one excel workbook with each esx server as a sheet in it.

       

      ESX-report.ps1 ---> Extract esx host info

      VM-report.ps1-----> Extract VM info

      pnic-report.ps1----> Extract ESX physical nic info

      portgroup-report.ps1-->Extract portgroup info

      scsiHBA-report.ps1-->Extract SCSI & HBA info

      Datastore report.ps1 -->Extract Datastore report info

      Firewall-report.ps1------> Need to be written(have given format in attached excel sheet)

      Timeservers-report.ps1-->Need to be written(have given format in attached excel sheet)

      DNSserver-report.ps1---->Need to be written(have given format in attached excel sheet)

       

      I have attached the format of the expected output excel workbook and the scripts.

       

      Thanks in advance!

        • 1. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
          LucD Guru
          User ModeratorsvExpertCommunity Warriors

          A first attempt.

          The script will create a XLS file with each report in a separate worksheet.

          The 3 reports that you didn't include yet, currently produce blank sheets.

           

          The input CSV file expects the ESX hosts in this format

          Name
          esx1
          esx2
          ...
          

           

          Give it a try and let me know if it is usable in this format ?

          function Copy-CSVtoSheet{
               param($to, $CSVname)
          
               $csvBook = $excelApp.Workbooks.Open($CSVname)
               $csvSheet = $csvBook.Worksheets.Item(1)
               $csvSheet.UsedRange.Copy()
               $to.Paste()
               $csvbook.Application.CutCopyMode = $false
               $csvBook.Close($false,$null,$null)
          }
          
          $inputCsv = "C:\vmhosts.csv"
          $myHosts = Import-Csv $inputCsv | %{Get-VMHost -Name $_.Name}
          $tempCSV = $env:Temp + "\Report-" + (Get-Date).Ticks + ".csv"
          
          $excelApp = New-Object -ComObject "Excel.Application"
          # $excelApp.Visible = $true
          $workBook = $excelApp.Workbooks.Add()
          
          # Report 1: ESX report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "ESX report"
          
          $report = $myHosts | Select Name, Version,Build, Manufacturer, Model, ProcessorType,
          @{N="NumCPU";E={($_| Get-View).Hardware.CpuInfo.NumCpuPackages}},
          @{N="Cores";E={($_| Get-View).Hardware.CpuInfo.NumCpuCores}},
          @{N="Service Console IP";E={($_|Get-VMHostNetwork).ConsoleNic[0].IP}},
          @{N="vMotion IP";E={($_|Get-VMHostNetwork).VirtualNic[0].IP}},
          @{N="HBA count";E={($_| Get-VMHostHba | where {$_.Type -eq "FibreChannel"}).Count}},
          @{N="Physical NICS count";E={($_ | Get-View).Config.Network.Pnic.Count}} 
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 2: VM report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "VM report"
          
          $report = $myHosts | Get-VM | %{
               $VM = $_
               $VMview = $VM | Get-View
               $VMResourceConfiguration = $VM | Get-VMResourceConfiguration -ErrorAction SilentlyContinue
               $VMHardDisks = $VM | Get-HardDisk
               $HardDisksSizesGB = @()
               $Temp = $VMHardDisks | ForEach-Object { $HardDisksSizesGB += [Math]::Round($_.CapacityKB/1MB) }
               $VmdkSizeGB = ""
               $Temp = $HardDisksSizesGB | ForEach-Object { $VmdkSizeGB += "$_+" }
               $VmdkSizeGB = $VmdkSizeGB.TrimEnd("+")
               $TotalHardDisksSizeGB = 0
               $Temp = $HardDisksSizesGB | ForEach-Object { $TotalHardDisksSizeGB += $_ }
               $Snapshots = $VM | Get-Snapshot
               $row = "" | Select-Object VMname,ESXname,MemoryGB,vCPUcount,vNICcount,IPaddresses,VmdkSizeGB,TotalVmdkSizeGB,DatastoreName,ToolsVersion,ToolsUpdate,SnapshotCount,GuestOS
               $row.VMName = $VM.name
               $row.ESXname = $VM.Host
               $row.MemoryGB = $VM.MemoryMB/1024
               $row.vCPUcount = $VM.NumCpu
               $row.vNICcount = $VM.Guest.Nics.Count
               $row.IPaddresses = $VM.Guest.IPAddress
               $row.VmdkSizeGB = $VmdkSizeGB
               $row.TotalVmdkSizeGB = $TotalHardDisksSizeGB
               $row.DatastoreName = $VMview.Config.DatastoreUrl
               $row.ToolsVersion = $VMview.Config.Tools.ToolsVersion
               $row.ToolsUpdate = $VMview.Guest.ToolsStatus
               $row.SnapshotCount = (@($VM | Get-Snapshot)).Count
               $row.GuestOS = $VM.Guest.OSFullName
               $row
          }
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 3: pNIC report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "pNIC report"
          
          $report = foreach($esxImpl in $myHosts){ 
               $esx = $esxImpl | Get-View
               $netSys = Get-View $esx.ConfigManager.NetworkSystem
               foreach($pnic in $esx.Config.Network.Pnic){
                    $vSw = $esxImpl | Get-VirtualSwitch | where {$_.Nic -contains $pNic.Device}
                    $pg = $esxImpl | Get-VirtualPortGroup | where {$_.VirtualSwitchName -eq $vSw.Name}
                    $order = ($esx.Config.Network.Vswitch | where {$_.Name -eq $vSw.Name}).Spec.Policy.NicTeaming.NicOrder
                    $cdpInfo = $netSys.QueryNetworkHint($pnic.Device)
                    $pnic | Select @{N="ESXname";E={$esxImpl.Name}},
                         @{N="pNic";E={$pnic.Device}}, 
                         @{N="Model";E={($esx.Hardware.PciDevice | where {$_.Id -eq $pnic.Pci}).DeviceName}},
                         @{N="vSwitch";E={$vSw.Name}},
                         @{N="Portgroups";E={$pg | %{$_.Name}}},
                         @{N="Speed";E={$pnic.LinkSpeed.SpeedMb}},
                         @{N="Status";E={if($pnic.LinkSpeed -ne $null){"up"}else{"down"}}},
                         @{N="PCI Location";E={$pnic.Pci}},
                         @{N="Active/stand-by/unassigned";E={if($order.ActiveNic -contains $pnic.Device){"active"}elseif($order.StandByNic -contains $pnic.Device){"standby"}else{"unused"}}},
                         @{N="IP range";E={[string]::Join("/",@($cdpInfo[0].Subnet | %{$_.IpSubnet + "(" + $type + ")"}))}},
                         @{N="Physical switch";E={&{if($cdpInfo[0].connectedSwitchPort){$cdpInfo[0].connectedSwitchPort.devId}else{"CDP not configured"}}}},
                         @{N="PortID";E={&{if($cdpInfo[0].connectedSwitchPort){$cdpInfo[0].connectedSwitchPort.portId}else{"CDP not configured"}}}}
               }
          }
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 4: Portgroup report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "Portgroup report"
          
          $report = foreach($esxImpl in $myHosts ){ 
               $esx = $esxImpl | Get-View
               $netSys = Get-View $esx.ConfigManager.NetworkSystem
               foreach($pg in $esx.Config.Network.Portgroup){
                    $pNICStr = @()
                    $pciStr = @()
                    $cdpStr = @()
                    foreach($a in $pg.ComputedPolicy.NicTeaming.NicOrder.ActiveNic){
                         if($a){
                              $pNICStr += ($a + "(a)")
                              $pciStr += ($esx.Config.Network.Pnic | where {$_.Device -eq $a} | %{$_.Pci + "(a)"})
                              $cdpInfo = $netSys.QueryNetworkHint($a)
                              $cdpStr += &{if($cdpInfo[0].connectedSwitchPort){
                                                  $cdpInfo[0].connectedSwitchPort.devId + "(a):" + $cdpInfo[0].connectedSwitchPort.PortId + "(a)"
                                             }
                                             else{"CDP not configured(a)"}}
                         }
                    }
                    foreach($s in $pg.ComputedPolicy.NicTeaming.NicOrder.StandbyNic){
                         if($s){
                              $pNICStr += ($s + "(s)")
                              $pciStr += ($esx.Config.Network.Pnic | where {$_.Device -eq $s} | %{$_.Pci + "(s)"})
                              $cdpInfo = $netSys.QueryNetworkHint($s)
                              $cdpStr += &{if($cdpInfo[0].connectedSwitchPort){
                                                  $cdpInfo[0].connectedSwitchPort.devId + "(s):" + $cdpInfo[0].connectedSwitchPort.PortId + "(s)"
                                             }
                                             else{"CDP not configured(s)"}}
                         }
                    }
          
                    $pg | Select @{N="ESXname";E={$esxImpl.Name}},
                    @{N="vSwitch";E={($esx.Config.Network.Vswitch | where {$_.Key -eq $pg.Vswitch}).Name}},
                    @{N="Portgroup";E={$pg.Spec.Name}},
                    @{N="VLANid";E={$pg.Spec.VlanId}},
                    @{N="pNIC";E={$pNICStr}},
                    @{N="PCI location";E={$pciStr}},
                    @{N="Physical switch";E={$cdpStr}}
               }
          }
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 5: SCSI HBA report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "SCSI HBA report"
          
          $report = $myHosts | %{
               $esxImpl = $_
               $esxImpl | Get-VMHostHba | select @{N="ESX Name";E={$esxImpl.Name}},
                         @{N="Device";E={$_.Device}},
                         @{N="HBA Model";E={$_.Model}},
                         @{N="HBA Type";E={$_.Type}},
                         @{N="Driver";E={$_.Driver}},
                         @{N="PCI";E={$_.Pci}},
                         @{N="PWWN";E={$hbaKey = $_.Key; "{0:x}" -f (($esxImpl | Get-View).Config.StorageDevice.HostBusAdapter | where {$_.GetType().Name -eq "HostFibreChannelHba" -and $_.Key -eq $hbaKey}).PortWorldWideName}}
          }
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 6: Datastore report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "Datastore report"
          
          $report = $myHosts | %{
               $esxImpl = $_
               $ds = $esxImpl | Get-Datastore | %{$dsTab[http://$_.Name|http://$_.Name] = $_}
               $esxImpl | Get-VMHostStorage | %{
                    $_.FileSystemVolumeInfo | %{
                         $sizeGB = $_.Capacity/1GB
                         $usedGB = ($_.Capacity/1MB - ($dsTab[http://$_.Name|http://$_.Name]).FreeSpaceMB)/1KB
                         $usedPerc = $usedGB / $sizeGB
                         $availGB = ($dsTab[http://$_.Name|http://$_.Name]).FreeSpaceMB/1KB
                         $ds = Get-View $dsTab[http://$_.Name|http://$_.Name].Id
                         $_ | select @{N="ESX Name";E={$esxImpl.Name}},
                              @{N="FS Name";E={$_.Name}},
                              @{N="Type";E={$_.Type}},
                              @{N="SizeGB";E={"{0:N1}" -f $sizeGB}},
                              @{N="UsedGB";E={"{0:N1}" -f $usedGB}},
                              @{N="AvailableGB";E={"{0:N1}" -f $availGB}},
                              @{N="Used%";E={"{0:P1}" -f $usedPerc}},
                              @{N="Mount point";E={$_.Path}},
                              @{N="Extents";E={if($_.Type -eq "VMFS"){$ds.Info.Vmfs.Extent[0].DiskName}
                                                  elseif($_.Type -eq "NFS"){$ds.Info.Nas.RemoteHost + ":" + $ds.Info.Nas.RemotePath}}}
                    }
               }
          }
          
          $report | Export-Csv -Path $tempCSV -NoTypeInformation
          Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 7: Firewall report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "Firewall report"
          
          # $report | Export-Csv -Path $tempCSV -NoTypeInformation
          # Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 8: Time servers report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "Time servers report"
          
          # $report | Export-Csv -Path $tempCSV -NoTypeInformation
          # Copy-CSVtoSheet $sheet $tempCSV
          
          # Report 9: DNS servers report
          $sheet = $excelApp.Worksheets.Add()
          $sheet.Name = "DNS servers report"
          
          # $report | Export-Csv -Path $tempCSV -NoTypeInformation
          # Copy-CSVtoSheet $sheet $tempCSV
          
          # Clean up workbook & close
          $workbook.Sheets | where {$_.Name -like "Sheet*"} | %{$_.Delete()}
          $workbook.SaveAs("C:\test.xls")
          $excelApp.Quit()
          

           

           

           

           

          ____________

          Blog: LucD notes

          Twitter: lucd22

          • 2. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
            Sureshadmin Hot Shot

            Luc,

             

            I get the below given error when i try to run the script. Can you please tell me the connection logic, since the script is to handle many esx servers which it gets from input csv file, how to use the connect-viserver?

             

            5/26/2010 5:13:19 AM     Get-VMHost          PowerCLI is currently not connected to a server. To create a new connection use Connect-VIServer.     

            At :line:13 char:46

            + $myHosts = Import-Csv $inputCsv | %{Get-VMHost <<<<  -Name $_.Name}

            • 3. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
              LucD Guru
              User ModeratorsCommunity WarriorsvExpert

              I tested this with ESX servers that are all connected to the same vCenter and the connection to the vCenter was made before I ran the script.

               

              Do you intend to connect directly to each ESX server ?

              Where would you get the credentials from in that case ?

               

               

               

               

              ____________

              Blog: LucD notes

              Twitter: lucd22

              • 4. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                Sureshadmin Hot Shot

                Luc,

                 

                Actually i didn't connect to the esx server and just wanted to know whether i should connect to all server before i run the the script or connection logic is handled by the script.

                 

                I'm ready to code the credentials into the script, can you please make this script to handle connection logic, so that it gets the esx server name and use the credentials given in the script to connect to the esx box.

                 

                Please note i have same password for all esx boxes

                • 5. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                  LucD Guru
                  Community WarriorsvExpertUser Moderators

                  Attached a version with some authentication logic included.

                  You can hardcode the  or you can get the values from the CSV file as well.

                   

                  The script connects to all servers in the CSV file.

                   

                  The script also sets the VIServerMode to "Multiple" that way any command will be executed against all connected servers present in the $DefaultVIServers array.

                   

                   

                   

                   

                  ____________

                  Blog: LucD notes

                  Twitter: lucd22

                  • 6. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                    Sureshadmin Hot Shot

                    Luc,

                     

                    I gave root and password in the script and ran the script. It connected to the esx server and changed vsphere powercli policy to multimode and then while executing the code it gave the following errors.

                     

                    -


                            

                    The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

                    At :line:35 char:49

                    + @{N="Service Console IP";E={($_|Get-VMHostNetwork <<<< ).ConsoleNic[0].IP}},

                     

                    The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

                    At :line:36 char:41

                    + @{N="vMotion IP";E={($_|Get-VMHostNetwork <<<< ).VirtualNic[0].IP}},

                     

                    The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

                    At :line:37 char:37

                    + @{N="HBA count";E={($_| Get-VMHostHba <<<<  | where {$_.Type -eq "FibreChannel"}).Count}},

                     

                    True

                    The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

                    At :line:47 char:27

                    + $report = $myHosts | Get-VM <<<<  | %{

                     

                    True

                    Cannot validate argument on parameter 'Id'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again.

                    At :line:86 char:19

                    +      $netSys = Get-View <<<<  $esx.ConfigManager.NetworkSystem

                    -


                     

                    • 7. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                      LucD Guru
                      Community WarriorsUser ModeratorsvExpert

                      It looks as if the $myHosts variable is empty or contains a funny entry.

                      Could you perhaps change these lines

                      ...
                      $inputCsv = "C:\vmhosts.csv"
                      $myHosts = Import-Csv $inputCsv | %{
                           Connect-VIServer -Server $_.Name -User <username> -Password <password> |Out-Null
                           Get-VMHost -Name $_.Name
                      }
                      ...
                      

                      into this

                      ...
                      $inputCsv = "C:\vmhosts.csv"
                      $myHosts = Import-Csv $inputCsv | %{
                           Connect-VIServer -Server $_.Name -User <username> -Password <password> |Out-Null
                           Get-VMHost -Name $_.Name
                      }
                      $myHosts
                      ...
                      

                      This should display the content and would allow us to verify if all the hosts that are defined in the CSV are opened.

                       

                      Btw I noticed some other bugs in the last script.

                      Attached an updated version

                       

                       

                       

                       

                      ____________

                      Blog: LucD notes

                      Twitter: lucd22

                      • 8. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                        Sureshadmin Hot Shot

                        Luc,

                         

                        I ran the updated version of the report that you have attached in the last post and now got this error as given below,

                        Please note that i have only one esx server in the input csv file at present.

                         

                         

                        Name            State      PowerState      Id CpuUsage CpuTotal  Memory  Memory

                                                                           Mhz      Mhz UsageMB TotalMB

                        -


                                    -


                              -


                              -- -


                        -


                        -


                        -


                        xxxxxxxxx   Connected  PoweredOn  ...host     5305     5566    5357    7935

                         

                        Cannot index into a null array.

                        At :line:182 char:43

                        +      $ds = $esxImpl | Get-Datastore | %{$dsTab[http:// <<<< $_.Name|http:// <<<< $_.Name] = $_}

                        • 9. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                          LucD Guru
                          vExpertUser ModeratorsCommunity Warriors

                          That's a good sign, we got further.

                          The error you now get is due to a missing declaration for the hash table called $dsTab.

                           

                          Corrected version attached.

                           

                           

                           

                           

                          ____________

                          Blog: LucD notes

                          Twitter: lucd22

                          • 10. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                            Sureshadmin Hot Shot

                            Wow, it gave the xls file. Awesome. This is going to be the perfect script to document all esx box in the environment at one go.

                             

                            Need a bit more,

                             

                            1. In VM report the ip address field contains no IPaddress for VM's, instead it contains this msg System.String[]

                             

                            2. In the xls file the sheet order starts from Firewall(first sheet) to ESX(last sheet). It would be more nice to see in a order of ESX to firewall.

                             

                            3. Lastly, for Firewall, DNS and Time servers can you please add a bit of code in the script to pull these info as well. This would be more helpful for me. The expected output for these three report are given in a xls file as attachment in the first message of this post.

                            • 11. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                              Sureshadmin Hot Shot

                              Luc,

                               

                              Again i ran the script once again,

                               

                              in VM Report along with IP address field the datastore field also has no correct values instead it has also has some msg VMware.Vim.VirtualMachineConfigInfoDatastoreUrlPair[]

                              • 12. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                                LucD Guru
                                User ModeratorsCommunity WarriorsvExpert

                                Ok, I think I tackled all the problems you mentioned.

                                And made a few other improvements.

                                 

                                Have a go.

                                 

                                 

                                 

                                 

                                 

                                ____________

                                Blog: LucD notes

                                Twitter: lucd22

                                1 person found this helpful
                                • 13. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                                  LucD Guru
                                  vExpertCommunity WarriorsUser Moderators

                                  Perhaps you're interested to know that I "generalised" the XLS function.

                                  See my Beyond Export-Csv: Export-Xls post.

                                   

                                   

                                   

                                   

                                  ____________

                                  Blog: LucD notes

                                  Twitter: lucd22

                                  1 person found this helpful
                                  • 14. Re: Combine Script ouput of ESX, VM, Network, Storage and output into Excel
                                    Sureshadmin Hot Shot

                                    hi Luc,

                                     

                                    Firstly, thanks for this awesome piece of work Export-xls function. This Export-xls function is going to be very useful for numerous number of vmware admins who use Powershell for their daily admin activities since most of us would like to have the reports in a neatly formatted xls file and the real fact is that manager's love this excel file and would be easy for them to present it to the higher management. In our PowerCLI community itself we have many posts where many people ask for exporting the script output to a spreadsheet, for admins like me who has a responsibility of periodically documenting the infrastructure this function is "Gold". I remember, a year before when i was not aware of powershell i used to gather data manually and will use my weekends to create and format a excel sheet with all the data manually as per the requirement of my management, a back-breaking work. Now just a click of button report is ready!

                                     

                                    If PowerCLI is a religion, Luc is God!

                                     

                                    Still i have not run your last version of the script since i'm in a extended weekend and away from my vmware environment. Will run it in couple of days.

                                    1 2 3 Previous Next