Exclude specific results exported in CSV via Powershell Script

Question

I am new to Powershell and I am trying to exclude specific "GivenName" and "SN" when exporting results to a CSV.

This is my current script-

Get-ADUser -SearchBase "OU=Us00,OU=NA,Dd=corp,Dd=ads" -Filter {Enabled -eq $True} -Properties * | Select-Object GivenName, SN, DisplayName, Company, LastLogonDate |Where {($_.LastLogonDate -lt (Get-Date).AddDays(-30)) -and ($_.LastLogonDate -ne $NULL)} | Export-Csv -Path G:\Conduct\InactiveUsers.csv -NoTypeInformation

My goal is to Exclude any GivenName that may include the word "Agile" and OR exclude any SN that may include the word "External"

I have tried a where "is not" statement, but I am failing to reach my end goal. Any guidance or help would be appreciated

Add them to your filter like Get-ADUser -Filter 'Enabled -eq $True -and (GivenName -notlike "*Agile*" -or SN -notlike "*External*")' — Cpt.Whale
– Cpt.Whale, Commented Nov 18, 2022 at 20:38
the -is and -isnot operators are for comparing types like "hello" -is [string]. Powershell uses -like and -notlike for text comparison. Check out examples of all the operators here: learn.microsoft.com/en-us/powershell/module/… — Cpt.Whale
– Cpt.Whale, Commented Nov 18, 2022 at 20:41

Santiago Squarzon · Accepted Answer · 2022-11-18 21:02:37Z

1

Everything you're looking to do can be done leveraging the Active Directory Filter:

$params = @{
    SearchBase = "OU=Us00,OU=NA,Dd=corp,Dd=ads"
    LDAPFilter =
        "(&" + # open the filter with AND
        "(!userAccountControl:1.2.840.113556.1.4.803:=2)" + # `Enabled`
        "(!givenName=*Agile*)" + # `GivenName` does not contain `Agile`
        "(!sn=*External*)" + # `sn` (Surname) does not contain `External`
        "(lastLogon<=$((Get-Date).AddDays(-30).ToFileTimeUtc()))" + # `lastLogon` is lower than or equal to 30 days ago
        "(lastLogon=*)" + # `lastLogon` attribute must be populated
        ")" # close the filter
    Properties = 'GivenName', 'SN', 'DisplayName', 'Company', 'LastLogonDate'
}
Get-ADUser @params | Select-Object $params['Properties'] |
    Export-Csv -Path G:\Conduct\InactiveUsers.csv -NoTypeInformation

NOTE - lastLogon attribute does not replicate across the Domain, because of this the query might not give you accurate results, you could however change the query to use lastLogonTimeStamp which indeed is replicated across Domain Controllers but it's also not accurate. See Understanding the AD Account attributes - LastLogon, LastLogonTimeStamp and LastLogonDate.

If you need the most accurate results you would need to perform this query (targeting the lastLogon attribute) against all your Domain Controllers to find the latest authentication for all the users in the Domain. These answers might give you a hint on how you could approach querying all your DCs in case this was needed:

edited Nov 18, 2022 at 21:02

answered Nov 18, 2022 at 20:46

Santiago Squarzon

65.6k5 gold badges26 silver badges60 bronze badges

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

2 Comments

KeepVBSalive Over a year ago

You certainly guided me to the right direction, writing it in this format makes it run much more effeciently. I took your advice and wrote it as follows- "(lastLogonTimeStamp<=$((Get-Date).AddDays(-30).ToFileTimeUtc()))" + # lastLogon is lower than or equal to 30 days ago "(lastLogonTimeStamp=*)" + # lastLogon attribute must be populated ")" # close the filter Properties = 'GivenName', 'SN', 'DisplayName', 'Company', 'LastLogonDate' My results look more accurate, however my fear is that I may still have false positives.

Santiago Squarzon Over a year ago

@KeepVBSalive well yeah you should query all your DCs using lastLogon (what I would do) but that was not part of the question. The question was how to exclude givenName and sn :P

Collectives™ on Stack Overflow

Exclude specific results exported in CSV via Powershell Script

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related