How to select a substring in select/group command- Powershell

Question

In the code below, the userprinciplename will output strings like "[email protected]" and [email protected]" but i am hoping to extract a substring from that, that being the codes before the "_" character, so LLL and XXXX.

There may be some which do not have an underscore character however and so these would need to be ignored / have the original string it would have returned.

##Check bottom of script for setting specific OU
Function Get-LastLogon {
  param(
    [string]$OUName
  )

  # Get all matching OUs on any level
  $OUs = Get-ADOrganizationalUnit -Filter "Name -like '$OUName'"
  $DCs = Get-ADDomainController -Filter *

  # Get all users from each OU from each DC
  $ADUsers = Foreach ($OU in $OUs) {
    Foreach ($DC in $DCs.HostName) {
      Get-ADUser -SearchBase $OU.DistinguishedName -Filter * -Properties LastLogon -server $dc | 
        Select-Object Name,userPrincipalName, @{n='LastLogon';e={[DateTime]::FromFileTime($_.LastLogon)}}
    }
  }

  # return most recent LastLogon date for each user
  $ADUsers | 
    Group Name,userPrincipalName | 
    Select Name,userprinciplename, @{n='LastLogon';e={$_.Group.LastLogon | sort -desc | select -First 1}}
}  ## End function


##Enter the OU here
Get-LastLogon -OUName 'Clients' 

##Un-comment below to export to csv
## | Export-Csv -Path 'C:\temp\UserExport.csv'

Here is what the script looks like now in full:

##Check bottom of script for setting specific OU
Function Get-LastLogon {
  param(
    [string]$OUName
  )

  # Get all matching OUs on any level
  $OUs = Get-ADOrganizationalUnit -Filter "Name -like '$OUName'"
  $DCs = Get-ADDomainController -Filter *

$ADUsers = foreach ($OU in $OUs) {
    foreach ($dc in $DCs.HostName) {
        Get-ADUser -SearchBase $OU.DistinguishedName -Filter * -Properties lastLogonTimeStamp -Server $dc | 
            Select-Object Name,UserPrincipalName, 
                          @{Name = 'LastLogon';Expression = {[DateTime]::FromFileTime($_.lastLogonTimeStamp)}},
                          @{Name = 'UserCode'; Expression = {([regex]'^([^_]+)_.*').Match($_.UserPrincipalName).Groups[1].Value}}
        }
    }  }

# return the most recent LastLogon date for each user 
# (only the users with a code prefix in the UserPrincipalName)
$ADUsers | Where-Object { ![string]::IsNullOrWhiteSpace($_.UserCode) } |
    Group-Object UserPrincipalName | ForEach-Object {
        [PsCustomObject]@{
            Name      = $_.Group[0].Name
            UserCode  = $_.Group[0].UserCode
            LastLogon = $_.Group.LastLogon | Sort-Object -Descending | Select-Object -First 1
        }
    }
  ## End function

$OUcustom = Read-Host -prompt 'Enter OU here or "Clients" for all'
##Enter the OU here
Get-LastLogon -OUName $OUcustom |
##export csv
Export-Csv -path "C:\temp\UserExport_$((Get-Date).ToString("ddMM_HHmm")).csv" -NoTypeInformation
".csv extracted to C:\temp"
pause

You need work on the userprincipalname variable before putting it in the select string. Kindly edit the question and share a sample userprincipalname and what is the expected output sample out of the sampleuserprincipalname. I believe then we can help you better. @Steve — Ranadip Dutta
– Ranadip Dutta, Commented Oct 26, 2022 at 10:04
Hi - I restructured the question to hopefully be a bit easier to answer. — Steve101
– Steve101, Commented Oct 26, 2022 at 10:14

Theo · Accepted Answer · 2022-10-26 14:21:18Z

1

Just add another calculated property to the code you have to get the array of user objects:

$ADUsers = foreach ($OU in $OUs) {
    foreach ($dc in $DCs.HostName) {
        Get-ADUser -SearchBase $OU.DistinguishedName -Filter * -Properties lastLogonTimeStamp -Server $dc | 
            Select-Object Name,UserPrincipalName, 
                          @{Name = 'LastLogon';Expression = {[DateTime]::FromFileTime($_.lastLogonTimeStamp)}},
                          @{Name = 'UserCode'; Expression = {if ($_.UserPrincipalName -like '*_*@*') {($_.UserPrincipalName -split '_')[0]} else { $null }}}
                          # or use regex like:
                          # @{Name = 'UserCode'; Expression = {([regex]'^([^_]+)_.*').Match($_.UserPrincipalName).Groups[1].Value}}
        }
    }

