CODEWITHSUNDEEP
powershell
KeepVBSalive
KeepVBSalive

Reputation: 21

Exclude specific results exported in CSV via Powershell Script

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

Upvotes: 1

Views: 137

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 60220

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:

Upvotes: 1

Related Questions