Handling Data & Sorting Results in PowerShell

Question

I am having trouble manipulating the data returned from a PowerShell query.

My goal is to get all NAMEs that are great than 30 days old (>30d).

After that, I want to load the NAMEs of these results into an array.

The data returned from my query is as follows:

PS C:\Users\> $output
NAME                      STATUS   AGE
dread-gorge               Active   284d
dread-lagoon              Active   210d
carncier-basin            Active   164d
chantague-shallows        Active   164d
hilraine-loch             Active   311d
stangrave-waters          Active   271d

Running a 'Select-Object' or 'Sort-Object' to filter the output doesn't work.

$output | Select-Object -Property NAME

$output | Sort-Object NAME

Looks like `$output` contains one or more strings, not structured objects. How did you obtain this data in the first place? — Mathias R. Jessen, Nov 22 '21 at 14:32
It's output from a kubectl query that I ran in Powershell. So, in it's raw format it's JSON. — Kerbol, Nov 22 '21 at 14:36
In that case you'll probably want to run your query with `--output=json` and pipe it to `ConvertFrom-Json` — Mathias R. Jessen, Nov 22 '21 at 14:39
Is there a way that I can simply grab all the NAMEs from the output above? The JSON is not so easy to manipulate and get value from using ConvertFrom-Json. In some cases "yes" but, not in this instance. — Kerbol, Nov 22 '21 at 14:55
You could do something like `@($output -split '\r?\n' |Select -Skip 1 |ForEach-Object Trim) -replace '\s.+$'` — Mathias R. Jessen, Nov 22 '21 at 15:16
If the output really is space-delimited data, you could skip the first row of the output (the column names) and use a regular expression to grab the first token on each line. But (as Mathias pointed out) it would be better to parse as json data if possible (more precise and direct, less error-prone). — Bill_Stewart, Nov 22 '21 at 15:17

score 3 · Accepted Answer · answered Nov 22 '21 at 15:57

If $output is a single multiline string, you can do this:

$result = ($output -split '\r?\n' -replace '\s+', ',' | ConvertFrom-Csv | Where-Object {[int]($_.Age -replace '\D') -gt 30}).NAME

to get the Names in an array $result.

If $output is already an array of strings, do:

$result = ($output -replace '\s+', ',' | ConvertFrom-Csv | Where-Object {[int]($_.Age -replace '\D') -gt 30}).NAME

mklement0 · Answer 2 · 2021-11-22T16:01:45.390

Update:

For a superior solution that transforms the input into CSV format and parses it with ConvertFrom-Csv, see Theo's answer.
The solution below may be of interest for its text-parsing techniques.

As the comments note, it's generally better to make an external program emit structured text, such as JSON, because parsing for-display formatted text is inherently brittle.
According to the kubectl docs, you can use -o json to achieve that (as Mathias has also noted).

If the data is simple enough so that plain-text parsing is still an option, you can try the following, which relies on the unary form of the -split operator to break each line into fields:

# Sample output from kubectl.
# Note: Capturing output from an external programs such as kubectl
#       results in an *array of lines*.
#       The -split '\r?\n` operation splits the multiline here-string into
#       just that.
$kubeCtlOutput = @'
NAME                      STATUS   AGE
dread-gorge               Active   28d
dread-lagoon              Active   31d
carncier-basin            Active   16d
chantague-shallows        Active   164d
'@ -split '\r?\n'

# Process the array's lines.
[array] $namesOfInterest = 
  $kubeCtlOutput | 
    Select-Object -Skip 1 |
      ForEach-Object { 
        $name, $age = (-split $_)[0, -1]
        if ([int] ($age -replace '\D') -gt 30) {
          $name
        }
      }

$namesOfInterest # Print the result.

Output, showing that only the names of the two lines whose AGE is greater than 30 days were extracted:

dread-lagoon
chantague-shallows

This related question has answers that define generic helper functions that can parse columnar plain-text data into objects.

Handling Data & Sorting Results in PowerShell

2 Answers2