What to do with a missing column?

Graywalker · June 9, 2022, 6:59pm

So, I have this command I am trying to convertfrom-csv - but one entry can have a blank - which gets lost…
The result will be like -

User Dept State Zip Time Location
Me IT MO 123456 12:30 Main
You MO 123456 1:30 Downtown

So,

Select-Object $Result -Skip 1 | ForEach-Object {($_ -replace '\s{2,}',',')}

That replaces each space with a comma and double commas with single…
getting -

Me,IT,MO,123456,12:30,Main
You,MO,123456,1:30,Downtown

Then
Me,IT,MO,123456,12:30,Main
You,MO,123456,1:30,Downtown

So… I would convert that from CSV - but the second line is missing a column!! Which throws everything off.

How to compensate for that missing column??

CommandToGetResult |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header

Olaf · June 9, 2022, 7:58pm

Bobby,
Welcome to the forum.

If I got it right then the column is not missing - it is empty. And you should not mess with it as it makes your input CSV valid.

I’d recomend to make sure your input data comes from a reliable source with a reliable format instead of trying to fix or work around possible missing data later on.

BTW: When you post sample data or console output you should format it as code as well. This way we can copy this data to try to reproduce your situation.

Thanks in advance.

Graywalker · June 10, 2022, 1:36am

The result is from QUSER - Its not a CSV file, so I can’t control it.

The results are like:

PS C:\WINDOWS\system32> quser
 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME
>bkearan               console             1  Active    1+03:57  6/6/2022 2:41 PM
 bktest                                    2  Disc      1+03:57  6/8/2022 4:38 PM

So, the SESSION NAME portion is, in fact “missing”
With the code posted at the end of the OP, it becomes:

bobbyk,console,1,Active,none,5/10/2022 10:47 PM
bktest,2,Disc,20.03,5/31/2022 7:53 AM

making the 2nd have invalid entries… ie, “2” is not the second session name.

Snippets from the actual code I have so far to get this output:

$Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
quser /server:$CN_Item |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header |
                    ForEach-Object {
                        #Write-Host $_.IdleTime
                        write-host $_
                        if (($_.IdleTime -eq 'none') -or ($_.IdleTime -eq '.') -or ($_.IdleTime -eq $Null) )
                            {
                            $IdleTime = $Null
                            }
                            else
                            {
                            If ($_.IdleTime -as [DateTime]) {
                                $IdleTime = New-TimeSpan -Start $_.IdleTime -End (Get-Date)
                                }
                                Else {
                                $IdleTime = [timespan]$_.IdleTime
                                }
                            }
                        [PSCustomObject]@{
                            ComputerName = $CN_Item
                            UserName = $_.UserName
                            SessionName = $_.SessionName
                            ID = $_.ID
                            State = $_.State
                            IdleTime = $IdleTime
                            LogonTime = $_.LogonTime
                            }
                        }                
                }

Olaf · June 10, 2022, 6:48am

Ah … I see … there’s an easy solution. Since quser outputs the table with fixed width I’d parse it with the substring() method instead. This way you’d get an empty cell for the line where the SESSIONNAME is missing.

Depending on your actual goal you could use another way to query what you’re after. By WMI/CIM for example.

Graywalker · June 10, 2022, 12:59pm

I’m not sure how that would work - as user names are not always the same length, nor is “Active” and “Disc” the same length… there will not be a length that will match every output. ?

Graywalker · June 10, 2022, 1:14pm

Well, I got something that MAY work for all instances…

