# Picking out usernames from a text-file

Welcome Forums General PowerShell Q&A Picking out usernames from a text-file

• Author
Posts
• #208665
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

Hi,

I’m working on a script to pick out usernames from a text file with som wonky formatting. It’s not a CSV or XML file. It has a number of (for me) useless lines above and below the content I’m interested in.

The username is two letters followed by four digits preceded by DOMAIN\. Before the username on the same row there is also a computername in the same format.

I’ve got a clunky way of picking out the usernames, which consists of a number of foreach-objects massaging the output of a Select-String and it just feels off and should be much simpler. Besides I’d also need to add the names to an array to do the actual work on the names at a later stage.

This is the way it looks now:

$replaceData = Get-Content -Path File.txt$replaceData | Select-String -Pattern 'DOMAIN\\\w{2}\d{4}' |
ForEach-Object -Process { $_.Matches } | ForEach-Object -Process {$_.Value } |
ForEach-Object -Process { $_.Split('\')[1] } • This topic was modified 2 months, 4 weeks ago by KLaage. Reason: Clean up code • #208698 Participant Topics: 4 Replies: 2249 Points: 5,494 Rank: Community MVP Could you post a few (sanitized) lines of example data you are dealing with? Maybe including the weird header you mentioned. There might be an easier way but it will be hard to work on guesses. Please format your example data as code as well. Thanks. A great inspiration for working with loosely structured text you could watch the video from Tobias: link • #208704 Participant Topics: 9 Replies: 29 Points: 191 Rank: Participant Here’s an example… The editor doesn’t like the format so it marks it as separate lines of code rather than a block even if I edit it in text-mode. Job '[JOB NAME]' : Step 1, '[DATA HEADER]' : Began Executing 2020-03-01 00:00:00 4-År Datornamn Modell Serienummer Sist på loggad ---------- ---------- ------------------------------ -------------- ------------------------------ 2019-03-27 CC1234 [COMP MODEL--------] [SERIAL #] DOMAIN\dd1234 2019-03-15 CC2345 [COMP MODEL--------] [SERIAL #] (null) 2019-03-15 CC3465 [COMP MODEL--------] [SERIAL #] OTHERDOMAIN\SYSTEMACCOUNT 2019-03-27 CC4567 [COMP MODEL--------] [SERIAL #] DOMAIN\dd2345 (38 rows(s) affected) I only need to grab the user names preceded by ‘DOMAIN\’, I can ignore the ‘(null)’ and ‘OTHERDOMAIN\’ results. I probably need to grab the computernames at a later date, but they seem to be upper case by default so I think a regex like ‘[A-Z]{2}\d{4}’ should work. • #208719 Participant Topics: 4 Replies: 2249 Points: 5,494 Rank: Community MVP Hmmm … you should use the code tags “PRE“. 😉 Try this: Select-String -Path .\file.txt -Pattern '(?<=\sDOMAIN\\).+(?=\s|$)' |
Select-Object -ExpandProperty Matches |
Select-Object -ExpandProperty Value
• #208743
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

Thank you, for that and the video, will have a look at it when I have the time.

Hadn’t thought about using Select-Object, instinctively it feels like it might be more efficient than ForEach-Object, though these files are not huge.

It still seems that I would need to iterate over the data a couple of times, though your regex allows me to skip the .Split()-function at the end. Will have to tweak it a bit as it grabs any string preceded by ‘DOMAIN\’,  in the example I didn’t include an example of ‘DOMAIN\WRONGNAMEFORMAT’, which apparently can occur as well – ‘(?<=\sDOMAIN\\)\w{2}\d{4}(?=\s|$)’ should do it I think. • #208746 Participant Topics: 4 Replies: 2249 Points: 5,494 Rank: Community MVP Will have to tweak it a bit as it grabs any string preceded by ‘DOMAIN\’, in the example I didn’t include an example of ‘DOMAIN\WRONGNAMEFORMAT’, which apparently can occur as well – ‘(?<=\sDOMAIN\\)\w{2}\d{4}(?=\s|$)’ should do it I think.

That’s why I asked for some sample data. And of course this sample data should contain some positives and some false positives. I cannot see your screen. 😉

• #208884
Participant
Topics: 5
Replies: 322
Points: 446
Rank: Contributor

Using a switch statement would be better if you are not sure how large the files will be.

$file = Get-ChildItem \\path\to\testfile.txt$reg = '(?<cname>\w{2}\d{4}).*] DOMAIN\\(?<uname>\w{2}\d{4})'

switch -Regex -File $file { {$_ -match $reg} {$_ -match $reg | Out-Null ; [PSCustomObject]@{ComputerName =$Matches['cname']
UserName=$Matches['uname']}} }  • #209418 Participant Topics: 9 Replies: 29 Points: 191 Rank: Participant Thank you. This helped me grab both in one swoop. And led me to rethinking my chain/pipeline for manipulating the data going forward, as I need to do a Get-ADUser lookup in the AD to grab GivenName, SurName, UserPrincipalName (email) and Manager info (which in its own turn needs a lookup for email) from the user name. Is it possible to rework this to create an array containing separate hashtables with a user and a computer in each: $UserCompArray = @(
@{
ComputerName = 'CN1234'
},
@{
ComputerName = 'CN2345'
}
)

Like mentioned here:  An Array of Hash Tables. He builds them manually in that article but it should be possible to do it by iterating.

My thought is that in the next step, I’d expand the hash tables to contain

$UserCompArray = @( @{ UserName = 'un1234' ComputerName = 'CN1234' GivenName = 'User' SurName = 'Name' UserPrincipalName = 'user.name@email.address' Manager = 'Manager String with username' }, ... ) And finally $UserCompArray = @(
@{
ComputerName = 'CN1234'
GivenName = 'User'
SurName = 'Name'
Manager	= 'Manager String with username'
},
...
)

Does that make any kind of sense?

• #209427
Participant
Topics: 4
Replies: 2249
Points: 5,494
Rank: Community MVP

Why do you think you need a hashtable for this? What would you like to do with these data when you have it?

• #209433
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

Basically I need to send mail to a number of people (UserName) about their computers (ComputerName) each month, preferably with a CC to their manager. So I basically need to use those two bits of info to grab info from our AD (Twice since I need to regex the user name and Get-ADUser the string from the Manager field in our AD), and then preferably have all the needed info for each user/computer pair in a neat package in the end to push into a Send-MailMessage function.

My thought was that I’d prefer to split it up into separate functions with a pipeline running through it rather than a massive single script. It seems to me that an array of hash tables would allow me to iterate through the collection as needed in the functions rather than having to manage a lot of decoupled variable names.

Let me see if I can just abstract the chain as I imagine it:
.txt-file > Regex out UserName, ComputerName > Get-ADUser UserName to get GivenName,SurName,UserPrincipalName,Manager > Regex out ManagerUserName > Get-ADUser Manager to get ManagerUserPrincipalName > Send-MailMessage -To UserPrincipalName -Cc ManagerUserPrincipalName -Subject “String with $ComputerName” -Body “HTML-content with$GivenName, $SurName and$ComputerName”

This is of course extremely simplified, but I hope you get the gist of what I’m wanting to do!?

And if there’s a simpler way of getting to the same end point I’d be happy to hear it – I know I sometimes get so focused on one particular way of doing things that I overlook other paths.

• This reply was modified 2 months, 3 weeks ago by KLaage. Reason: Pre-section not optimally readable
• #209439
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

I had a long reply typed out and posted, but I went in to edit out some pre-tags that made part of it hard to read – and it disappeared!?
For once I wrote directly in the editor, rather than Notepad++ so I don’t have a backup.
Will rewrite in a while…

• #209463
Participant
Topics: 4
Replies: 2249
Points: 5,494
Rank: Community MVP

OK, to make it a little bit shorter … I think you don’t need an array of hashtables. A simple array would do it perfectly … like this

'ManagerUPName', 'UserPrincipalName', 'SurName', 'UserName', 'ComputerName', 'Manager', 'GivenName'
'manager.name@email.address', 'user.name@email.address', 'Name', 'un1234', 'CN1234', 'Manager String with username', 'User'
• #209466
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

Managed to retrieve the reply I thought I’d lost:

Basically I need to send mail to a number of people (UserName) about their computers (ComputerName) each month, preferably with a CC to their manager. So I basically need to use those two bits of info to grab info from our AD (Twice since I need to regex the user name and Get-ADUser the string from the Manager field in our AD), and then preferably have all the needed info for each user/computer pair in a neat package in the end to push into a Send-MailMessage function.

My thought was that I’d prefer to split it up into separate functions with a pipeline running through it rather than a massive single script. It seems to me that an array of hash tables would allow me to iterate through the collection as needed in the functions rather than having to manage a lot of decoupled variable names.

Let me see if I can just abstract the chain as I imagine it:
.txt-file > Regex out UserName, ComputerName > Get-ADUser UserName to get GivenName,SurName,UserPrincipalName,Manager > Regex out ManagerUserName > Get-ADUser Manager to get ManagerUserPrincipalName > Send-MailMessage -To UserPrincipalName -Cc ManagerUserPrincipalName -Subject “String with $ComputerName” -Body “HTML-content with$GivenName, $SurName and$ComputerName”

This is of course extremely simplified, but I hope you get the gist of what I’m wanting to do!?

And if there’s a simpler way of getting to the same end point I’d be happy to hear it – I know I sometimes get so focused on one particular way of doing things that I overlook other paths.

• #209481
Participant
Topics: 4
Replies: 2249
Points: 5,494
Rank: Community MVP

I think you’re overcomplicating this task. The TXT file you mentioned … where do you get this? Would it be possible to get this information as CSV file? But even if not … you know already how you extract the desired information from this file, right?
If you have the sAMAccountName and the ComputerName listed in an array named $UserComputerList you extracted out of the TXT file with the headers “User” and “ComputerName” you could do something like this: $UserComputerList |
ForEach-Object {
$User = Get-ADuser -Identity$_.User -Properties Manager, Mail
$Computer = Get-ADComputer -Identity$_.ComputerName
$Manager = Get-ADuser -Identity$User.Manager -Properties Mail
[PSCustomObject]@{
User         = $_.User GivenName =$User.GivenName
Surname      = $User.Surname Email =$User.Mail
Manager      = $Manager.Name ManagerEmail =$Manager.Mail
Computer     = $Computer.sAMAccountName } } Now you have all needed information to iterate over this list to send the desired/needed mails. • #209610 Participant Topics: 9 Replies: 29 Points: 191 Rank: Participant Unfortunately, I have no control over the format of the file… I would have preferred getting a simple .CSV file if I could choose. Looking at the ForEach-Object in your example it’s obvious that I’ve been overthinking that part massively. However, it feels like I’m missing something when it comes to output from the file. You mention an array with headers, but unless I’m missing something an array with computer and user names would just look like one of these two examples: $Array = @('CN1234','un1234','CN2345','un2345'...)
$Array = @('CN1234','CN2345','un1234','un2345'...) Simple lists of values with no headers. Otherwise it feels like we’re talking about this: $Array = $(${Computer='CN1234';User='un1234'},${Computer='CN2345';User='un2345'}...) Where the iteration goes over each of the elements of the array. Using a tweaked version of the suggestion from “random commandline”, I get the following: ComputerName UserName ------------ -------- CN1234 un1234 CN2345 un2345 CN3456 un3456 ... Which looks like what you’re suggesting, but using .getType() on it identifies it as a hash table. Also, If I try to assign the [PSCustomObject] to a variable like so $ReturnObject = [pscustomobject]@{...} it only contains the last added Key/Value pair.
At least that is all that’s displayed when calling the variable directly. So how do I use the full results from a [PSCustomObject] later.

Sorry if I’m rambling, but I’m just trying to learn and get this right. I do appreciate the assistance.

• #209673
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

I’m sorry for spamming this thread, but tweaking and just trying to get my head around this has got me to this:

Switch -Regex -File $filePath { '(?[A-Z]{2}\d{4}).* DOMAIN\\(?\w{2}\d{4})' {$ComputerName = $Matches['cname']$UserName = $Matches['uname']$ReturnHash.Add($ComputerName,$UserName)
$ComputerName =$UserName = $null } } #$ReturnHash
$ReturnHash.GetType()$ReturnHash.GetEnumerator() | ForEach-Object {
$User = Get-ADUser -Server$server -Identity $_.Value -Properties Manager$Manager = Get-ADUser -Server $server -Identity$User.Manager
$Computer =$_.Key
[PSCustomObject]@{
User = $_.Value GivenName =$User.GivenName
SurName = $User.SurName To = ($User.UserPrincipalName).ToLower()
Manager = $Manager.Name Cc = ($Manager.UserPrincipalName).ToLower()
Computer = $Computer } }  This gives me output like this (repeated 30 times this month): User : un1234 GivenName : John SurName : Doe To : john.doe@email.address Manager : Jane Doe - [DEPARTMENT] - un2345 Cc : jane.doe@email.address Computer : CN1234 ... It may be overcomplicating it a bit still, but I get all the info I need. I thought I could use [PSCustomObject] as a splat into a function that accepts pipeline input, but when I use any of the members in the function it expands everything to this @{User=un1234; GivenName=John; SurName=Doe; To=john.doe@email.address; Manager=Jane Doe - [DEPARTMENT] - un2345; Cc=jane.doe@email.address; Computer=CN1234}. So I’m not there yet, but I feel I’m getting much closer. • #209760 Participant Topics: 4 Replies: 2249 Points: 5,494 Rank: Community MVP hmmm … you still don’t want to give up the idea of hashtables … do you? 😉 The suggestion from random command line seems to work perfectly .. so we use it to extract the information you’re after from the input file and store it in an array variable … like this: $file = Get-ChildItem D:\sample\file.txt
$reg = '(?<cname>\w{2}\d{4}).*] DOMAIN\\(?<uname>\w{2}\d{4})' # Output ComputerName and UserName$UserComputerList = switch -Regex -File $file { {$_ -match $reg } {$_ -match $reg | Out-Null [PSCustomObject]@{ ComputerName =$Matches['cname']
User         = $Matches['uname'] } } } The result is an array saved in a variable with the name … $UserComputerList

