Add Columns to Array

tlauwk · November 1, 2022, 4:48am

Hi,

I’m trying to manipulate some text that looks like this:

Users of FeatureA:  (Total of 1 license issued;  Total of 1 license in use)
Users of FeatureB:  (Total of 3 license issued;  Total of 2 license in use)
    User.A ComputerA cryptictext (version) (ServerName/port processid), start Tue 10/30 7:20
    User.B ComputerB cryptictext (version) (ServerName/port processid), start Tue 11/1 8:48
Users of FeatureC:  (Total of 7 licenses issued;  Total of 3 licenses in use)
    User.A ComputerA cryptictext (version) (ServerName/port processid), start Tue 10/27 7:20
    User.B ComputerB cryptictext (version) (ServerName/port processid), start Tue 10/30 8:48
	User.C ComputerC cryptictext (version) (ServerName/port processid), start Tue 11/1 9:20
Users of FeatureD:  (Total of 1 license issued;  Total of 1 license in use)
Users of FeatureE:  (Total of 1 license issued;  Total of 1 license in use)
    User.C ComputerC cryptictext (version) (ServerName/port processid), start Tue 11/1 6:32

Thanks to @kryzdoug from an earlier thread, I’ve reused the code to get this (ignore “Total of X license in use” as its not accurate … instead count number of User.X below each Feature):

License	Total	Used
FeatureA	1	0
FeatureB	3	2
FeatureC	7	3
FeatureD	1	0
FeatureE	1	1

This is the code:

$Lines = & $PathToExe | Where-Object {$_ -match "Users" -or $_ -match "start"} | Out-String

$Report = $Lines -split "(?=Users)" | ForEach-Object {
    ForEach ($Line in $_ -split "\r\n"){
        If($Line -match "Users")
        {
			$Line = $Line -split " "
			$Current = [PSCustomObject]@{
				License = $Line[2].TrimEnd(":")
				Total = $Line[6]
				Used = 0
			}
        }
        ElseIf ($Line -match "start")
        {
            $Current.Used++
        }
    }
    $Current
    $Current = $null
}

$Report

I would now like to add another 3 columns to the array:

License	Total	Used	Name	Computer	CheckedOut
FeatureA	1	0			
FeatureB	3	2	User.A	ComputerA	10/30 7:20
					User.B	ComputerB	11/1 8:48
FeatureC	7	3	User.A	ComputerA	10/27 7:20
					User.B	ComputerB	10/30 8:48
					User.C	ComputerC	11/1 9:20
FeatureD	1	0			
FeatureE	1	1	User.C	ComputerC	11/1 6:32

I’ve tried within the “ElseIf” section the following:

