Extract columns from text based table output

Question

qfarm /load command shows me the load from my servers. Output:

PS> qfarm /load

Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons

I need to display only first column (Server Name) and the second one (Server Load) and loop through them, in order to make some logic later, but it seems the powershell doesn't see it as object with properties:

PS> qfarm /load | Select -ExpandProperty "Server Name"
Select-Object : Property "Server Name" cannot be found.

Is there any other possibility, like a table or something?

Solution 1

One way to do this is to build objects out of the command's output. Tested the following:

#requires -version 3

# sample data output from command
$sampleData = @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@ -split "`n"

$sampleData | Select-Object -Skip 2 | ForEach-Object {
  $len = $_.Length
  [PSCustomObject] @{
    "ServerName"         = $_.Substring(0,  22).Trim()
    "ServerLoad"         = $_.Substring(22, 13).Trim() -as [Int]
    "LoadThrottlingLoad" = $_.Substring(35, 22).Trim()
    "LogonMode"          = $_.Substring(57, $len - 57).Trim()
  }
}

In your case, you should be able to replace $sampleData with your qfarm load command; e.g.:

qfarm /load | Select-Object -Skip 2 | ForEach-Object {
...

Of course, this is assuming no blank lines in the output and that my column positions for the start of each item is correct.

PowerShell version 2 equivalent:

#requires -version 2

function Out-Object {
  param(
    [Collections.Hashtable[]] $hashData
  )
  $order = @()
  $result = @{}
  $hashData | ForEach-Object {
    $order += ($_.Keys -as [Array])[0]
    $result += $_
  }
  New-Object PSObject -Property $result | Select-Object $order
}

# sample data output from command
$sampleData = @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@ -split "`n"

$sampleData | Select-Object -Skip 2 | ForEach-Object {
  $len = $_.Length
  Out-Object `
    @{"ServerName"         = $_.Substring(0,  22).Trim()},
    @{"ServerLoad"         = $_.Substring(22, 13).Trim() -as [Int]},
    @{"LoadThrottlingLoad" = $_.Substring(35, 22).Trim()},
    @{"LogonMode"          = $_.Substring(57, $len - 57).Trim()}
}

Solution 2

You can easily convert your table to PowerShell objects using the ConvertFrom-SourceTable cmdlet from the PowerShell Gallery:

$sampleData = ConvertFrom-SourceTable @"
Server Name           Server Load  Load Throttling Load  Logon Mode
--------------------  -----------  --------------------  ------------------
SERVER-01             400          0                     AllowLogons
SERVER-02             1364         OFF                   AllowLogons
SERVER-03             1364         OFF                   AllowLogons
SERVER-04             1000         0                     AllowLogons
SERVER-05             700          0                     AllowLogons
SERVER-06             1200         0                     AllowLogons
"@

And than select your columns like:

PS C:\> $SampleData | Select-Object "Server Name", "Server Load"

Server Name Server Load
----------- -----------
SERVER-01   400
SERVER-02   1364
SERVER-03   1364
SERVER-04   1000
SERVER-05   700
SERVER-06   1200

For details see: ConvertFrom-SourceTable -?

_{The ConvertFrom-SourceTable cmdlet is available for download at the PowerShell Gallery and the source code from the GitHub iRon7/ConvertFrom-SourceTable repository.}

Solution 3

Command-line utilities return their outputs as a string array. This should work:

qfarm /load | ForEach-Object { $_.Substring(0,33) }

Comments

[G42]

run (qfarm /load).GetType() to see what datatype PowerShell sees it as. I assume String.

[Maximilian Burszley]

@gms0ulman All command-line tools return strings

[catalin]

gms0ulman the output is System.Array.

[Matt]

Very close duplicate in my mind: stackoverflow.com/questions/29125337/…

[Maximilian Burszley]

Wouldn't a better practice be to type-cast in front and error out rather than -as [type] and end up with an empty property?

[Bill_Stewart]

Possibly; that's a judgment call that depends on the output of the command.

[catalin]

Unfortunately my version is 2. The PSCustomObject is not recognized.

[Matt]

Do you remember this Bill? Sure that could work here as well

[catalin]

@Bill_Stewart yes, this works on v2 also. Now I'm trying to understand all the code, so I can extract just the Server Name column and the Server Load column, so I can place them into arrays and make logic with them later.

[Bill_Stewart]

Put the code in its own script and run the script. You can then pipe the script's output to Select-Object, Measure-Object, ForEach-Object, etc. because you now have objects.

[Bill_Stewart]

@Matt it would need some changes because the header column names here contain spaces. But the principles are there...

[Bill_Stewart]

@cata great! Don't forget to accept the answer if it works for you.

[Bill_Stewart]

I think probably the Server Load property should be type [Int].

[Matt]

Yes. That could be useful but depending on what you want you can rely on LHS typing or force it when you doing the calculation. That logic could certainly be useful but goes beyond the scope of my function.