rstack
Contributor
Contributor

Arrays in arrays in a report.

Jump to solution

I have looked all over and I am having a hard time finding what I need. I am fairly new to complex scripting so I am hoping someone here can help.

I am trying to create a report for vms. I don't want to post the whole script here as it is long due to menus and such as there may be some not so technical people running it, and want to make it as easy as possible.

I am using the Powershell Module ImportExcel to export it into a spreadsheet, also mutipule csv files if module not installed.

So to export to one of the worksheets, I have as follows

$VMData2 = @()

        $infoObject2= New-Object PSObject

        #Second Page of Excel / Csv - Guest Info

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Name" -value $VM

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Folder" -value $VMSystem.Folder.Name

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "OS" -value $VMSystem.Guest.OSFullName

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Power State" -value $VMSystem.PowerState

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "CPU" -value $VMSystem.NumCpu

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Cores Per Socket" -value $VMSystem.CoresPerSocket

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Memory" -value $VMSystem.MemoryGB

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "# Drives Attached" -value $VMDisk.Length

        Add-Member -inputObject $infoObject2 -memberType NoteProperty -name "Guest Disk" -value $VMDiskInfo

        $VMData2 += $infoObject2

What I am trying to do is for guest disk is pull info from all of the disks basically just path, Capacity GB, Freespace in GB, and freespace %. Have it populate across columns so one server is on a row.

Something like

Disk 1 Cap | Disk 1 Free | Disk 1 Free% | Disk 2 Cap | etc

for how ever many disks are connected. That is part of the issue as there can be 1 disk or there can be 10+ in our environment.

I did try and use an array to pull the disk info and it grabs it correctly, I am just not sure how to format it correctly to export on each on a new row

$VMDisk = $VMSystem.Guest.Disks| Sort-Object -Property path

        foreach ($Disk in $VMDisk)

        {

        $GuestDisk = New-Object PSObject

        Add-Member -inputObject $GuestDisk -memberType NoteProperty -Name "Disk$($index) path" -value $Disk.Path

        Add-Member -inputObject $GuestDisk -memberType NoteProperty -Name "Disk$($index) Capacity(GB)" -Value ([math]::Round($Disk.Capacity/ 1GB))

        Add-Member -inputObject $GuestDisk -memberType NoteProperty -Name "Disk$($index) FreeSpace(GB)" -Value ([math]::Round($Disk.FreeSpace / 1GB))

        Add-Member -inputObject $GuestDisk -memberType NoteProperty -Name "Disk$($index) FreeSpace(%)" -Value ([math]::Round(((100* ($Disk.FreeSpace))/ ($Disk.Capacity)),0))

        $index++

        }

        $VMDiskInfo = $VMDisk += $GuestDisk

$VMDiskInfo indexes and displays the data correctly for every disk it sees.

I know I can use the $VMDiskInfo[0].Path etc for each disk but if I put 10 of those lines in Add-Member and some are null it will throw an error. and I find myself writing more error correcting lines then I really want to as I have ErrorAction set to stop so I can catch and report them.

I would like to do the same for each nic attached but for now I just wrote some if / else statements to correct errors. I have attached what I have so far. If anyone could point in the right direction my head would be grateful as I keep banging on the desk.

Also if anyone sees some efficiencies that can be made i am open to suggestions.

Thanks!!!!

1 Solution

Accepted Solutions
LucD
Leadership
Leadership

When you have an array with rows with varying length, or in other words properties, it's not obvious to get all your data to the output.

When you display such an array on screen, or export it to a CSV or XLSX, it will be the "length" of the first row in the array, that determines how many properties will be displayed/exported.

One way of countering this is to sort your array, in descending order, on the number of properties in each row.

Something like this

$array | Sort-Object -Property {($_ | Get-Member -MemberType NoteProperty).COunt} -Descending

The disadvantage is the awkward order in which your result will be displayed/exported.


A better way, imho, is to dimension your output row on the maximum number of properties when you create it.

As an example, a rather simplified version of what you are doing.

First it creates the row ($obj) with the "fixed" properties.

Then it adds the maximum number of properties for a specific property that can vary over different rows.

In the sample below I first calculate the maximum number of harddisks a VM can have.

Then, for each row, I add the maximum number of properties, with an empty value.

The actual values for the properties, are filled in a subsequent step.

This way all rows in your array will have the exact same number of properties.


$vms = Get-VM

$maxHd = $vms | %{(Get-HardDisk -VM $_).Count} | Measure-Object -Maximum | select -ExpandProperty Maximum

$array = @()

