Picking out usernames from a text-file

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

Viewing 19 reply threads
  • 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
      Helping Hand
      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
      Helping Hand
      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
      Helping Hand
      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
      Helping Hand
      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})'
      
      # Output ComputerName and UserName
      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 = @(
      	@{
      		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?

    • #209427
      Participant
      Topics: 4
      Replies: 2249
      Points: 5,494
      Helping Hand
      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
      Helping Hand
      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
      Helping Hand
      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
      Helping Hand
      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
      Helping Hand
      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.

Viewing 19 reply threads
  • You must be logged in to reply to this topic.