quser /server:$CN_Item |
                    Select-Object -Skip 1 |
                    ForEach-Object {($_ -replace '\s{25}',',NoSession')} |
                    ForEach-Object {($_ -replace '\s{2,}', ',').Trim()} |
                    ConvertFrom-Csv -Header $Header |
                    ForEach-Object {

Not as elegant as I’d like, but if there are at least 25 spaces, its fairly safe (?) to assume that the “Session Name” is missing…

Olaf · June 10, 2022, 1:15pm

Since the table headers define the length of each column you simply use this and trim all unneeded spaces. No big deal.

Olaf · June 10, 2022, 1:18pm

I wouldn’t like to depend on that. But hey … it’s a free world. As long as it works for you.

Graywalker · June 10, 2022, 1:33pm

I just don’t see a reliable way to implement the substring() method given the actual output when having at least two logged on users.

I got started on this from : https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/

I am encountering a lot of things that the posters of the original code found there didn’t anticipate.
“Theory looks great until applied in practice.”

Like now, I am having to go back and put in something to account for if the Idle Time is more than 1 day - which ends up with an idle time of 1+15:39, which throws errors when converting to timespan -

PS C:\WINDOWS\system32> $Result
,USERNAME,,,,,,,,,,,,,,SESSIONNAME,,,,,,,,ID,,STATE,,,IDLE,TIME,,LOGON,TIME
>bkearan,,,,,,,,,,,,,,,console,,,,,,,,,,,,,1,,Active,,,,1+15:39,,6/6/2022,2:41,PM
,bktest,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,2,,Disc,,,,,,1+15:39,,6/8/2022,4:38,PM

What seems brilliant in the lab, doesn’t always work in the real world. The ‘code’ above shows the output with all the spaces replaced with commas… it doesn’t look like the headers define the column length. Just doesn’t match up.

Olaf · June 10, 2022, 1:47pm

Look closer!

For the Quser output you shared you could do it like this:

$QuserOutput = @'
 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME
>bkearan               console             1  Active    1+03:57  6/6/2022 2:41 PM
 bktest                                    2  Disc      1+03:57  6/8/2022 4:38 PM
'@ -split "`n" |
    Select-Object -Skip 1

$UserList =
foreach ($line in $QuserOutput) {
    [PSCustomObject]@{
        UserName    = $line.Substring(1, 22).trim()
        SessionName = $line.Substring(23, 19).trim()
        ID          = $line.Substring(42, 3).trim()
        State       = $line.Substring(46, 8).trim()
        IdleTime    = $line.Substring(54, 11).trim()
    }
}
$UserList | Format-Table -AutoSize

The output would look like this:

UserName SessionName ID State  IdleTime
-------- ----------- -- -----  --------
bkearan  console     1  Active 1+03:57
bktest               2  Disc   1+03:57

Graywalker · June 10, 2022, 3:39pm

Here is the final, working code

#
# Complied from pieces of code and additions by Bobby Kearan with inspiration from
# https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/
# and Thanks to Olaf (https://forums.powershell.org/u/Olaf) for helping with parsing the output of quser properly
#
#

function Get-QUserInfo
    {
    [CmdletBinding()]
    Param (
        [Parameter ()]
            [string[]]
            $ComputerName = $env:COMPUTERNAME
        )

    begin
        {
        $Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
        $No_Connection = '-- No Connection --'
        }

    process
        {
        foreach ($CN_Item in $ComputerName)
            {
            if (Test-Connection -ComputerName $CN_Item -Count 1 -Quiet)
                {
                $QuserOutput = quser /server:$CN_Item
                $QuserOutput -split "`n" | Select-Object -Skip 3
                    foreach ($line in $QuserOutput) {
                    
                    $IdleTime = $line.Substring(54, 11).trim()
                    #Write-Host $IdleTime
                    If (($IdleTime -eq 'none') -or ($IdleTime -eq '.') -or ($IdleTime -eq '$Null')) {
                    $IdleTime = "Not Idle"
                    }
                    IF ($IdleTime -like '*+*') {
                        $IdleTime = $IdleTime.replace("+",":")
                        #Write-Host $IdleTime " was the Plus sign replaced + ? "
                      }
              
                    If ($_.IdleTime -as [DateTime]) {
                          $IdleTime = New-TimeSpan -Start $IdleTime -End (Get-Date)
                          }
                        Else {

                          $IdleTime = [timespan]$IdleTime
                          }
                       
                    #Write-Host $IdleTime
                    $final = $line.length - 65
                    $Username = $line.Substring(1, 22).trim()
                    
                     [PSCustomObject]@{
                     UserName    = $line.Substring(1, 22).trim()
                     SessionName = $line.Substring(23, 19).trim()
                     ID          = $line.Substring(42, 3).trim()
                     State       = $line.Substring(46, 8).trim()
                     IdleTime    = $IdleTime
                     LogonTime   = $line.Substring(65, $final).trim()
                        }

    }
                }
                else
                {
                [PSCustomObject]@{
                    ComputerName = $CN_Item
                    UserName = $No_Connection
                    SessionName = $No_Connection
                    ID = $No_Connection
                    State = $No_Connection
                    IdleTime = $No_Connection
                    LogonTime = $No_Connection
                    }                
                }
            } # end >> foreach ($CN_Item in $ComputerName)
        } # end >> process {}

    end {}

    } # end >> function Get-QUserInfo

    $Info = Get-QUserInfo -ComputerName localhost

    $state = "Active"
    While($state = "Active"){
        $Info = Get-QUserInfo -ComputerName LocalHost
        ForEach ($user in $Info) {
        $IdleHou = $user.IdleTime.Hours
        $IdleMin = $user.IdleTime.Minutes
        Write-Host $user.State " User " $user.UserName " has been idle " $IdleHou " hours, " $IdleMin " Minutes"
        If (($user.State.Trim() -ne "Active") -and ($IdleHou -ge 1)) {
        #Write-Host $user.UserName "Should be logged off"
        logoff $user.ID
        }
        }
    Sleep -Seconds 350
    }

Please feel free to share improvements!!

Graywalker · June 10, 2022, 3:42pm

Here is the working code -

#
# Complied from pieces of code and additions by Bobby Kearan with inspiration from
# https://www.reddit.com/r/PowerShell/comments/8r56tr/getting_idle_time_for_logged_on_domain_users/
# and Thanks to Olaf (https://forums.powershell.org/u/Olaf) for helping with parsing the output of quser properly
#
#

function Get-QUserInfo
    {
    [CmdletBinding()]
    Param (
        [Parameter ()]
            [string[]]
            $ComputerName = $env:COMPUTERNAME
        )

    begin
        {
        $Header = 'UserName','SessionName','ID','State','IdleTime','LogonTime'
        $No_Connection = '-- No Connection --'
        }

    process
        {
        foreach ($CN_Item in $ComputerName)
            {
            if (Test-Connection -ComputerName $CN_Item -Count 1 -Quiet)
                {
                $QuserOutput = quser /server:$CN_Item
                $QuserOutput -split "`n" | Select-Object -Skip 1 |
                    ForEach-Object {
                    
                    $IdleTime = $_.Substring(54, 11).trim()
                    #Write-Host $IdleTime
                    If (($IdleTime -eq 'none') -or ($IdleTime -eq '.') -or ($IdleTime -eq '$Null')) {
                    $IdleTime = "Not Idle"
                    }
                    IF ($IdleTime -like '*+*') {
                        $IdleTime = $IdleTime.replace("+",":")
                        #Write-Host $IdleTime " was the Plus sign replaced + ? "
                      }
              
                    If ($_.IdleTime -as [DateTime]) {
                          $IdleTime = New-TimeSpan -Start $IdleTime -End (Get-Date)
                          }
                        Else {
                          If ($IdleTime -ne 'Not Idle') {
                          $IdleTime = [timespan]$IdleTime }
                          }
                       
                    #Write-Host $IdleTime
                    $final = $_.length - 65
                    $Username = $_.Substring(1, 22).trim()
                    
                     [PSCustomObject]@{
                     UserName    = $_.Substring(1, 22).trim()
                     SessionName = $_.Substring(23, 19).trim()
                     ID          = $_.Substring(42, 3).trim()
                     State       = $_.Substring(46, 8).trim()
                     IdleTime    = $IdleTime
                     LogonTime   = $_.Substring(65, $final).trim()
                        }

    }
                }
                else
                {
                [PSCustomObject]@{
                    ComputerName = $CN_Item
                    UserName = $No_Connection
                    SessionName = $No_Connection
                    ID = $No_Connection
                    State = $No_Connection
                    IdleTime = $No_Connection
                    LogonTime = $No_Connection
                    }                
                }
            } # end >> foreach ($CN_Item in $ComputerName)
        } # end >> process {}

    end {}

    } # end >> function Get-QUserInfo

    $Info = Get-QUserInfo -ComputerName localhost

    $state = "Active"
    While($state = "Active"){
        $Info = Get-QUserInfo -ComputerName LocalHost
        ForEach ($user in $Info) {
        $IdleHou = $user.IdleTime.Hours
        $IdleMin = $user.IdleTime.Minutes
        Write-Host $user.State " User " $user.UserName " has been idle " $IdleHou " hours, " $IdleMin " Minutes"
        If (($user.State.Trim() -eq "Disc") -and ($IdleHou -ge 1)) {
        #Write-Host $user.UserName "Should be logged off"
        logoff $user.ID
        }
        }
    Sleep -Seconds 160
    }

Please feel free to share improvements!
** Edited to correct an issue… **

Olaf · June 10, 2022, 6:53pm

Maybe you get it yourself …

Whatfor do you run this line of code:

$QuserOutput -split "`n" | Select-Object -Skip 3

??

Graywalker · June 10, 2022, 7:11pm

Well, for some reason, I was getting three blank lines in the output. ??? That line eliminates those blank entries. I adjusted it to also skip the Header line (see the edited reply).

Olaf · June 10, 2022, 7:27pm

Hmm … you changed much more than I would have. And the pointless line of code is still there.

What I meant is you run this line of code but you don’t use the output of it. Probably you started with one line and increased it when it didn’t provided the output you expected, didn’t you? IF you want to shorten the content of the variable you need to assign it to itself like this:

$QuserOutput = $QuserOutput -split "`n" | Select-Object -Skip 3

In the previous version of your code I would have changed the line above to this:

$QuserOutput = quser /server:$CN_Item | Select-Object -Skip 1

and would have deleted the line we’re discussing here completely.

Graywalker · June 10, 2022, 7:44pm

Then I wonder why it was (and is) working??

So weird… took that line out and it works the same. Edited the code above…

MrAutomation · June 12, 2022, 11:45am

This is all you need : quser | ConvertFrom-String

Good luck with it,

Kind regard MrAutomation

Graywalker · June 13, 2022, 12:43pm

That looked like it would be great - but has some problems of it’s own - like splitting up the string based on spaces, so “Logon Time” was split out to P8 : Logon P9 : Time
powershell_ise_bqHiFRqYVz

Topic		Replies	Views
CSV output is not in specific columns PowerShell Help	5	212	May 16, 2024
Saying my CSV doesn't have a column when it does... PowerShell Help	5	398	May 16, 2024
Text to column issues PowerShell Help	6	263	May 16, 2024
Join Header column PowerShell Help	4	212	May 16, 2024
Importing extra column from CSV and exporting it to a CSV PowerShell Help	4	231	May 16, 2024

What to do with a missing column?

Related topics