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

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

CodePudding user response：

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.}

CodePudding user response：

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