Add a row with custom value in the output

[ganapa2000]

Hi,

I am trying to add a 2nd row with custom value for CPU, Memory and HDD, which is used get department wise allocated budget and their usage.

If I dont add the row2, it gives me the Folder wise usage, but I would like to add allocated CPU, Memory and Capacity, so that I can get the used resources in row3.

Please help!!!

Script

  $report = Get-Folder RND | Get-VM | Group-Object -Property {$_.Folder.Id} | Select @{N="Cluster";E={Get-Cluster -VM $_.Group}},

  @{N="Folder Name";E={$folder = $_.Group[0].Folder; $path = $folder.Name; $path}}, 

  @{N="CPU";E={$_.Group | Measure-Object -Property NumCpu -Sum | Select -ExpandProperty Sum}},

  @{N="Memory(GB)";E={$_.Group | Measure-Object -Property MemoryGB -Sum | Select -ExpandProperty Sum}},

  @{N = "Capacity(GB)"; E = {

   $script:vdiskFree = ($_.Group.Guest.Disks | Measure-Object -Property FreeSpaceGB -Sum).Sum

   $script:vdiskCapacity = ($_.Group.Guest.Disks | Measure-Object -Property CapacityGB -Sum).Sum

   "{0:f1}" -f $script:vdiskCapacity

   }

   },

  @{N = "Used(GB)"; E = {"{0:f1}" -f ($script:vdiskCapacity - $script:vdiskFree)}},

  @{N = "FreeSpace(GB)"; E = {[math]::Round($script:vdiskCapacity - ($script:vdiskCapacity - $script:vdiskFree))}},

  @{N = "FreeSpace(%)"; E = {[math]::Round($script:vdiskFree / $script:vdiskCapacity * 100)}},

  @{N="Total VM's";E={$_.Group.Count}},

  @{N="PoweredOn VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOn"}).Count}},

  @{N="PoweredOff VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOff"}).Count}}

  $sum = $report | Measure-Object -sum "CPU", "Memory(GB)", "Capacity(GB)", "Used(GB)", "FreeSpace(GB)", "FreeSpace(%)", "Total VM's", "PoweredOn VM's", "PoweredOff VM's"

  $row1 = "" | Select "Folder Name","Cluster","CPU","Memory(GB)","Capacity(GB)","Used(GB)","FreeSpace(GB)","FreeSpace(%)","Total VM's","PoweredOn VM's","PoweredOff VM's"

  $report += $row1

  $row2 = "" | Select "","","1000","800","3076","","","","","",""

  $report += $row2

  $row3 = "" | Select "Folder Name","Cluster","CPU","Memory(GB)","Capacity(GB)","Used(GB)","FreeSpace(GB)","FreeSpace(%)","Total VM's","PoweredOn VM's","PoweredOff VM's"

  $row3."Folder Name" = 'Total'

  $row3.CPU = $sum | where{$_.Property -eq 'CPU'} | select -ExpandProperty Sum

  $row3."Memory(GB)" = $sum | where{$_.Property -eq "Memory(GB)"} | select -ExpandProperty Sum

  $row3."Capacity(GB)" = $sum | where{$_.Property -eq "Capacity(GB)"} | select -ExpandProperty Sum

  $row3."Used(GB)" = $sum | where{$_.Property -eq "Used(GB)"} | select -ExpandProperty Sum

  $row3."FreeSpace(GB)" = $sum | where{$_.Property -eq "FreeSpace(GB)"} | select -ExpandProperty Sum

  $row3."Total VM's" = $sum | where{$_.Property -eq "Total VM's"} | select -ExpandProperty Sum

  $row3."PoweredOn VM's" = $sum | where{$_.Property -eq "PoweredOn VM's"} | select -ExpandProperty Sum

  $row3."PoweredOff VM's" = $sum | where{$_.Property -eq "PoweredOff VM's"} | select -ExpandProperty Sum

  $report += $row3

  $report

[LucD]

To be honest I have no clue what you are trying to do.

Where are the values for $row2 coming from?

Are they static?

Perhaps you could share a mockup of the $report.

That might make things clearer.

[ganapa2000]

Hi LucD,

I want row2 CPU, Memory and Capacity values are static and get the difference allocated and available

Current Report

Desired Report

[LucD · Accepted solution]

You mean like this?

$report = @()

$allocated = "" | Select "Cluster","Folder Name","CPU","Memory(GB)","Capacity(GB)","Used(GB)","FreeSpace(GB)","FreeSpace(%)","Total VM's","PoweredOn VM's","PoweredOff VM's"

$allocated.'Folder Name' = 'Allocated'

$allocated.CPU = 1000

$allocated.'Memory(GB)' = 800

$allocated.'Capacity(GB)' = 3076

$report += $allocated

$report += Get-Folder RND | Get-VM | Group-Object -Property {$_.Folder.Id} | Select @{N="Cluster";E={Get-Cluster -VM $_.Group}},

  @{N="Folder Name";E={$folder = $_.Group[0].Folder; $path = $folder.Name; $path}},

  @{N="CPU";E={$_.Group | Measure-Object -Property NumCpu -Sum | Select -ExpandProperty Sum}},

  @{N="Memory(GB)";E={$_.Group | Measure-Object -Property MemoryGB -Sum | Select -ExpandProperty Sum}},

  @{N = "Capacity(GB)"; E = {

   $script:vdiskFree = ($_.Group.Guest.Disks | Measure-Object -Property FreeSpaceGB -Sum).Sum

   $script:vdiskCapacity = ($_.Group.Guest.Disks | Measure-Object -Property CapacityGB -Sum).Sum

   "{0:f1}" -f $script:vdiskCapacity

   }

   },

  @{N = "Used(GB)"; E = {"{0:f1}" -f ($script:vdiskCapacity - $script:vdiskFree)}},

  @{N = "FreeSpace(GB)"; E = {[math]::Round($script:vdiskCapacity - ($script:vdiskCapacity - $script:vdiskFree))}},

  @{N = "FreeSpace(%)"; E = {[math]::Round($script:vdiskFree / $script:vdiskCapacity * 100)}},

  @{N="Total VM's";E={$_.Group.Count}},

  @{N="PoweredOn VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOn"}).Count}},

  @{N="PoweredOff VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOff"}).Count}}

  $available = "" | Select "Folder Name","Cluster","CPU","Memory(GB)","Capacity(GB)","Used(GB)","FreeSpace(GB)","FreeSpace(%)","Total VM's","PoweredOn VM's","PoweredOff VM's"

  $available.'Folder Name' = 'Available'

  $available.CPU = $report[0].CPU - ($report[1..($report.Count -1)].CPU | Measure-Object -Sum).Sum

  $available.'Memory(GB)' = $report[0].'Memory(GB)' - ($report[1..($report.Count -1)].'Memory(GB)' | Measure-Object -Sum).Sum

  $available.'Capacity(GB)' = $report[0].'Capacity(GB)' - ($report[1..($report.Count -1)].'Capacity(GB)' | Measure-Object -Sum).Sum

  $report += $available

  $report

[ganapa2000]

Bulls Eye.....Perfect LucD. that worked.

Thank you very much.