Export to CSV return System.Object[] not the Data

I have a problem I cannot find an answer to when I run the following command in powershell it returns correctly

DiskName DiskSizeGB


{ABC-Server-disk, ABC-Server1-01-disk, ABC-Serv2-02-disk, ABC-Server3-01-disk…} {127, 127, 127, 127…}

When I export to CSV all I get is

DiskName DiskSizeGB
System.Object[] System.Object[]

How do I get it to out put the correct information to CSV.

It’d be helpfull to include the code you’re using now…

But from the error, it suggests you’re exporting the object “{ABC-Server-disk, ABC-Server1-01-disk, ABC-Serv2-02-disk, ABC-Server3-01-disk…}” to the csv and not each item in that object. eg: “ABC-Server-disk”.

$report = @()
$vms = Get-Azvm
$nics = Get-AzNetworkInterface | ?{ $_.VirtualMachine -NE $null}
$disk = get-azdisk
foreach($nic in $nics)
{
$info = “” | Select ResourceGroupName,VmName,OStype,Interface,Adaptor,PrivIP,PublicIP,DiskName,DiskSize
$vm = $vms | ? -Property Id -eq $nic.VirtualMachine.id
$info.VMName = $vm.Name
$info.ResourceGroupName = $vm.ResourceGroupName
$info.Interface = $nic.name
$info.Adaptor = $nic.IpConfigurations.Name
$info.PrivIP = $nic.IpConfigurations.PrivateIpAddress
$info.PublicIP = $nic.IPConfigurations.PublicIpAddress
$info.OStype = $vm.StorageProfile.osDisk.osType
$info.Diskname = $disk.name
$info.DiskSize = $disk.DiskSizeGB
$report+=$info
}
$report | Sort-object PrivIP | Export-Csv -Path “c:\temp\AzureIP+Disk.Csv”

I don’t have an Azure environment to test this, but it looks like you are gathering all disks in a subscription not a specific resource group. Also, use the full cmdlet name not an alias.

$vm = Get-Azvm
$nic = Get-AzNetworkInterface | Where-Object {$_.VirtualMachine}
$disk = get-azdisk

$report = 
foreach($n in $nic)
{
    $v = $vm | Where-Object {$_.Id -eq $n.VirtualMachine.id}
    $d = $disk | Where-Object {$_.ResourceGroupName -eq $v.ResourceGroupName}
    [PSCustomObject]@{
        VMName = $v.Name
        ResourceGroupName = $v.ResourceGroupName
        Interface = $n.name
        Adaptor = $n.IpConfigurations.Name
        PrivIP = $n.IpConfigurations.PrivateIpAddress
        PublicIP = $n.IPConfigurations.PublicIpAddress
        OStype = $v.StorageProfile.osDisk.osType
        Diskname = $d.name
        DiskSize = $d.DiskSizeGB
    }
}

$report | Sort-Object PrivIP | Export-Csv -Path “c:\temp\AzureIP+Disk.Csv”

so when the report it calls Diskname = $disk.name DiskSize = $disk .DiskSizeGB yield multiple results so I get System.Object[] System.Object[] inthe csv file however the values are displayed in the PowerShell console itself.

I need it to export all the Fields from the output of the command.

Seems like you need a storage format that can handle nested objects. Unless you want to count the disks and have disk1, disk2, etc. Could you use clixml or JSON?

what would that command look like would I have to covert to JSON and than to CSV ?

CSV is for 2 dimensional arrays. If you want your disks grouped, then you don’t want to use CSV. You would simply export to json and import it back when you need it. If you want to stick to CSV then you’ll need to split all the items up. If the amount of disks change per host, then you’ll need to put them in a foreach loop and count them. If you want a pscustomobject per disk, then run the entire $d = $disk section in a foreach loop. Can you tell me if you want an entire listing like you have let disk? Or would you like the listing like it is with each disk added as a property?

[pscustomobject]@{DiskName = echo ABC-Server-disk, ABC-Server1-01-disk, ABC-Serv2-02-disk, 
  ABC-Server3-01-disk; DiskSizeGB = echo 127, 127, 127, 127} | convertto-json

{
  "DiskName": [
    "ABC-Server-disk",
    "ABC-Server1-01-disk",
    "ABC-Serv2-02-disk",
    "ABC-Server3-01-disk"
  ],
  "DiskSizeGB": [
    127,
    127,
    127,
    127
  ]
}

