CODEWITHSUNDEEP
powershellscriptingactive-directorypowershell-4.0windows-scripting
Senior Systems Engineer
Senior Systems Engineer

Reputation: 1139

Powershell Get-ADUser filter to exclude specific OU in the list

I am trying to change the below Powershell script so it does not search any account in the specific OU from the list.

The AD account that will be reported is the AD account without any of the below attributes:

Phone Number
Mobile/Cellphone Number
Job Title
Email Address.
Company
City
office address

This is the script that I have so far:

    $filter = "(Enabled -eq 'true') -and (mail -notlike '*') -and (company -notlike '*') -and "
$filter += "(l -notlike '*') -and (physicalDeliveryOfficeName -notlike '*') -and "
$filter += "(title -notlike '*') -and (telephoneNumber -notlike '*') -and (mobile -notlike '*')"

$properties = @('mail', 'physicalDeliveryOfficeName', 'Company', 'DisplayName', 'title', 'SamAccountName', 'CanonicalName', 'lastlogondate', 'mobile', 'telephoneNumber', 'l', 'Whencreated', 'DistinguishedName')
$domainDN = (Get-ADDomain).DistinguishedName

$excludeOUs = @(
'OU=Disabled Users,DC=GlobalCorp,DC=com'
    'OU=GlobalCorp Testing,DC=GlobalCorp,DC=com'
    'OU=Admin Accounts,OU=GlobalCorp Global,DC=GlobalCorp,DC=com'
    'OU=Service Accounts,OU=GlobalCorp Global,DC=GlobalCorp,DC=com'
    'OU=Shared Mailboxes,OU=GlobalCorp Global,DC=GlobalCorp,DC=com'
)

$reExcludeOUs = '(?:{0})$' -f ($excludeOUs -join '|')

Get-ADUser -Filter $filter -Properties $properties -SearchBase $domainDN |
    Where-Object {
        ($_.DistinguishedName -notmatch $reExcludeOUs) -and
        ($_.SamAccountName -notmatch '^(SM_|Temp|HealthMailbox)|SVC|Test|admin|\$') -and
        ($_.DisplayName -notmatch 'Admin|Calendar|Room')
    } |
    Select-Object -Property `
        DisplayName,
        Company,
        Title,
        TelephoneNumber,
        Mobile,
        PhysicalDeliveryOfficeName,
        SamAccountName,
        Mail,
        @{n = 'OU'; e = { $_.CanonicalName.Remove($_.CanonicalName.LastIndexOf($_.Name) - 1) } },
        @{n = 'CN'; e = { Split-Path $_.CanonicalName -Parent } },
        @{n = 'ParentContainer'; e = { $_.DistinguishedName -replace '^CN=.*?(?=CN|OU)' } },
        LastLogondate,
        WhenCreated | 
        Sort-Object OU | 
        ConvertTo-HTML | 
        Set-Variable HTMLBody

Send-MailMessage -SmtpServer SMTP.GlobalCo.com -From "$env:COMPUTERNAME@$env:userdnsdomain" -To [email protected] -Subject "AD User Incomplete report as at $((Get-Date).ToString('dd-MM-yyyy'))" -Body ($HTMLBody -join '`n') -BodyAsHTML

How can I modify the above Where-Object filter?

Upvotes: 0

Views: 7138

Answers (2)

Theo
Theo

Reputation: 61068

First thing is that you say you want to report only users that are without any of the below attributes. In that case, your filter is wrong and should be more like:

$filter =  "(Enabled -eq 'true') -and (mail -notlike '*') -and (company -notlike '*') -and "
$filter += "(l -notlike '*') -and (physicalDeliveryOfficeName -notlike '*') -and "
$filter += "(title -notlike '*') -and (telephoneNumber -notlike '*') -and (mobile -notlike '*')"

I've spread this out on several lines to make it more readable

Then there is the way you construct your $excludeOUs that adds a comma after each OU DN.
I would suggest to simply declare that as string array like this:

$excludeOUs = 'OU=Disabled Users,DC=GlobalCorp,DC=com', 
    'OU=GlobalCorp Testing,DC=GlobalCorp,DC=com',
    'OU=Admin Accounts,OU=GlobalCorp Global,DC=GlobalCorp,DC=com',
    'OU=Service Accounts,OU=GlobalCorp Global,DC=GlobalCorp,DC=com',
    'OU=Shared Mailboxes,OU=GlobalCorp Global,DC=GlobalCorp,DC=com'

and for testing purposes later on, combine them to create a Regular Expression:

# create a regular expression string combining the OUs with the 'OR' pipe symbol and wrap that
# inside a non-capturing group. The $ means the match should end in any of the 'OR'-ed ou DNs
$reExcludeOUs = '(?:{0})$' -f ($excludeOUs -join '|')

Armed with that, you can do

$properties = 'EmailAddress', 'Office', 'Company', 'DisplayName', 'Title', 'SamAccountName', 'DistinguishedName',
              'CanonicalName', 'LastLogonDate', 'MobilePhone', 'OfficePhone','City','Created'
$domainDN = (Get-ADDomain).DistinguishedName

$report = Get-ADUser -Filter $filter -Properties $properties -SearchBase $domainDN |
            Where-Object {
                ($_.DisplayName -notmatch 'Admin|Calendar|Room') -and
                ($_.SamAccountName -notmatch '^(SM_|HealthMailbox)|SVC|Test|admin|\$') -and
                ($_.DistinguishedName -notmatch $reExcludeOUs)
            } |
            Select-Object -Property DisplayName, Company, Title, OfficePhone, MobilePhone,
                                    Office, SamAccountName, EmailAddress, City,
                                    @{n = "OU"; e = { $_.CanonicalName.Remove($_.CanonicalName.LastIndexOf($_.Name) - 1) } },
                                    @{n = 'CN'; e = { Split-Path $_.CanonicalName -Parent } },
                                    @{n = 'ParentContainer'; e = { $_.DistinguishedName -replace '^CN=.*?(?=CN|OU)' } },
                                    LastLogonDate, Created

After this, the $report variable should contain all info you need to save as CSV, ConvertToHTML etc.

Inside the Where-Object{} you can use regular expression matches (-notmatch in this case), so one regex can save you a lot of -notlike clauses here.

P.S. for cmdlets like the Send-MailMessage with a lot of parameters I'd advise to use Splatting to keep the code readable.

Hope that helps

Upvotes: 1

Mike
Mike

Reputation: 131

You can use both -notcontains or -notmatch, but the problem here is that you have to select the DistinguishedName too otherwise it will not apply the condition because it's 'invisible'

Get-ADUser -Filter $filter -Properties $properties -SearchBase $domainDN |
    Select-Object -Property `
        DisplayName,
        Company,
        Title,
        telephoneNumber,
        mobile,
        PhysicalDeliveryOfficeName,
        DistinguishedName,
        ...
        ...

Upvotes: 1

Related Questions