Split text by columns in PowerShell

Question

I'm a PowerShell novice (Bash is my thing normally) who's currently trying to obtain qwinsta output to show who is logged in as an 'rdpwd' (rdesktop) user so that I can check each username against a list of usernames, and if they do not match, log them off.

I am currently working through two problems:

I am unable to split the qwinsta output to be left with only the username - I've tried the "split" function but so far am getting either syntax issues or weird results; one gripe seems to be that '\s+' matches the letter S instead of whitespace; other times I've managed to split to the second column, but only output from line 1 appears
Whilst I'm not there yet, I sense that I will have problems with the second step as well, namely looping through the array of non-log-offable users (which are to be obtained from a local user group)

I'll focus on problem 1 for now!

The text I've got is:

SESSIONNAME       USERNAME        ID     STATE   TYPE      DEVICE
services                          0      Disc
console                           1      Conn
rdp-tcp#0         user.name1      2      Active  rdpwd
rdp-tcp#1         user.name2      3      Active  rdpwd
rdp-tcp#1         user.name3      4      Active  rdpwd
rdp-tcp                           65536  Listen

The output I want is:

user.name1
user.name2
user.name3

(With the aim of then creating a loop that says, in brief terms, "foreach user in list, if not in localgroup, logoff user".)

So far, I've got as far as selecting text with 'rdpwd', but using all manner of variations on "split", I have not got further forward than that.

I'm happy to share what I've got already, but alas I don't think it'll help anyone!

Any assistance would be most appreciated. :)

I am assuming the text you mention above is a psobject array? In that case you can just do `$users = $array | select Username` which will give you an array of just `user.name1...` — Dane Boulton, Mar 18 '15 at 14:59
@DaneBoulton OP says the output comes from `qwinsta.exe` so that won't work — arco444, Mar 18 '15 at 15:04

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

Honestly I'd look up a better way to do this, but you can fudge it with some text manipulation and the ConvertFrom-Csv cmdlet:

$(qwinsta.exe) -replace "^[\s>]" , "" -replace "\s+" , "," | ConvertFrom-Csv | select username

Firstly replace any leading spaces or > characters with nothing, then replace any white spaces with a comma. Then you can pipe to ConvertFrom-Csv and work with the data as an object.

EDIT

Actually, the above has some issues, mostly with the \s+ because if a column is blank it does not get correctly recognised as a blank field, and the next text is incorrectly promoted to the current field.

The below is a full blown parser for this command, and would probably work for any sort of tabulated output from a native windows exe:

$o = @()
$op = $(qwinsta.exe)

$ma = $op[0] | Select-String "(?:[\s](\w+))" -AllMatches
$ErrorActionPreference = "Stop"

for($j=1; $j -lt $op.length; $j++) {
    $i = 0
    $obj = new-object pscustomobject
    while ($i -lt $ma.matches.count) { 
      $prop = $ma.matches[$i].groups[1].value; 
      $substrStart = $ma.matches[$i].index 
      $substrLen = $ma.matches[$i+1].index - $substrStart
      try {
        $obj | Add-Member $prop -notepropertyvalue $op[$j].substring($substrStart,$substrLen).trim() 
      }
      catch [ArgumentOutOfRangeException] {
        $substrLen = $op[$j].length - $substrStart 
        if($substrLen -gt 0) {
          $obj | Add-Member $prop -notepropertyvalue $op[$j].substring($substrStart,$substrLen).trim()
        }
        else {
          $obj | Add-Member $prop -notepropertyvalue ""
        }
      }
      $i++
    }
    $o += ,$obj
}

$o | ? { $_.type -eq 'rdpwd'} | select username

USERNAME
--------
user.name1
user.name2
user.name3

score 3 · Answer 2 · answered Mar 18 '15 at 15:12

Can't tell for sure, but it sounds like you're trying to do a regex split using the string .split() method. That doesn't work. Use the Powershell -split operator to do a regex split:

