PowerCLI Script to get all Datastore in one Row...

[hharold]

Hi all,

What I am trying to accomplish is to get this kind of output:

Cluster, Host,WWN1,WWN2,Datastore1,Datastore2,Datastore...,DatastoreX

So, 1 row per host, with all attached Datastores in the same row

Number of Datastores can vary from 1 to 30.

Until now I have created this script:

$clusters = Get-Cluster | sort

$StorageInfo = @()

foreach ($cluster in $clusters){
    Write-Host $cluster
    
    $VMHosts = Get-Cluster $Cluster | Get-VMhost | sort
    foreach ($VMHost in $VMHosts){
        $Vendor = "" 
        $VMhostname = $VMHost.Name
        $WWN1 = "{0:x}" -f ($vmhost.ExtensionData.config.storagedevice.HostBusAdapter | Where { $_.Key -match "FibreChannelHba" })[0].PortWorldWideName
        $WWN2 = "{0:x}" -f ($vmhost.ExtensionData.config.storagedevice.HostBusAdapter | Where { $_.Key -match "FibreChannelHba" })[1].PortWorldWideName
        
        if ( ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | Where { $_.Key -match "Disk"}).count -gt 1 ){
            $Vendor = ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | Where { $_.Key -match "Disk" -and $_.Model -notmatch "Block Device"})[0].Vendor
            } else { 
            $Vendor = ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | Where { $_.Key -match "Disk" -and $_.Model -notmatch "Block Device"}).Vendor
            }
        
        $Datastores = $VMHost | Get-Datastore | sort
        foreach ($Datastore in $Datastores){
                    
            $myObj = @()
            $myObj = "" | Select Farm,HostName,WWN1,WWN2,Vendor,Datastore
            $myObj.Farm = $Cluster.Name
            $myObj.HostName = $VMhostname
            $myObj.WWN1 = $WWN1
            $myObj.WWN2 = $WWN2
            $myObj.Vendor = $Vendor
            $myObj.Datastore = $Datastore.name
                           
            $StorageInfo += $myObj
            }
        }
    }
    
$StorageInfo | convertto-csv -NoTypeInformation | Out-File ./Storage-info.csv    

But this gives me one line per datastore, so multiple lines for each host.

Could someone point me at a method how to accomplish this?

Thanks and kind regards,

Harold

[LucD · Accepted solution]

You can use the Join function

Something like this

$clusters = Get-Cluster | sort
$StorageInfo = @()

foreach ($cluster in $clusters){
    Write-Host $cluster
    $VMHosts = Get-Cluster $Clusters | Get-VMhost | sort
    foreach ($VMHost in $VMHosts){
        $Vendor = "" 
        $VMhostname = $VMHost.Name
        $WWN1 = "{0:x}" -f ($vmhost.ExtensionData.config.storagedevice.HostBusAdapter | where { $_.Key -match "FibreChannelHba" })[0].PortWorldWideName
        $WWN2 = "{0:x}" -f ($vmhost.ExtensionData.config.storagedevice.HostBusAdapter | where { $_.Key -match "FibreChannelHba" })[1].PortWorldWideName

        if ( ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | where { $_.Key -match "Disk"}).count -gt 1 ){
            $Vendor = ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | where { $_.Key -match "Disk" -and $_.Model -notmatch "Block Device"})[0].Vendor
        } else { 
            $Vendor = ($vmhost.ExtensionData.config.StorageDevice.ScsiLun | where { $_.Key -match "Disk" -and $_.Model -notmatch "Block Device"}).Vendor
        }

        $Datastores = [string]::Join(',',($VMHost | Get-Datastore | sort | %{$_.Name}))

        $myObj = "" | Select Farm,HostName,WWN1,WWN2,Vendor,Datastore
        $myObj.Farm = $Cluster.Name
        $myObj.HostName = $VMhostname
        $myObj.WWN1 = $WWN1
        $myObj.WWN2 = $WWN2
        $myObj.Vendor = $Vendor
        $myObj.Datastore = $Datastores
        $StorageInfo += $myObj
    }
}

$StorageInfo | convertto-csv -NoTypeInformation | Out-File ./Storage-info.csv 

[hharold]

Thanks Luc!

That did it, good lesson for me.

Regards,

Harold

[LucD]

That's one of the nice points with PowerShell, you have access to all the .NET methods as well.