$Line = $Line -split " "
$Current += [PSCustomObject]@{
    Name = $Line[4]
    Computer = $Line[5]
    CheckedOut = $Line[-2,-1] -join " "

But I’m way off the mark. Using Add-Member is even further off the mark. Any pointers in the right direction would be much appreciated.

Thanks.

Olaf · November 1, 2022, 8:12am

You want to add hierarchical information into a structured data format. That’ll need more logic in your code and might be error prone. And - at least in the sample data you shared here - there are data sets missing. There is no User using the license of FeatureA or FeatureD?

I’d recommend trying to get another data source where you already have this information in a structured format.

tlauwk · November 1, 2022, 11:07pm

Unfortunately, the datasource comes from a command line licensing tool and its output is not structured. The data I’ve pulled into $Lines are the only relevant information, namely license feature name and users who have checked it out.

FeatureA and FeatureD are correct in the sense that no user has checked out the license … hence its 0 under the Used column.

The end goal is to be able to view license usage and the Users who have checked it out. The “CheckedOut” column tells us who has been using it the longest (possibly being someone who has forgotten to check it back in before leaving the office).

Thanks.

krzydoug · November 2, 2022, 2:36am

Since you are showing hierarchical data, I recommend using the data as json.

$userspattern = 'Users.+?(\S+):.+(\d+).+issued.+(\d+).+in use'
$checkedpattern = '\s+(\S+)\s+(\S+)\s+crypt.+start\s+\w+\s+(\w.+)'

$output = $txt -split '(?=Users)' | ForEach-Object {
    foreach($line in $_ -split '\r?\n'){
        if($line -match $userspattern){
            $current = [ordered]@{
                License = $Matches.1
                Total   = $Matches.2
                Used    = $Matches.3
                Checked = @()
            }
        }
        elseif($line -match $checkedpattern){
            $current.Checked += [PSCustomObject]@{
                Name       = $Matches.1
                Computer   = $Matches.2
                CheckedOut = $Matches.3
            }
        }
    }

    if($current){
        [PSCustomObject]$current
    }

    Remove-Variable current -ErrorAction SilentlyContinue
}

$output | ConvertTo-Json

Alternatively if you wanted to try and maintain CSV, you’ll just have to duplicate data.

$userspattern = 'Users.+?(\S+):.+(\d+).+issued.+(\d+).+in use'
$checkedpattern = '\s+(\S+)\s+(\S+)\s+crypt.+start\s+\w+\s+(\w.+)'

$output = $txt -split '(?=Users)' | ForEach-Object {
    foreach($line in $_ -split '\r?\n'){
        if($line -match $userspattern){
            $current = [ordered]@{
                License    = $Matches.1
                Total      = $Matches.2
                Used       = $Matches.3
                Name       = ''
                Computer   = ''
                CheckedOut = ''
            }
        }
        elseif($line -match $checkedpattern){
            $current.Name       = $Matches.1
            $current.Computer   = $Matches.2
            $current.CheckedOut = $Matches.3

            [PSCustomObject]$current
        }
        else{
            [PSCustomObject]$current
        }
    }

    Remove-Variable current -ErrorAction SilentlyContinue
}

$output | Format-Table -AutoSize

tlauwk · November 2, 2022, 6:52am

Regex is hard, no question. Took me a while to get my head around $userspattern but got there in the end. Though I got stuck at understanding $Matches.{1,2,3} in the ordered hashtable. How does “-match $userspattern” turn into 3 different matches with the exact values (leaving out all the other detritus):

Users of FeatureA: (Total of 1 license issued; Total of 1 license in use)

Next, apologies for being spurious in the text output and explanation (I was trying to be generic) which led to some confusion.

I can’t make use of …

Total of 1 license in use

… as its not accurate (it includes reserved licenses). So “Used” needs to be the number of lines below “Users of Feature…” until the next “Users of Feature…”. Hence Feature{A,D} have 0 used.

Plus the actual text output of the users who have checked out licenses look more like this:

Users of FeatureB:  (Total of 3 license issued;  Total of 2 license in use)
    john.buck COMPUTER012179 '9E081=DUF`D X@Qz^8v (v10.1) (SERVERNAME.domain.com/27000 5138), start Thu 10/27 12:31
    jane.doe COMPUTER016046 '9E085@DRCaK$eFC6qX'vOI (v10.1) (SERVERNAME.domain.com/27000 2247), start Fri 10/28 6:06

Hence the regex $checkedpattern wouldn’t work in this situation. If I understand how $Matches.{1,2,3} elicits the correct values from the regex, I may be be able to have a go myself at the regex required for $checkedpattern.

Thanks.

krzydoug · November 2, 2022, 5:14pm

When you post sample text it should be close to, if not exactly (sanitized of course) the same. No one can read your mind or see your screen. Advice/suggestions are given based on the information you provide.

$txt = @'
  Users of FeatureA:  (Total of 1 license issued;  Total of 1 license in use)
Users of FeatureB:  (Total of 3 license issued;  Total of 2 license in use)
    john.buck COMPUTER012179 '9E081=DUF`D X@Qz^8v (v10.1) (SERVERNAME.domain.com/27000 5138), start Thu 10/27 12:31
    jane.doe COMPUTER016046 '9E085@DRCaK$eFC6qX'vOI (v10.1) (SERVERNAME.domain.com/27000 2247), start Fri 10/28 6:06
Users of FeatureC:  (Total of 7 licenses issued;  Total of 3 licenses in use)
    User.A ComputerA cryptictext (version) (ServerName/port processid), start Tue 10/27 7:20
    User.B ComputerB cryptictext (version) (ServerName/port processid), start Tue 10/30 8:48
	User.C ComputerC cryptictext (version) (ServerName/port processid), start Tue 11/1 9:20
Users of FeatureD:  (Total of 1 license issued;  Total of 1 license in use)
Users of FeatureE:  (Total of 1 license issued;  Total of 1 license in use)
    User.C ComputerC cryptictext (version) (ServerName/port processid), start Tue 11/1 6:32
'@
 
$userspattern = 'Users.+?(\S+):.+(\d+).+issued.+'
$checkedpattern = '\s+(\S+)\s+(\S+)\s+.+start\s+\w+\s+(\w.+)'

$output = $txt -split '(?=Users)' | ForEach-Object {
    foreach($line in $_ -split '\r?\n'){
        if($line -match $userspattern){
            $current = [ordered]@{
                License = $Matches.1
                Total   = $Matches.2
                Used    = 0
                Checked = @()
            }
        }
        elseif($line -match $checkedpattern){
            $current.Used++

            $current.Checked += [PSCustomObject]@{
                Name       = $Matches.1
                Computer   = $Matches.2
                CheckedOut = $Matches.3
            }
        }
    }

    if($current){
        [PSCustomObject]$current
    }

    Remove-Variable current -ErrorAction SilentlyContinue
}

$output | ConvertTo-Json

tlauwk · November 3, 2022, 2:02am

Lesson learnt. Thanks for the assist.

I had to tweak the regex for $checkedpattern to …

^\s+(\S+)\s(\S+).+start\s\S+\s(\w.+)

… so it’ll pick up double digit values. Went with the tabular format for better readability compared to json. And by some trial and error, I omitted the final else statement …

else{
            [PSCustomObject]$current
        }

… which output only licenses that had been checked out:

License		Total	Used	Name		Computer  		CheckedOut
-------		-----	----	----        --------  		----------
FeatureB  	3		1		john.buck	Computer016041	11/2 8:20
FeatureC	7		1		jane.doe	Computer024018	11/2 16:13
FeatureC	7		2		john.buck	Computer016041	11/3 5:37

Thats probably a better output for the end users that need it.

@krzydoug Again, much appreciated. And managed to figure out the brackets in the regex are capturing groups which corresponds to $Matches.{1,2,3}.

krzydoug · November 3, 2022, 4:33am

Awesome. Once you unlock regex that puts you on another level. It’s basically the same for every language

Topic		Replies	Views
How to add values to separate lines in an array? PowerShell Help	3	223	May 16, 2024
How to add a column to an existing array? PowerShell Help	2	612	May 16, 2024
Dealing with arrays... PowerShell Help	13	273	May 16, 2024
How to add values to an array ... PowerShell Help	6	240	May 16, 2024
Foreach array/loop help PowerShell Help	7	173	May 16, 2024

Add Columns to Array

Related Topics