(@'
SESSIONNAME       USERNAME        ID     STATE   TYPE      DEVICE
services                          0      Disc
console                           1      Conn
rdp-tcp#0         user.name1      2      Active  rdpwd
rdp-tcp#1         user.name2      3      Active  rdpwd
rdp-tcp#1         user.name3      4      Active  rdpwd
rdp-tcp                           65536  Liste
'@).split("`n") |
foreach {$_.trim()} | sv x


$x -match 'rdpwd' |
foreach { ($_ -split '\s+')[1] }

user.name1
user.name2
user.name3

Matt · Answer 3 · 2015-03-18T19:15:25.653

My take of the position based delimiter. All the other answers get you the information you are looking for but much like Arco I was looking for a PowerShell object based answer. This assumes $data is populated with new line delimeted text like you would get from get-content could easily split the output from qwinsta.exe ($data = (qwinsta.exe) -split "`r`n" for example)

$headerString = $data[0]
$headerElements = $headerString -split "\s+" | Where-Object{$_}
$headerIndexes = $headerElements | ForEach-Object{$headerString.IndexOf($_)}

$results = $data | Select-Object -Skip 1  | ForEach-Object{
    $props = @{}
    $line = $_
    For($indexStep = 0; $indexStep -le $headerIndexes.Count - 1; $indexStep++){
        $value = $null            # Assume a null value 
        $valueLength = $headerIndexes[$indexStep + 1] - $headerIndexes[$indexStep]
        $valueStart = $headerIndexes[$indexStep]
        If(($valueLength -gt 0) -and (($valueStart + $valueLength) -lt $line.Length)){
            $value = ($line.Substring($valueStart,$valueLength)).Trim()
        } ElseIf ($valueStart -lt $line.Length){
            $value = ($line.Substring($valueStart)).Trim()
        }
        $props.($headerElements[$indexStep]) = $value    
    }
    [pscustomobject]$props
} 

$results | Select-Object sessionname,username,id,state,type,device | Format-Table -auto

This approach is based on the position of the header fields. Nothing is hardcoded and it is all custom build based on those indexes and field names. Using those $headerIndexes we carve up every line and place the results, if present, into its respective column. There is logic to ensure that we don't try and grab and part of the string that might not exist and treat the last field special.

$results would not contain your text as a custom psobject. Now you can do filtering like you would any other object collection.

Output from above sample

SESSIONNAME USERNAME   ID    STATE  TYPE  DEVICE
----------- --------   --    -----  ----  ------
services               0     Disc               
console                1     Conn               
rdp-tcp#0   user.name1 2     Active rdpwd       
rdp-tcp#1   user.name2 3     Active rdpwd       
rdp-tcp#1   user.name3 4     Active rdpwd       
rdp-tcp                65536 Listen

Now we show all usernames where the type is rdpwd

$results | Where-Object{$_.type -eq "rdpwd"} | Select-Object -ExpandProperty username

score 1 · Answer 4 · edited Aug 23 '17 at 00:13

1

Print field 4,5 and 6 in second column.

awk 'NR>3&&NR<7{print $2}' file

    user.name1
    user.name2
    user.name3

edited Aug 23 '17 at 00:13

Matt

45,022
8
78
119

answered Nov 30 '16 at 19:57

Claes Wikner

1,457
1
9
8

7

Please do not post bare code, also provide an explanation. – Jonathan Mee Nov 30 '16 at 21:26

score 1 · Answer 5 · answered Feb 13 '18 at 23:51

Some of the answers here commendably try to parse the input into objects, which, however, is (a) a nontrivial effort and (b) comes at the expense of performance.

As an alternative, consider text parsing using PowerShell's -split operator, which in its unary form splits lines into fields by whitespace similar to the standard awk utility on Unix platforms:

^{On Windows, if you first install an awk port such as Gawk for Windows, you could invoke awk directly, as demonstrated in Ed Morton's answer. On Unix (using PowerShell Core), awk is available by default.

The solution below is similar to Ed's, except that it won't perform as well.}

qwinsta | % { if (($fields = -split $_)[4] -eq 'rdpwd') { $fields[1] } }

-split $_ splits the input line at hand ($_) into an array of fields by runs of whitespace, ignoring leading and trailing whitespace.
(...)[4] -eq 'rdpwd' tests the 5th field (as usual, indices are 0-based) for the value of interest.
In case of a match, $fields[1] then outputs the 2nd field, the (assumed to be nonempty) username.

score 0 · Answer 6 · answered Mar 18 '15 at 15:28

Looks like there are a few answers on this but here's another one.

You could extract the substring from each line according to the position like this.

$Sessions=qwinsta.exe
$SessionCount=$Sessions.count
[int]$x=1
do
    {$x++
     if(($Sessions[$x]) -ne $null){$Sessions[$x].subString(19,21).Trim()}
    }until($x -eq $SessionCount)

score 0 · Answer 7 · answered Mar 18 '15 at 16:04

0

Do it exactly the same way you should if your shell was bash:

$ awk '$NF=="rdpwd"{print $2}' file 
user.name1
user.name2
user.name3

Caveat: I've no idea what "powershell" is but you tagged the question with awk so I assume "powershell" is some kind of shell and calling awk from it is an option.

answered Mar 18 '15 at 16:04

Ed Morton

188,023
17
78
185

That _almost_ works (assuming you're running PowerShell Core on Unix or, on Windows, you have an Awk port such as Gawk for Windows installed - and we now know the OP is using a Windows-only utility, `qwinsta`): sadly, though, PowerShell is quoting-challenged when it comes to the outside world and requires explicit `\ `-escaping of the embedded `"`: `awk '$NF==\"rdpwd\"{print $2}' file` However, a similar - though not quite as concise and definitely slower - solution is possible in PowerShell itself; see my answer. P.S: To learn about PowerShell, see https://microsoft.com/powershell – mklement0 Feb 13 '18 at 23:59

Bill_Stewart · Answer 8 · 2015-03-19T15:55:53.893

[Edit: I liked Matt's idea of dynamically determining the column names, so I updated my answer to a more robust solution.]

Here's one way:

# Get-SessionData.ps1
$sessionData = qwinsta
$headerRow = $sessionData | select-object -first 1
# Get column names
$colNames = $headerRow.Split(' ',[StringSplitOptions]::RemoveEmptyEntries)
# First column position is zero
$colPositions = @(0)
# Get remainder of column positions
$colPositions += $colNames | select-object -skip 1 | foreach-object {
  $headerRow.IndexOf($_)
}
$sessionData | select-object -skip 1 | foreach-object {
  # Create output object
  $output = new-object PSCustomObject
  # Create and populate properties for all except last column
  for ( $i = 0; $i -lt $colNames.Count - 1; $i++ ) {
    $output | add-member NoteProperty $colNames[$i] ($_[$($colPositions[$i])..$($colPositions[$i + 1] - 1)] -join "").Trim()
  }
  # Create property for last column
  $output | add-member NoteProperty $colNames[$colNames.Count - 1] ""
  # Remainder of text on line, if any, is last property
  if ( ($_.Length - 1) -gt ($colPositions[$colPositions.Count - 1]) ) {
    $output.$($colNames[$colNames.Count - 1]) = $_.Substring($colPositions[$colPositions.Count - 1]).Trim()
  }
  $output
}

This converts the command's output into custom objects that you can filter, sort, etc.

This means you could run the following command to get only the usernames where the TYPE column is rdpwd:

Get-SessionData | where-object { $_.TYPE -eq "rdpwd" } |
  select-object -expandproperty USERNAME

Output:

user.name1
user.name2
user.name3

score 0 · Answer 9 · answered Mar 18 '15 at 17:17

How about using running processes to look for explorer instances for the logged-in users? (Or some other process you know your users to be running):

Get-WmiObject -ComputerName "Machine" -Class win32_process | Where-Object {$_.Name -match "explorer"} | ForEach-Object {($_.GetOwner()).User}

Will provide all usernames associated with running explorer processes.

score 0 · Answer 10 · edited May 23 '17 at 12:09

I like Matt's answer for this, however it has issues with spaces in column headings (they are problematic in general, but sometimes you can't do much). Here's a tweaked, functionized version to help. Note you could probably tweak the preproc to include e.g. tabs or other delimiters but still relies on per-line indexes being constant.

function Convert-TextColumnsToObject([String]$data)
{
    $splitLinesOn=[Environment]::NewLine
    $columnPreproc="\s{2,}"
    $headerString = $data.Split($splitLinesOn) | select -f 1
    #Preprocess to handle headings with spaces
    $headerElements = ($headerString -replace "$columnPreproc", "|") -split "\|" | Where-Object{$_}
    $headerIndexes = $headerElements | ForEach-Object{$headerString.IndexOf($_)}
    $results = $data.Split($splitLinesOn) | Select-Object -Skip 1  | ForEach-Object{
        $props = @{}
        $line = $_
        For($indexStep = 0; $indexStep -le $headerIndexes.Count - 1; $indexStep++){
            $value = $null            # Assume a null value 
            $valueLength = $headerIndexes[$indexStep + 1] - $headerIndexes[$indexStep]
            $valueStart = $headerIndexes[$indexStep]
            If(($valueLength -gt 0) -and (($valueStart + $valueLength) -lt $line.Length)){
                $value = ($line.Substring($valueStart,$valueLength)).Trim()
            } ElseIf ($valueStart -lt $line.Length){
                $value = ($line.Substring($valueStart)).Trim()
            }
            $props.($headerElements[$indexStep]) = $value    
        }
        [pscustomobject]$props
    }

    return $results
}

Example:

$data= @"
    DRIVER              VOLUME NAME
    local               004e9c5f2ecf96345297965d3f98e24f7a6a69f5c848096e81f3d5ba4cb60f1e
    local               081211bd5d09c23f8ed60fe63386291a0cf452261b8be86fc154b431280c0c11
    local               112be82400a10456da2e721a07389f21b4e88744f64d9a1bd8ff2379f54a0d28
    "@ 

$obj=Convert-TextColumnsToObject $data
$obj | ?{ $_."VOLUME NAME" -match "112be" }

iRon · Answer 11 · 2018-07-11T06:59:10.037

I wrote a reusable ConvertFrom-SourceTable cmdlet which is available for download at the PowerShell Gallery and the source code from the GitHub iRon7/ConvertFrom-SourceTable repository.

$Object = ConvertFrom-SourceTable '
SESSIONNAME       USERNAME        ID     STATE   TYPE      DEVICE
services                          0      Disc
console                           1      Conn
rdp-tcp#0         user.name1      2      Active  rdpwd
rdp-tcp#1         user.name2      3      Active  rdpwd
rdp-tcp#1         user.name3      4      Active  rdpwd
rdp-tcp                           65536  Listen
'

It pretty flexible and capable of reading a lot of table format including reading the output of the results. Or even if e.g. the ID column is right aligned meaning that it would concern integers rather than strings:

$Object = ConvertFrom-SourceTable '
   ID TYPE  USERNAME   STATE  DEVICE SESSIONNAME
   -- ----  --------   -----  ------ -----------
    0                  Disc          services
    1                  Conn          console
    2 rdpwd user.name1 Active        rdp-tcp#0
    3 rdpwd user.name2 Active        rdp-tcp#1
    4 rdpwd user.name3 Active        rdp-tcp#1
65536                  Listen        rdp-tcp
'

For details see: ConvertFrom-SourceTable -?

score -1 · Answer 12 · edited Dec 01 '15 at 10:24

-1

a simple way

get list of active users only

$logonusers = qwinsta /server:ts33 | Out-String -Stream | Select-String "Active"

clears all the info up apart from the users, with -replace command

$logonusers = $logonusers -replace("rdp-tcp") -replace("Active") -
replace("rdpwd") -replace("#") -replace '\s+', ' ' -replace '[0-9]',' '

$logonusers

will then list all the active users.

edited Dec 01 '15 at 10:24

Kumar Saurabh

2,297
5
29
43

answered Dec 01 '15 at 09:42

paul marsh

1

Split text by columns in PowerShell

12 Answers12

EDIT

Linked