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

Welcome Forums General PowerShell Q&A Export to CSV return System.Object[] not the Data

Viewing 22 reply threads
  • Author
    Posts
    • #219984
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

      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.

    • #219990
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: Major Contributor

      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”.

    • #219993
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

      $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”

    • #220014
      Participant
      Topics: 5
      Replies: 322
      Points: 445
      Helping Hand
      Rank: Contributor

      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”
      
    • #220026
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

      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.

    • #220035
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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?

    • #220041
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

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

    • #220068
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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?

    • #220071
      js
      Participant
      Topics: 29
      Replies: 813
      Points: 2,450
      Helping Hand
      Rank: Community Hero
      [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
        ]
      }
      • This reply was modified 1 month, 2 weeks ago by js.
      • This reply was modified 1 month, 2 weeks ago by js.
    • #220083
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

      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

       

    • #220086
      js
      Participant
      Topics: 29
      Replies: 813
      Points: 2,450
      Helping Hand
      Rank: Community Hero

      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.

      • This reply was modified 1 month, 2 weeks ago by js.
    • #220095
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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.

    • #220107
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

      @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.

    • #220110
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

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

    • #220143
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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.

    • #220146
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      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"
                        }
                    ]
      }
      
      
    • #220722
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member

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

      System.Object[]
      System.Object[]
      System.Object[]
      System.Object[]
    • #220743
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

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

    • #220827
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member
      $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

       

    • #220830
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member
      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[]
    • #220836
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      Hi sreed,

      Be sure to post any code in “

      ” tags. You’ve replaced all your code with dummy data. I was only showing the method of running the disks through a foreach loop. You’ll still need your data. I’m not sure how the code you posted ended up with the output you show. Anyways, I took your original code and adjusted. Please give it a shot. Please note that the first time one of these servers has a second NIC added, you’ll get duplicated entries for that server.
      
      $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
      $count = 1
      foreach($d in $disks){
      Add-member -InputObject $info -NotePropertyName "Disk$($count)Name" -NotePropertyValue $d.name
      Add-member -InputObject $info -NotePropertyName "Disk$($count)Size" -NotePropertyValue $d.disksizeGB
      $count++
      }
      $report+=$info
      }
      $report | Sort-object PrivIP | Export-Csv -Path “c:\temp\AzureIP+Disk.Csv”
      
      

      Good luck.

    • #221193
      Participant
      Topics: 4
      Replies: 13
      Points: 82
      Rank: Member
      ResourceGroupName VmName OStype Interface Adaptor PrivIP PublicIP DiskName DiskSize
      Server1 Probe Windows probe ipconfig1 10.0.0.1 System.Object[]

       

      I changed the ResourceGroupName , VmName , and PrivIP to generic data but the output is the same. I still get the System.Object[] and nothing for DiskSize.

      • This reply was modified 1 month, 1 week ago by sreed7743.
      • This reply was modified 1 month, 1 week ago by sreed7743.
    • #221253
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      Does it show disk1name = object? I had a typo in it so I’m not sure how it gave you anything there. I recommend you work on running these commands one by one to find what each one outputs, what properties there are, how many there are, etc. I don’t have azure vms to test with so I can’t test any further. My guess based on the limited info here is you’ve got more than one disk and you’re trying to store them in one property. I tried showing some ways of looping over the drives, but you should confirm that is the actual issue. What is the output of

      write-output $disks
      
      
Viewing 22 reply threads
  • You must be logged in to reply to this topic.