foreach($vm in $vms){

    $obj = New-Object PSObject

   

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Name" -Value $vm

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Folder" -Value $vm.Folder.Name

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "OS" -Value $vm.Guest.OSFullName

   

    1..$maxHD | %{

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "Path$($_)" -Value ''

    }

    $i = 1

    Get-HardDisk -VM $vm | %{

        $obj."Path$($i)" = $_.Filename

        $i++

    }

   

    $array += $obj

}

$array | Export-Excel -Path C:\Temp\test.xlsx -WorkSheetname Test


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

4 Replies
LucD
Leadership
Leadership

When you have an array with rows with varying length, or in other words properties, it's not obvious to get all your data to the output.

When you display such an array on screen, or export it to a CSV or XLSX, it will be the "length" of the first row in the array, that determines how many properties will be displayed/exported.

One way of countering this is to sort your array, in descending order, on the number of properties in each row.

Something like this

$array | Sort-Object -Property {($_ | Get-Member -MemberType NoteProperty).COunt} -Descending

The disadvantage is the awkward order in which your result will be displayed/exported.


A better way, imho, is to dimension your output row on the maximum number of properties when you create it.

As an example, a rather simplified version of what you are doing.

First it creates the row ($obj) with the "fixed" properties.

Then it adds the maximum number of properties for a specific property that can vary over different rows.

In the sample below I first calculate the maximum number of harddisks a VM can have.

Then, for each row, I add the maximum number of properties, with an empty value.

The actual values for the properties, are filled in a subsequent step.

This way all rows in your array will have the exact same number of properties.


$vms = Get-VM

$maxHd = $vms | %{(Get-HardDisk -VM $_).Count} | Measure-Object -Maximum | select -ExpandProperty Maximum

$array = @()

foreach($vm in $vms){

    $obj = New-Object PSObject

   

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Name" -Value $vm

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Folder" -Value $vm.Folder.Name

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "OS" -Value $vm.Guest.OSFullName

   

    1..$maxHD | %{

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "Path$($_)" -Value ''

    }

    $i = 1

    Get-HardDisk -VM $vm | %{

        $obj."Path$($i)" = $_.Filename

        $i++

    }

   

    $array += $obj

}

$array | Export-Excel -Path C:\Temp\test.xlsx -WorkSheetname Test


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

rstack
Contributor
Contributor

Thank you!!!!!

My brain hurts a little less now.

Question though is bad to do that more then once say for each report?

As when I do something say like the following I get an error.

$vms = Get-VM

$maxHd = $vms | %{(Get-HardDisk -VM $_).Count} | Measure-Object -Maximum | select -ExpandProperty Maximum

$array = @()

foreach($vm in $vms){

    $obj = New-Object PSObject

   

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Name" -Value $vm

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Folder" -Value $vm.Folder.Name

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "OS" -Value $vm.Guest.OSFullName

   

    1..$maxHD | %{

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "Path$($_)" -Value ''

    }

    $i = 1

    Get-HardDisk -VM $vm | %{

        $obj."Path$($i)" = $_.Filename

        $i++

    }

        2..$maxHD | %{

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "CapacityGB$($_)" -Value ''

    }

    $i = 1

    Get-HardDisk -VM $vm | %{

        $obj."CapacityGB$($i)" = $_.CapacityGB

        $i++

    }

   

    $array += $obj

}

$array | Export-Excel -Path C:\Temp\test.xlsx -WorkSheetname Test


When I do something like this I get The property for 'CapacityGB1' cannot be found on this object. I am sure there is something simple I am missing.

0 Kudos
LucD
Leadership
Leadership

There is no need to do the Get-Harddisk twice.

You could combine this as follows.

$vms = Get-VM

$maxHd = $vms | %{(Get-HardDisk -VM $_).Count} | Measure-Object -Maximum | select -ExpandProperty Maximum

$array = @()

foreach($vm in $vms){

    $obj = New-Object PSObject

  

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Name" -Value $vm

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "Folder" -Value $vm.Folder.Name

    Add-Member -InputObject $obj -MemberType NoteProperty -Name "OS" -Value $vm.Guest.OSFullName

  

    1..$maxHD | %{

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "Path$($_)" -Value ''

        Add-Member -InputObject $obj -MemberType NoteProperty -Name "CapacityGB$($_)" -Value ''

    }

    $i = 1

    Get-HardDisk -VM $vm | %{

        $obj."Path$($i)" = $_.Filename

        $obj."CapacityGB$($i)" = $_.CapacityGB

        $i++

    }

    $array += $obj

}

$array | Export-Excel -Path C:\Temp\test.xlsx -WorkSheetname Test


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
rstack
Contributor
Contributor

Ahhhhhh.... No emoji for a light bulb.

Thank you so much!!!

0 Kudos