Exporting vCenter Info to CSV File.

[Daithi_N]

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

[LucD]

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

[Daithi_N]

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:

[LucD]

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.

[Daithi_N]

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

[LucD]

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.

[Daithi_N]

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

[LucD]

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?

[Daithi_N]

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){

[LucD]

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

[Daithi_N]

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

[LucD]

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}}

[Daithi_N]

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

[LucD]

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?

[Daithi_N]

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 ?

[LucD]

Try something like this perhaps.

Get-Datacenter -PipelineVariable dc |

ForEach-Object -Process {

    $cl = Get-Cluster -Location $dc

    if($cl){

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

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

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

    }

    else{

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

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

        @{N='Cluster';E={''}}

    }

}

[Daithi_N]

Thank you that works, how would i populate for the rest similar to the code that i had:

so i'm able to get CSV file as follows:

vcenter | datacenter | cluster | switch      |esxi |

test1 | test1 | test1-cluster | test1-switch | esxi-1

test1 | test2 | ' ' | test2-switch | esxi-2

[LucD]

It seems you are aiming for a moving target.

First you left out the switch and included the template.

Which can be done with

Get-Datacenter -PipelineVariable dc |

ForEach-Object -Process {

    Get-VMHost -Location $dc -PipelineVariable esx |

    ForEach-Object -Process {

        Get-Datastore -VMHost $esx -PipelineVariable ds |

        ForEach-Object -Process {

            if(Get-Template -Datastore $ds -Location $esx){

               Get-Template -Datastore $ds -Location $esx -PipelineVariable template |

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

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

                   @{N='Cluster';E={(Get-Cluster -VMHost $esx).Name}},

                   @{N='VMHost';E={$esx.Name}},

                   @{N='Datastore';E={$ds.Name}},

                   @{N='Template';E={$template.Name}}

            }

            else{

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

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

                   @{N='Cluster';E={(Get-Cluster -VMHost $esx).Name}},

                   @{N='VMHost';E={$esx.Name}},

                   @{N='Datastore';E={$ds.Name}},

                   @{N='Template';E={$template.Name}}

            }

        }

    }

} | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture

Now the switch is back and the template is out.
Which is it going to be?

[Daithi_N]

Hi LucD,

Thank you for your help: