powershell wildcard array in the where clause

I'm struggling with a PowerShell Where clause with an array that contains wildcards. Here's the command in my much larger code,

$excludedOUs = @("*OU=Loaners,*", "*OU=InStock,*", "*OU=Conference Room,*","*OU=Training,*") 
Get-ADComputer -SearchBase $targetOU -Property LastLogonDate,LastLogon | 
          Where-Object { $_.DistinguishedName -notin $excludedOUs } | Select-Object Name,DistinguishedName, @{l='LastLogonDate';e={[DateTime]::FromFileTime($_.LastLogon)}}

I want to return a list of machines from the $targetOU but I want to exclude a list of sub-OUs from the $targetOU. I've seen multiple posts that explain why this is failing (wildcards not allowed in "notin", need to use -like and -notlike with wilcards, etc.), and I've also seen the suggestions of using Regex to solve the problem. Unfortunately, my eyes always glaze over at the first sight of regular expressions and I can never figure out how to use them correctly. Ultimately, I'd like to not have to use the wildcards in the array at all. If the $excludedOUs array was,

$excludedOUs = @("Loaners", "InStock", "Conference Room", "Training")

I would prefer it. But, I need to use the wildcards because the sub-OU is only a part of the DistinguishedName property. Additionally, the array may not always be limited to just these four items. It might be a single item, or none, or dozens. I won't know how others in the organization will use this. Any suggestions are appreciated.

Solution

As you note, -in and -contains and their negated variants only perform whole-value, literal equality comparisons.

Unfortunately, neither -like, the wildcard matching operator, nor -match, the regular-expression matching operator, support an array of patterns to match against, as of PowerShell (Core) 7.4:

Having this ability (with any one of the patterns matching being considered an overall match) would be useful; GitHub issue #2132 asks for it to be implemented in a future PowerShell (Core) version.

The workaround is to construct a single regex that uses alternation (|) that you can use with -match / -notmatch, which is easy to do programmatically:

Construct your array with literal substrings, i.e. without * characters, given that -match / -notmatch performs substring matching by default.
- Note: Conversely, this means that extra effort is needed to perform whole-value or prefix / suffix matching with a regex, namely by stipulating matching at the start (^) and/or end ($) of the input strings.^[1]
Join the array elements with | via -join to form a single string that represents a regex with alternation, which means that any one of the substrings matches.
- Note: If the literal substrings happen to contain regex metacharacters such as . or +, you must additionally escape them, using the [regex]::Escape() .NET method - see the relevant comment in the source code below.
  The alternative is to perform character-individual escaping with \, which is only an option in array literals.
Use that string with -notmatch

# Define the exclusions as literal substrings, without "*"
$excludedOUs = 
  @("OU=Loaners,", "OU=InStock,", "OU=Conference Room,","OU=Training,")

# Join them with "|", to construct a single regex that matches
# any one of the substrings.
# In cases where the array elements contain regex metacharacters (see above), 
# use the following instead:
#   $regexExcludedOUs = $excludedOUs.ForEach({ [regex]::Escape($_) }) -join '|'
$regexExcludedOUs = $excludedOUs -join '|'

# Use the regex with -notmatch
Get-ADComputer -SearchBase $targetOU -Property LastLogonDate,LastLogon | 
  Where-Object { $_.DistinguishedName -notmatch $regexExcludedOUs } |
  Select-Object Name,DistinguishedName, @{l='LastLogonDate';e={[DateTime]::FromFileTime($_.LastLogon)}}

^{[1] Two examples that in essence use the same technique as in the rest of the answer:

Whole-string matching: $regexes = 'fo+', 'ba[r]'; $combinedRegex = '^({0})$' -f ($regexes -join '|')

$combinedRegex then contains ^(fo+|ba[r])$ verbatim.

Prefix matching: $literalPrefixes = 'fo.', 'ba+'; $combinedRegex = '^({0})' -f ($literalPrefixes.ForEach({ [regex]::Escape($_) }) -join '|')

$combinedRegex then contains ^(fo\.|ba\+) verbatim.}