what I’m looking is a csv output like this

ResourceGroupName VmName OStype Interface Adaptor PrivIP PublicIP DiskName DiskSize
Test ABC-Server Windows inerfacename ipconfig1 10.0.0.0 what every the disk for this servers is what every the disk size for that server is
everything works except or the output of the DiskName and DiskSize as it sends it back in one large group not an individual object

 

You could join each array as string with something like a comma in between. Otherwise you’d need to have a loop to make a row for each element.

The nice thing about json is it will remember things like arrays and numbers when they’re imported back in.

So you ant all the disks under that VM name. Then loop the disks while keeping count so you can assign each like this

count = 1
Foreach($d in ($disks | where.....)){
“Disk$countName” = $d.name

“Disk$countSize” = $d.disksizeGB
$count++

}

I’m on my mobile. I have not tested it. I’d play with json as well to see if it can satisfy your requirements.

@Doug Maurer not sure how a count will help you lost me as for the JSON I would have to see formatting as I’m kind of lost.

Ok change your export to CSV to an export to json and look at the file.

So you have more than one disk. You’re only trying to put it in 1 Disk property. Do you want it to look like this?

Diskname = SomediskSomeotherdiskYetnotherdisk

Disksize = 100Gb200Gb300GB

That’s essentially what you are trying to do with your current code. So that disk’s name is “SomediskSomeotherdiskYetnotherdisk” and it’s size is 100Gb200Gb300GB. Probably not what you are wanting. If you know how many disks/what to expect and this information won’t change, you can account for it. For example, let’s say you will always have two disks.

[PSCustomObject]@{
    VMName = $v.Name
    ResourceGroupName = $v.ResourceGroupName
    Interface = $n.name
    Adaptor = $n.IpConfigurations.Name
    PrivIP = $n.IpConfigurations.PrivateIpAddress
    PublicIP = $n.IPConfigurations.PublicIpAddress
    OStype = $v.StorageProfile.osDisk.osType
    Disk1name = $Disk[0].name
    Disk1Size = $Disk[0].DiskSizeGB
    Disk2name = $Disk[1].name
    Disk2Size = $Disk[1].DiskSizeGB
}
$report | Sort-Object PrivIP | Export-Csv -Path “c:\temp\AzureIP+Disk.Csv”

Now if you don’t know how many disks then you can run it through a loop.

$disks = @(
    @{Name="OS";disksizeGB=100},
    @{Name="DATA";disksizeGB=1000}
    @{Name="BACKUP";disksizeGB=2000}
)

$object = [PSCustomObject]@{
    VMName = "VMName"
    ResourceGroupName = "ResourceGroupName"
    Interface = "NetName"
    Adaptor = "Name"
    PrivIP = "PrivateIpAddress"
    PublicIP = "PublicIpAddress"
    OStype = "osType"
}

$count = 1
Foreach($d in ($disks | where {$_})){
    $d.name
    Add-member -InputObject $object -NotePropertyName "Disk$($count)Name" -NotePropertyValue $d.name
    Add-member -InputObject $object -NotePropertyName "Disk$($count)Size" -NotePropertyValue $d.disksizeGB
    $count++
}
$object | Sort-Object PrivIP | Export-Csv -Path “c:\temp\AzureIP+Disk.Csv” -NoTypeInformation

That will give you the CSV output you want.

"VMName","ResourceGroupName","Interface","Adaptor","PrivIP","PublicIP","OStype","Disk1Name","Disk1Size","Disk2Name","Disk2Size","Disk3Name","Disk3Size"
"VMName","ResourceGroupName","NetName","Name","PrivateIpAddress","PublicIpAddress","osType","OS","100","DATA","1000","BACKUP","2000"

Single property, single value.

Now, we need to discuss what I believe is a fundamental flaw in your code snippet. You are creating this custom object inside of

$report = foreach($n in $nic)
{

}

Which means you are going to get an object per NIC. Unless you are only always going to have 1 NIC per machine, but if so then why run it through a NIC loop?

I hope this gets you on the right track.

For JSON I guess you don’t export to it like csv, you convert and then take it out. However you can see what JSON would look like with the snipped below. This is a multidimensional storage format.

$disks = @(
    @{Name="OS";disksizeGB=100},
    @{Name="DATA";disksizeGB=1000}
    @{Name="BACKUP";disksizeGB=2000}
)