applied to the sample data you provided above we get this output:

ComputerName User
------------ ----
CC1234       dd1234
CC4567       dd2345

Now you can use this array to get the information from your AD … like this:

$UserComputerList | ForEach-Object {$User = Get-ADUser -Server $server -Identity$_.User -Properties Manager
$Manager = Get-ADUser -Server$server -Identity $User.Manager [PSCustomObject]@{ User =$_.User
GivenName = $User.GivenName SurName =$User.SurName
To        = ($User.UserPrincipalName).ToLower() Manager =$Manager.Name
Cc        = ($Manager.UserPrincipalName).ToLower() Computer =$_.ComputerName
}
}

You can pipe this to any needed further step or you simply save it in a variable or export it to a CSV file or whatever you want.

If you have a function accepting pipeline input of course you can pipe it to that function. 😉

• #209835
Participant
Topics: 9
Replies: 29
Points: 191
Rank: Participant

It really isn’t that I’m in love with hash tables… I swear 😉

It’s more that I’m working my way through the things I’m somewhat comfortable with to get to where I’m going. It may take longer, but I think I understand it better if I implement it bit by bit.

I feel like I’m almost at the finish line, but there’s still one thing that’s eluding me.

$reg = '(?[A-Z]{2}\d{4}).* DOMAIN\\(?[a-z]{2}\d{4})'$ReturnArray = Switch -Regex -File $filePath { {$_ -cmatch $reg } {$_ -cmatch $reg | Out-Null [PSCustomObject]@{ ComputerName =$Matches['cname']
UserName = $Matches['uname'] } } }$ReturnArray | ForEach-Object {
$User = Get-ADUser -Server$server -Identity $_.UserName -Properties Manager$Manager = Get-ADUser -Server $server -Identity$User.Manager -ErrorAction Stop
[PSCustomObject]@{
User = $_.UserName GivenName =$User.GivenName
SurName = $User.Surname To = ($User.UserPrincipalName).ToLower()
Cc = ($Manager.UserPrincipalName).ToLower() Computer =$_.ComputerName
} | Get-MailInfo
}

