Powershell table - Rows to columns

Question

I have array of objects which outputs on console like this

$VM | %{ $_ | Get-Stat -Stat $metrics -Realtime -Start (Get-Date).AddMinutes(-2) -Instance ""} | Format-Table -AutoSize

MetricId                Timestamp                          Value Unit     
--------                ---------                          ----- ----     
cpu.ready.summation     25.9.2014 15:23:40                    39 milli      
cpu.ready.summation     25.9.2014 15:23:20                    50 milli    
cpu.ready.summation     25.9.2014 15:23:00                    36 milli   
cpu.ready.summation     25.9.2014 15:22:40                    44 milli   
cpu.ready.summation     25.9.2014 15:22:20                    43 milli    
cpu.ready.summation     25.9.2014 15:22:00                    46 milli   
cpu.demand.average      25.9.2014 15:23:40                    37 MHz              
cpu.demand.average      25.9.2014 15:23:20                    37 MHz              
cpu.demand.average      25.9.2014 15:23:00                    37 MHz              
cpu.demand.average      25.9.2014 15:22:40                    38 MHz              
cpu.demand.average      25.9.2014 15:22:20                    40 MHz              
cpu.demand.average      25.9.2014 15:22:00                    41 MHz              
cpu.usagemhz.average    25.9.2014 15:23:40                    26 MHz              
cpu.usagemhz.average    25.9.2014 15:23:20                    52 MHz              
cpu.usagemhz.average    25.9.2014 15:23:00                    29 MHz              
cpu.usagemhz.average    25.9.2014 15:22:40                    28 MHz              
cpu.usagemhz.average    25.9.2014 15:22:20                    51 MHz              
cpu.usagemhz.average    25.9.2014 15:22:00                    32 MHz

How to change the output like this ?

25.9.2014 15:23:40  39 milli    37 MHz      26 MHz
25.9.2014 15:23:20  50 milli    37 MHz      52 MHz
.
.
.

If you could post the code that lets you view the table then I might be able to help — Shane Callanan
– Shane Callanan, Commented Sep 25, 2014 at 16:11

TheMadTechnician · Accepted Answer · 2014-09-25 17:43:12Z

Looks like a good time to use Group-Object on TimeStamp, then create an object per timestamp, and add members for each record for that timestamp:

$data = $VM | %{ $_ | Get-Stat -Stat $metrics -Realtime -Start (Get-Date).AddMinutes(-2) -Instance ""}
$Converted = $Data | Group TimeStamp | %{
    $Record = [pscustomobject][ordered]@{'TimeStamp'=$_.Name}
    $_.Group |%{
        Add-Member -InputObject $Record -MemberType NoteProperty -Name $_.MetricId -Value $_.Value
        Add-Member -InputObject $Record -MemberType NoteProperty -Name ($_.MetricId + ' Unit') -Value $_.Unit
    }
    $Record
}
$Converted|ft

That would output:

TimeStamp          cpu.ready.summation cpu.ready.summation Unit cpu.demand.average cpu.demand.average Unit cpu.usagemhz.average cpu.usagemhz.average Unit
---------          ------------------- ------------------------ ------------------ ----------------------- -------------------- -------------------------
25.9.2014 15:23:40 39                  milli                    37                 MHz                     26                   MHz                      
25.9.2014 15:23:20 50                  milli                    37                 MHz                     52                   MHz                      
25.9.2014 15:23:00 36                  milli                    37                 MHz                     29                   MHz                      
25.9.2014 15:22:40 44                  milli                    38                 MHz                     28                   MHz                      
25.9.2014 15:22:20 43                  milli                    40                 MHz                     51                   MHz                      
25.9.2014 15:22:00 46                  milli                    41                 MHz                     32                   MHz

The column headers are kind of long, but you could probably parse those back a bit with some substring or replace shenanigans.

Edit: For the sake of shortening the headers you could replace both instances of $_.MetricId with $_.MetricId.Split('.')[1] and it would shorten them to 'ready', 'demand', and 'usagemhz' with their related unit headers being similarly named. That makes it much more readable.

Alexander Obersht · Accepted Answer · 2014-09-25 18:50:04Z

One way to do it is to create a new collection and manually format objects as needed:

$raw = $VM | %{ $_ | Get-Stat -Stat $metrics -Realtime -Start (Get-Date).AddMinutes(-2) -Instance ""}

$joined = @()

foreach ($raw_object in $raw) {
    $need_new_object = ($joined | Select-Object -ExpandProperty Timestamp) `
        -notcontains $raw_object.Timestamp
    $value = $raw_object.Value + " " + $raw_object.Unit

    if ($need_new_object) {
        $object = New-Object PSObject -Property @{
            "Timestamp" = $raw_object.Timestamp
        }

        $joined += $object
    } else {
       $object = `
            $joined | Where-Object { $_.Timestamp -eq $raw_object.Timestamp }
    }

    $object | Add-Member -MemberType "NoteProperty" `
        -ErrorAction "SilentlyContinue" -Name $raw_object.MetricId -Value $value

}

$joined | Format-Table -AutoSize

The output is exactly what you wanted:

Timestamp          cpu.ready.summation cpu.demand.average cpu.usagemhz.average
---------          ------------------- ------------------ --------------------
25.9.2014 15:23:40 39 milli            37 MHz             26 MHz              
25.9.2014 15:23:20 50 milli            37 MHz             52 MHz              
25.9.2014 15:23:00 36 milli            37 MHz             29 MHz              
25.9.2014 15:22:40 44 milli            38 MHz             28 MHz              
25.9.2014 15:22:20 43 milli            40 MHz             51 MHz              
25.9.2014 15:22:00 46 milli            41 MHz             32 MHz

Collectives™ on Stack Overflow

Powershell table - Rows to columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related