$object = [PSCustomObject]@{
    VMName = "VMName"
    ResourceGroupName = "ResourceGroupName"
    Interface = "NetName"
    Adaptor = "Name"
    PrivIP = "PrivateIpAddress"
    PublicIP = "PublicIpAddress"
    OStype = "osType"
    Disks = $disks
}

$object | ConvertTo-Json

Output

{
    "VMName": "VMName",
    "ResourceGroupName": "ResourceGroupName",
    "Interface": "NetName",
    "Adaptor": "Name",
    "PrivIP": "PrivateIpAddress",
    "PublicIP": "PublicIpAddress",
    "OStype": "osType",
    "Disks": [
                  {
                      "disksizeGB": 100,
                      "Name": "OS"
              },
                  {
                      "disksizeGB": 1000,
                      "Name": "DATA"
              },
                  {
                      "disksizeGB": 2000,
                      "Name": "BACKUP"
                  }
              ]
}

Thanks for the help when I run your CSV code it still gives me

System.Object[]
System.Object[]
System.Object[]
System.Object[]

You’ll have to show what you ran and some sample data. My code has dummy data, and is incomplete.

$vms = Get-Azvm
$nics = Get-AzNetworkInterface | ?{ $_.VirtualMachine -NE $null}
$disks = @(
@{Name="OS";disksizeGB=100},
@{Name="DATA";disksizeGB=1000}
@{Name="BACKUP";disksizeGB=2000}
)
foreach($nic in $nics)
{
$object = [PSCustomObject]@{
VMName = "VMName"
ResourceGroupName = "ResourceGroupName"
Interface = "NetName"
Adaptor = "Name"
PrivIP = "PrivateIpAddress"
PublicIP = "PublicIpAddress"
OStype = "osType"
}
}
$count = 1
foreach($d in ($disks | where {$_}))
{
$d.name
Add-member -InputObject $object -NotePropertyName "Disk$($count)Name" -NotePropertyValue $d.name
Add-member -InputObject $object -NotePropertyName "Disk$($count)Size" -NotePropertyValue $d.disksizeGB
$count++
}
$report | Export-Csv -Path c:\temp\temp.csv
 
ResourceGroupName VmName OStype Interface Adaptor PrivIP PublicIP Disk
ABC Server1 Windows interface1 ipconfig1 127.0.0.0 System.Object[]
ABC Server2 Windows interface2 ipconfig1 127.0.0.1 System.Object[]
ABC Server3 Windows interface3 ipconfig1 127.0.0.2 System.Object[]
ABC Server4 Windows interface4 ipconfig1 127.0.0.3 System.Object[]
ABC Server5 Windows interface5 ipconfig1 127.0.0.4 System.Object[]
ABC Server6 Windows interface6 ipconfig1 127.0.0.5 System.Object[]
ABC Server7 Windows interface7 ipconfig1 127.0.0.6 System.Object[]
ABC Server8 Windows interface8 ipconfig1 127.0.0.7 System.Object[]
ABC Server9 Windows interface9 ipconfig1 127.0.0.8 System.Object[]
ABC Server10 Windows interface10 ipconfig1 127.0.0.9 System.Object[]
ABC Server11 Windows interface11 ipconfig1 127.0.0.10 System.Object[]
ABC Server12 Windows interface12 ipconfig1 127.0.0.11 System.Object[]
ABC Server13 Windows interface13 ipconfig1 127.0.0.12 System.Object[]
ABC Server14 Windows interface14 ipconfig1 127.0.0.13 System.Object[]
ABC Server15 Windows interface15 ipconfig1 127.0.0.14 System.Object[]
ABC Server16 Windows interface16 ipconfig1 127.0.0.15 System.Object[]
ABC Server17 Windows interface17 ipconfig1 127.0.0.16 System.Object[]
ABC Server18 Windows interface18 ipconfig1 127.0.0.17 System.Object[]
ABC Server19 Windows interface19 ipconfig1 127.0.0.18 System.Object[]
ABC Server20 Windows interface20 ipconfig1 127.0.0.19 System.Object[]
ABC Server21 Windows interface21 ipconfig1 127.0.0.20 System.Object[]
ABC Server22 Windows interface22 ipconfig1 127.0.0.21 System.Object[]