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!!!!
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
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
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.
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
Ahhhhhh.... No emoji for a light bulb.
Thank you so much!!!