Then you can filter out the users that do have such a code:

# return the most recent LastLogon date for each user 
# (only the users with a code prefix in the UserPrincipalName)
$ADUsers | Where-Object { ![string]::IsNullOrWhiteSpace($_.UserCode) } |
    Group-Object UserPrincipalName | ForEach-Object {
        [PsCustomObject]@{
            Name      = $_.Group[0].Name
            UserCode  = $_.Group[0].UserCode
            LastLogon = $_.Group.LastLogon | Sort-Object -Descending | Select-Object -First 1
        }
    }

If you want to rule out all users that do not have some code followed by an underscore in their UserPrincipalName property straight away, you can use the filter parameter:

Get-ADUser -SearchBase $OU.DistinguishedName -Filter "UserPrincipalName -like '*_*@*'" -Properties lastLogonTimeStamp -Server $dc

This however will not give you the opportunity to use the collected users for some other purpose, like outputting users who do not have a code prefixed, as would be easy to do with the code above.

^{P.S. Did you know PowerShell also provides an attribute LastLogonDate, which is the LDAP property lastLogonTimeStamp, converted to local time.}

edited Oct 26, 2022 at 14:21

answered Oct 26, 2022 at 11:58

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Steve101 Over a year ago

Hi, thanks for that, i just gave it a go and i can see on my csv file i now have a column for "User Code" however it is all blank, and the principle name remains where really all i need is to truncate everything but the text before the underscore. Any idea why the codes didn't populate and also can we change it so the code just outputs, name, the "user code" then the last logon date? Also when i use LastLogonDate all the dates become 01/01/1601 00:00:00 even though these users have 100% logged in either recently or at least once in the past.

Steve101 Over a year ago

The script now does nothing and returns no results.

Theo Over a year ago

@Steve101 Can you check if the first code block $ADUsers = ... returns an array of users? BTW, Get-ADOrganizationalUnit -Filter "Name -like '$OUName'" should probably be Get-ADOrganizationalUnit -Filter "Name -like '*$OUName*'". Without the wildcard characters, -like acts as -eq

Steve101 Over a year ago

The first block also terminates without error however also does nothing.

Theo Over a year ago

@Steve101 Well... Nothing much has changed there, although I would prefer to swap the two foreach loops (but left that as in your original code). Is this all the code, or just part of a larger script? Are you sure you call the function with an existing OU name?

|

Steve101 · Accepted Answer · 2022-10-28 11:29:25Z

0

So this was actually more simple than I realised. I just needed to add in:

@{N='userPrincipalName';E={$_.userPrincipalName.Split("_")[0]}}

To the first and second blocks where UserPrincipleName was being selected. Will post the full working code below for relevance.

##Check bottom of script for setting specific OU
Function Get-LastLogon {
  param(
    [string]$OUName
  )

  # Get all matching OUs on any level
  $OUs = Get-ADOrganizationalUnit -Filter "Name -like '$OUName'"
  $DCs = Get-ADDomainController -Filter *

  # Get all users from each OU from each DC
  $ADUsers = Foreach ($OU in $OUs) {
    Foreach ($DC in $DCs.HostName) {
      Get-ADUser -SearchBase $OU.DistinguishedName -Filter * -Properties LastLogon -server $dc | 
        Select-Object Name,@{N='userPrincipalName';E={$_.userPrincipalName.Split("_")[0]}}, @{n='LastLogon';e={[DateTime]::FromFileTime($_.LastLogon)}}
    }
  }

  # return most recent LastLogon date for each user
  $ADUsers | 
    Group Name,userPrincipalName | 
    Select Name,@{N='userprinciplename';E={$_.userprinciplename.Split("_")[0]}}, @{n='LastLogon';e={$_.Group.LastLogon | sort -desc | select -First 1}}
}  ## End function

$OUcustom = Read-Host -prompt 'Enter OU here or "Clients" for all'
##Enter the OU here
Get-LastLogon -OUName $OUcustom |
##export csv
Export-Csv -path "C:\temp\UserExport_$((Get-Date).ToString("ddMM_HHmm")).csv" -NoTypeInformation
".csv extracted to C:\temp"

pause

answered Oct 28, 2022 at 11:29

Steve101

511 silver badge8 bronze badges

2 Comments

Theo Over a year ago

So, basically this is the code I gave in my original answer.. the code that according to you did not work...

Steve101 Over a year ago

No, you are correct it did not work when i tried yours. They aren't word for word so i don't know why when i pasted your code it didn't work and mine did but that's the way it went. No doubt in my mind you're still better at this stuff than me, don't worry..

Collectives™ on Stack Overflow

How to select a substring in select/group command- Powershell

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related