1 2 Previous Next 18 Replies Latest reply on Apr 20, 2020 6:13 AM by Daithi_N

    Exporting vCenter Info to CSV File.

    Daithi_N Novice

      Hi, I'm currently working on Exporting Elements out of our vCenter Environments into a CSV File:

      such as vCenter /DataCenter/ Cluster / DataStore / vDS & Standard Switching / Templates (If Template Not Found Export Null or None)

       

      vCenter |  Cluster | DataStore | Network | Templates

      test1  |  Test1_cluster_a | San1 |  vDS_test | win_12

      test1  |  Test1_cluster_a | San2 |  VDS_test | win_12

      test2  |  Test2_cluster_b | San3 |  VDS_test | win_12

      test2  |  Test2_cluster_c | San3 |  VDS_test | Null

       

      Not sure if this is the best approach to doing such: At the moment:

       

       

      #vCenter Variables
      $user = ''
      $pswd = ''
      $vCenter_Servers = "  ", " " , " "
      
      
      $report = foreach($vCenter in $vCenter_Servers) {
      Connect-VIServer -Server $vCenter -User $user -Password $pswd
                  foreach($DataCenter in Get-Datacenter){           
                  foreach($cluster in Get-Cluster){
                  foreach($datastore in Get-Datastore){
                  foreach($switch in Get-VDSwitch){
                  foreach($Template in Get-Template){
                    $obj = New-Object PSObject -Property @{
                        Vcenter = $vCenter.Name
                        Cluster = $cluster.Name
                        DataStore = $datastore.name
                        Switch = $Switch.Name
                        Template = $vm.Template
                  }
                  $obj
              }
            }
          }
        }
       }
      }
      
      
      
      $report |
      
      
      Select Vcenter,Cluster,DataStore,Template |
      Export-Csv -Path C:\Scripts\Report5.csv -NoTypeInformation -UseCulture
      
        • 1. Re: Exporting vCenter Info to CSV File.
          LucD Guru
          Community WarriorsUser ModeratorsvExpert

          By using nested foreach loops you are trying to combine of objects that do not really have a relation with each other.
          Another issue is that datastores, VDS and Templates are 'known' on the datacenter level (and thus in each cluster in that datacenter).

          Finally, there might be multiple datastores, VDS and templates in a cluster/datacenter, hence the joining of the names so it becomes 1 value.

           

           

          #vCenter Variables 

          $user = ''

          $pswd = ''

          $vCenter_Servers = "  ", " " , " "

           

          $report = foreach ($vCenter in $vCenter_Servers) {

              Connect-VIServer -Server $vCenter -User $user -Password $pswd

              foreach ($DataCenter in Get-Datacenter -Server $vCenter) {           

                  foreach ($cluster in Get-Cluster -Location $DataCenter -Server $vCenter) {

                      

                      $ds = Get-Datastore -Location $DataCenter -Server $vCenter

                      $vds = Get-VDSwitch -Location $DataCenter -Server $vCenter

                      $template = Get-Template -Location $DataCenter -Server $vCenter


                      New-Object PSObject -Property @{

                          Vcenter   = $vCenter.Name

                          Cluster   = $cluster.Name

                          DataStore = $ds.name -join '|'

                          Switch    = $vds.Name -join '|'

                          Template  = $template.Name -join '|'

                      }

                  }

              }

          }

           

          $report |

          Export-Csv -Path C:\Scripts\Report5.csv -NoTypeInformation -UseCulture

           

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Exporting vCenter Info to CSV File.
            Daithi_N Novice

            Hi @LucD

             

            Thank you for your reply: I'll be Importing this CSV File to a SQL Lite Database: which I would like to avoid using -join '|' where i can remove the duplicates using SQL. without using the Join it's showing the Datastores as System Objects:

            • 3. Re: Exporting vCenter Info to CSV File.
              LucD Guru
              Community WarriorsUser ModeratorsvExpert

              How would you in that case represent a varying number of datastores in a row in such a CSV?

              Note that Export-Csv uses the first row in an array to determine how many columns the CSV will hold.

              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Exporting vCenter Info to CSV File.
                Daithi_N Novice

                Hi LucD  I'm looking to Import the CSV and using the SQL distinct to remove duplicated values from being Returned,

                • 5. Re: Exporting vCenter Info to CSV File.
                  LucD Guru
                  vExpertCommunity WarriorsUser Moderators

                  That was not my question.
                  How do you intend to display this data in 1 row?
                  Like

                  vCenter1,Cluster1,Datastore1,Datastore2,Datastore3,Switch1,Switch2,Template1,Template2,Template3,Template4

                  vCenter2,Cluster2,Datastore21,Switch21,Switch22,Template21,Template22

                  If you are importing the data into a DB, I would think it would be a lot easier to have a fixed number of columns.
                  And use a Split('|') function to retrieve the different values in 1 column.

                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                  • 6. Re: Exporting vCenter Info to CSV File.
                    Daithi_N Novice

                    Hi LucD

                     

                    Within our Environment: While the DataCenter sees all Attached DataStores / Networks

                    on the Cluster Level, They may not be attached and unable to see such.

                     

                    For Example:

                    DC 1 can see both SAN1 & SAN2

                    Cluster2 only sees SAN2

                    Cluster1 only sees SAN1

                    • 7. Re: Exporting vCenter Info to CSV File.
                      LucD Guru
                      Community WarriorsUser ModeratorsvExpert

                      Are you saying that each cluster only sees 1 datastore, 1 switch and 1 template?

                      If not, my question is still, how are you going to represent that in a row?

                      Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                      • 8. Re: Exporting vCenter Info to CSV File.
                        Daithi_N Novice

                        Hi,

                        In one of our Datacenters it has 4 Attached DataStores but the cluster within that DC can only see one of them

                        While another Cluster can see SAN00 SAN01 & SAN03

                         

                        I'm looking at the following:

                        Connect-VIServer -Server $vCenter -User $user -Password $pswd

                            foreach ($DataCenter in Get-Datacenter -Server $vCenter) {          

                                foreach ($cluster in Get-Cluster -Location $DataCenter -Server $vCenter) {

                                foreach($ds in Get-Datastore -RelatedObject $cluster -Server $vCenter){

                        • 9. Re: Exporting vCenter Info to CSV File.
                          LucD Guru
                          vExpertCommunity WarriorsUser Moderators

                          You don't seem to understand what exactly I'm asking.

                           

                          In any case, run the following and see if that is what you actually want.

                           

                          #vCenter Variables 

                          $user = ''

                          $pswd = ''

                          $vCenter_Servers = "  ", " " , " "

                           

                           

                          $report = foreach ($vCenter in $vCenter_Servers) {

                              Connect-VIServer -Server $vCenter -User $user -Password $pswd

                              foreach ($DataCenter in Get-Datacenter -Server $vCenter) {           

                                  foreach ($cluster in Get-Cluster -Location $DataCenter -Server $vCenter) {

                                      


                                      $obj = [ordered]@{

                                          Vcenter = $vCenter.Name

                                          Cluster = $cluster.Name

                                      }

                                      $i = 1

                                      Get-Datastore -RelatedObject $cluster -Server $vCenter |

                                      Where-Object { $_.ExtensionData.Summary.MultipleHostAccess } | ForEach-Object {

                                          $obj.Add("Datastore$i", $_.Name)

                                          $i++

                                      }

                                      $i = 1

                                      Get-VMHost -Location $cluster -Server $vCenter | Get-VDSwitch | ForEach-Object {

                                          $obj.Add("vdSwitch$i", $_.Name)

                                          $i++

                                      }

                                      $i = 1

                                      Get-VMHost -Location $cluster -Server $vCenter | Get-Template | ForEach-Object {

                                          $obj.Add("Template$i", $_.Name)

                                          $i++

                                      }

                                      New-Object PSObject -Property $obj

                                  }

                              }

                          }

                           

                           

                           

                          $report | Sort-Object -Property { $_ | Get-Member -MemberType NoteProperty } -Descending

                          Export-Csv -Path C:\Scripts\Report5.csv -NoTypeInformation -UseCulture

                           

                          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                          • 10. Re: Exporting vCenter Info to CSV File.
                            Daithi_N Novice

                            Hi LucD:

                             

                            Due to our Environment Set-up our Datacenters within our vCenter could have Clusters that are indeed in different Server Rooms / locations: Due to this issue: I would need to output by Cluster and not Datacenter this will lead to outputting everything into it's own Column which would mean duplicated entries,

                            For this I will be using the SQL Distinct feature:

                             

                            Select distinct Datacenter  From Table

                             

                            The Below Code is outputting the way i can import into my SQL: the problem is that it's not outputting all the Datacenters only a select few. I have one vCenter that has 32 it only exports 6 of these:

                             

                            $report = foreach ($vCenter in $vCenter_Servers) {

                                  Connect-VIServer -Server $vCenter -User $user -Password $pswd

                                   foreach ($DataCenter in Get-Datacenter -Server $vCenter) {          

                                    foreach ($cluster in Get-Cluster -Location $DataCenter -Server $vCenter) {

                                     foreach($esxi in Get-VMHost -Location $cluster -Server $vCenter){

                                      foreach($ds in Get-Datastore -RelatedObject $cluster -Server $vCenter){

                                        foreach($template in Get-Template -Datastore $ds -Server $vCenter){

                                          #foreach($vds in Get-VDSwitch -VMHost $esxi -Server $vCenter){

                                

                                        New-Object PSObject -Property @{

                                            DataCenter  = $DataCenter.Name

                                            Vcenter     = $vc.Name

                                            Cluster     = $cluster.Name

                                            esx         = $esxi.name

                                            DataStore   = $ds.name

                                           #Switch      = $vds.Name

                                            Template    = $template.Name

                             

                             

                                          }

                                        }

                                      }

                                    }

                                  }

                                }

                              }

                             

                             

                            $report |

                            Select Vcenter, Datacenter, Cluster, esx, DataStore, Switch, Template | 

                            Export-Csv -Path C:\Scripts\Report5.csv -NoTypeInformation -UseCulture

                            • 11. Re: Exporting vCenter Info to CSV File.
                              LucD Guru
                              User ModeratorsCommunity WarriorsvExpert

                              Provided you are connected to all your vCenters (check with $global:defaultVIServers), does the following return the complete list?

                               

                              Get-Datacenter -PipelineVariable dc |

                              Get-Cluster |

                              Select @{N='vCenter';E={([uri]$_.ExtensionData.Client.ServiceUrl).Host}},

                                  @{N='Datacenter';E={$dc.Name}},

                                  @{N='Cluster';E={$_.Name}}

                              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                              • 12. Re: Exporting vCenter Info to CSV File.
                                Daithi_N Novice

                                It appears it doesn't output All DCs but i if just did a Get-Datacenter on it's own it outputs all to console

                                • 13. Re: Exporting vCenter Info to CSV File.
                                  LucD Guru
                                  vExpertCommunity WarriorsUser Moderators

                                  That could mean that

                                  - not all your Datacenters have clusters

                                  - you don't have permission to list all clusters

                                   

                                  Can you see a pattern for the ones that are not listed?

                                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                                  • 14. Re: Exporting vCenter Info to CSV File.
                                    Daithi_N Novice

                                    Hi LucD.

                                     

                                    Thanks for the Reply indeed some DC don't have clusters in them which won't appear, but others seem to have clusters in them and still aren't appearing, would the script stop running once it see a DC with not cluster and finish ?

                                     

                                    if so is it possible to output null or even the ESXi for that DC if that is the case ?

                                    1 2 Previous Next