function Get-MailInfo {
[CmdletBinding()]
param(
[Parameter(ValueFromPipeline = $true)] [Object]$obj
)
$obj.GivenName$obj.SurName
$obj.To$obj.Cc
'-----'
$hereText = @" Hello$obj.GivenName $obj.SurName, Your email is$obj.To and your boss is $obj.Cc "@$text = "Hello $obj.GivenName$obj.SurName. Your email is: $obj.To and your boss is:$obj.Cc"
$text } This gives me output in the form of: User Name user.name@email.address manager.name@email.address But the variables in the double quoted string or the here string is not expanded correctly, they are expanded to the full objects instead. Like this: Hello @{User=un1234; GivenName=User; SurName=Name; To=user.name@email.address; Cc=manager.name@email.address ; Computer=CN1234}.GivenName ... This makes it difficult to compose the final mail messages that go out. • #209856 Participant Topics: 4 Replies: 2249 Points: 5,494 Rank: Community MVP But the variables in the double quoted string or the here string is not expanded correctly, they are expanded to the full objects instead. Usually it helps to “call” the variables like this: $($obj.GivenName) • #210081 Participant Topics: 9 Replies: 29 Points: 191 Rank: Participant Thanks… Has been so wrapped up in this thread, that I wrote before thinking about it myself. I went about it by doing a simple reassignment at the top $GivenName = $obj.GivenName but $(\$obj.GivenName) is more succinct.