Combining two psobject tables(arrays) into one

Welcome Forums General PowerShell Q&A Combining two psobject tables(arrays) into one

Viewing 8 reply threads
  • Author
    Posts
    • #259000
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      Hey guys, struggling a little on a script I’m trying to write that is meant to gather various stats across our environment into a single exec summary report and am hoping someone here could shed a little light on how to accomplish what I’m trying to accomplish.

      I have two separate pieces, one that collects vmware stats and one that collects netapp stats. Here is my code for that:

      Connect-VIServer -Server vcenter1, vcenter2

      $myVCCol = @()
      foreach($cluster in (get-cluster | sort Name)){
      $ClusterVMs = $Cluster | Get-VM
      $ClusterCPUCores = $Cluster.ExtensionData.Summary.NumCpuCores
      $ClusterAllocatedvCPUs = ($ClusterVMs | Measure-Object -Property NumCPu -Sum).Sum
      $ClusterEffectiveMemoryGB = [math]::round(($Cluster.ExtensionData.Summary.EffectiveMemory / 1KB),0)
      $ClusterAllocatedMemoryGB = [math]::round(($ClusterVMs | Measure-Object -Property MemoryMB -Sum).Sum / 1KB)
      $vcstats = New-Object -TypeName psobject -Property @{
      date = Get-Date -Format ddMMyyyy
      cluster = $cluster.Name
      clustercores = $ClusterCPUCores
      clusterassignedcores = $ClusterAllocatedvCPUs
      clusterram = $ClusterEffectiveMemoryGB
      clusterassignedram = $ClusterAllocatedMemoryGB
      }
      $vcstats = $vcstats | select date, cluster, clustercores, clusterassignedcores, clusterram, clusterassignedram
      $myvccol += $vcstats
      }

      $nacs = "nac1","nac2"
      $myNACol = @()
      foreach($nac in $nacs){
      Connect-NcController $nac -Credential $creds
      $filerTtl = 0
      $filerAvail = 0
      $filerUsed = 0
      foreach($aggr in (get-ncaggr | sort name)){
      $aggrTtl = [math]::Round($aggr.TotalSize / 1TB, 2)
      $aggrAvail = [math]::Round($aggr.Available / 1TB, 2)
      $aggrUsed = $aggrTtl - $aggrAvail
      $filerttl += $aggrTtl
      $filerAvail += $aggrAvail
      $filerUsed += $aggrUsed
      }
      $nastats = New-Object -TypeName psobject -Property @{
      date = Get-Date -Format ddMMyyyy
      filer = $nac
      filerttlspaceTB = $filerTtl
      filerusedspaceTB = $filerUsed
      fileravailspaceTB = $filerAvail
      }
      $nastats = $nastats | select date, filer, filerttlspaceTB, filerusedspaceTB, fileravailspaceTB
      $myNACol += $nastats
      }

      Both the $myVCCol and $myNACol objects are reporting all the data I need correctly, I’m just struggling on how to get them together into a single object that I can then send in a single table via an html email (much praise to Milo for their send-htmlemail function).

      I’m no powershell expert (as is probably apparent by my code 🙂 ) but I’ve tried stuff like $myVCCol += $myNACol and $mycols = $myVCCol, $myNCCol and doing a foreach loop to populate a new object, but neither is giving me my desired results.

      Something tells me I’m missing something obvious but for whatever reason, I just can’t seem to figure it out. Any pointers?

    • #259015
      Participant
      Topics: 12
      Replies: 541
      Points: 1,314
      Helping Hand
      Rank: Community Hero

      There’s no common property between the two tables/arrays. This is essentially not a PowerShell question but a SQL database join question.

    • #259018
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      Thanks you for your reply Sam. Since I built both tables as a psobject, if I were to create each table to have all the same properties, would that make it doable? I have 0 SQL experience so I’m not sure I would be able to go down that route just yet.

    • #259021
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      maybe something like this –

    • #259024
      Participant
      Topics: 16
      Replies: 1794
      Points: 3,300
      Helping Hand
      Rank: Community Hero

      Rather than trying to join data that cannot be correlated, you can send an email like this (there is no need for a Send-HTMLEmail function):

      If you were getting controller information for each VM, that makes sense to join it so you see correlated information for VM. Imagine you have something like this:

      How can you correlate those two seperate datasets? If you ‘joined’ on Color, only Orange would work.

      Now there is a ColorId (i.e. Foreign Key) associated with Id (i.e. Primary Key) that we can join on. As Sam stated, there is no way to associate a VM to a specific controller in the code you provided. There needs to be a relationship. You can join objects with no association by adding controller information to every VM, but what are you gaining?

    • #259027
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      Thank you Rob for your reply. I was able to confirm your first example works however, this is an executive summary report meant for my bosses boss who has grown accustom to the pretty tables that Milo’s send-htmlemail function outputs. The only real formatting requirements that I’ve been given is that it looks like all the other reports I’ve created using the aforementioned function and that it be in a single email. Here is a rough draft of what they are expecting the output to look like:

      Date Name Total pCores Assigned vCores Total RAM Assigned RAM Total Space TB Used Space TB Avail. Space TB
      09252020 cluster1 64 222 1480 1040 n/a n/a n/a
      09252020 cluster2 36 124 2200 1700 n/a n/a n/a
      09252020 filer1 n/a n/a n/a n/a 97 77 20
      09252020 filer2 n/a n/a n/a n/a 40 35 15

       

    • #259204
      Participant
      Topics: 12
      Replies: 541
      Points: 1,314
      Helping Hand
      Rank: Community Hero
    • #259690
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      Thank you for your reply Sam! This gets me 99% of the way there however the nacs are getting repeated in the output resulting in this table:

      Date Name Total pCores Assigned vCores Total RAM Assigned Ram Total Space TB Used Space Tb Avail Space TB
      29092020 cluster1 64 228 1480 1040 n/a n/a n/a
      29092020 cluster2 36 134 2251 1710 n/a n/a n/a
      nac1
      29092020 nac1 n/a n/a n/a n/a 97 77 20
      nac2
      29092020 nac2 n/a n/a n/a n/a 40 17 23

      I assumed it was something to do with the new-object being built outside of the nested foreach loop but when I put it inside the loop, it created even more duplicates. Any ideas what would be causing the blank duplicate rows?

    • #259732
      Participant
      Topics: 3
      Replies: 14
      Points: 26
      Rank: Member

      Update – figured out that it was this line causing the blank rows

      fixed it by doing this

      • This reply was modified 3 weeks, 6 days ago by grokkit. Reason: code formatting - please read the guide
      • This reply was modified 3 weeks, 6 days ago by ps_SeanOB.
      • This reply was modified 3 weeks, 6 days ago by ps_SeanOB.
      • This reply was modified 3 weeks, 6 days ago by ps_SeanOB.
Viewing 8 reply threads
  • You must be logged in to reply to this topic.