VMware Cloud Community
Daithi_N
Enthusiast
Enthusiast
Jump to solution

Exporting vCenter Info to CSV File.

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

Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

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?


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

View solution in original post

Reply
0 Kudos
18 Replies
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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:

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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 ?

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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={''}}

    }

}


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

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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?


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

Reply
0 Kudos
Daithi_N
Enthusiast
Enthusiast
Jump to solution

Hi LucD,

Thank you for your help:

Reply
0 Kudos