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
It seems you are aiming for a moving target.
First you left out the switch and included the template.
Which can be done with
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
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.
$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
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:
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
Hi LucD I'm looking to Import the CSV and using the SQL distinct to remove duplicated values from being Returned,
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
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
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
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){
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.
$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
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
Provided you are connected to all your vCenters (check with $global:defaultVIServers), does the following return the complete list?
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
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
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
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 ?
Try something like this perhaps.
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
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
It seems you are aiming for a moving target.
First you left out the switch and included the template.
Which can be done with
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
Hi LucD,
Thank you for your help: