Issue with exporting data to csv

[ganapa2000]

Hi,

I am having issue exporting the data to csv as the data is not getting exported to different columns but it is exporting to same column.

Please help,

$vcenters = "10.10.10.10"

$password = 'password'

foreach ( $vcenter in $vcenters ) {

   connect-viserver $vcenter -Username 'root' -Password 'password'

   $vmhosts = get-vmhost | where { $_.connectionState -ne "NotResponding" } | sort Name

$report = @()

foreach ( $vmhost in $vmhosts.name ) {

    $details = " " | select HostName, DeviceName, SettableDisplay, Size, MPP, DevfSPath, Vendor, Model, Revision, RDM, Local, Removable, QueueSize, isShared, isUSB,BootUSB, isBootDevice

    $details.Hostname = $vmhost

    echo y | .\plink.exe  -ssh root@$vmhost -pw $password hostname

    $hostdetails = .\plink.exe  -ssh root@$vmhost -pw $password -m .\cmd.txt

    $details.devicename = $hostdetails

    $report += $details

    }

    $report | export-csv -NoTypeInformation -path .\$vcenter-boot-device-info.csv

}

Below the command in another text file - cmd.txt

b=`ls -l /bootbank | awk -F"-> " '{print $2}'`

d=`vmkfstools -P $b | egrep 'mpx|naa|eui' | awk -F":" '{print $1 }' | sed -e 's/^[ \t]*//'`

esxcli storage core device list | grep -A27  $d | egrep 'Display Name:|Size:|Multipath Plugin:|Devfs Path:|Vendor:|Model:|Revision:|Is RDM Capable:|Is Local:|Is Removable:|Is Shared Clusterwide:|Is USB:|Is Boot USB Device:|Is Boot Device:' | awk -F":" '{print $2}' |sed  's/ //g' | awk -vRS="" -vOFS=',' '$1=$1'

output of csv, all the columns are empty except first two

[vijayrana968]

Change last line from $report to $details.

$details | export-csv -NoTypeInformation -path .\$vcenter-boot-device-info.csv

[ganapa2000]

same issue, attached the output file

[LucD]

You get all the properties back in 1 string.

The fields are marked with commas between tehm, so we can split them that way.

Try like this

$vcenters = "10.10.10.10"

$password = 'password'

foreach ( $vcenter in $vcenters ) {

   Connect-VIServer $vcenter -Username 'root' -Password 'password'

   $vmhosts = Get-VMHost -Server $vcenter | where { $_.connectionState -ne "NotResponding" } | sort Name

   $report = @()

   foreach ( $vmhost in $vmhosts) {

  echo y | .\plink.exe  -ssh root@$vmhost.Name -pw $password hostname

   $hostdetails = .\plink.exe  -ssh root@$vmhost.Name -pw $password -m .\cmd.txt

   $hostdetailsArray = $hostdetails.Split(',')

   $report += New-Object PSObject -Property @{

   hostName = $vmhost.Name

   deviceName = $hostdetailsArray[0]

   SettableDisplay = $hostdetailsArray[1]

   Size = $hostdetailsArray[2]

   MPP = $hostdetailsArray[3]

   DevfSPath = $hostdetailsArray[4]

   Vendor = $hostdetailsArray[5]

   Model = $hostdetailsArray[6]

   Revision = $hostdetailsArray[7]

   RDM = $hostdetailsArray[8]

   Local = $hostdetailsArray[9]

   Removable = $hostdetailsArray[10]

   QueueSize = $hostdetailsArray[11]

   isShared = $hostdetailsArray[12]

   isUSB = $hostdetailsArray[13]

   bootUSB = $hostdetailsArray[14]

   isBootDevice = $hostdetailsArray[15]

   }

   }

   $report | Export-Csv -NoTypeInformation -path .\$vcenter-boot-device-info.csv

}

[ganapa2000]

Hi LucD,

I am getting the below error

Unable to open connection:

Host does not existUnable to open connection:

Host does not existYou cannot call a method on a null-valued expression.

At D:\temp1\temp2.ps1:11 char:4

+    $hostdetailsArray = $hostdetails.Split(',')

+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

    + FullyQualifiedErrorId : InvokeMethodOnNull

Cannot index into a null array.

At D:\temp1\temp2.ps1:12 char:4

+    $report += New-Object PSObject -Property @{

+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException

    + FullyQualifiedErrorId : NullArray

[LucD · Accepted solution]

Can you check if by replacing the following lines it behaves differently

echo y | .\plink.exe  -ssh "root@$($vmhost.Name)" -pw $password hostname

$hostdetails = .\plink.exe  -ssh "root@$($vmhost.Name)" -pw $password -m .\cmd.txt

[ganapa2000]

Thanks a lot LucD,

That worked perfectly