Picking out usernames from a text-file

PowerShell Help
1

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] }
2

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

3

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.

4

Hmmm … you should use the code tags “PRE”. :wink:

Try this:

Select-String -Path .\file.txt -Pattern '(?<=\sDOMAIN\\).+(?=\s|$)' | 
Select-Object -ExpandProperty Matches | 
Select-Object -ExpandProperty Value
5

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.

6

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. :wink:

7

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})'

# Output ComputerName and UserName
switch -Regex -File $file
{
{$_ -match $reg} {$_ -match $reg | Out-Null ; 
    [PSCustomObject]@{ComputerName = $Matches['cname']
    UserName=$Matches['uname']}}
}
8

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 = @(
	@{
		UserName = 'un1234'
		ComputerName = 'CN1234'
	},
	@{
		UserName = 'un2345'
		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 = @(
	@{
		UserName = 'un1234'
		ComputerName = 'CN1234'
		GivenName = 'User'
		SurName = 'Name'
		UserPrincipalName = 'user.name@email.address'
		Manager	= 'Manager String with username'
		ManagerUPName = 'manager.name@email.address
	},
	...
)

Does that make any kind of sense?

9

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

10

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.

11

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…

12

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'
13

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.

14

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.

15

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.

16

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.

17

hmmm … you still don’t want to give up the idea of hashtables … do you? :wink:

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. :wink:

18

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

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.

19

Usually it helps to “call” the variables like this:

$($obj.GivenName)

20

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.