Filtering With a Variable Using Get-ADUser

Question

Currently I am pulling a list of emails from a SQL database, and storing these emails in a variable named $emaildata. I'm attempting to filter out any email in $emaildata from Active Directory. Right now no errors are popping up, but it's definitely not taking the emails out of the list that show up in the variable.

$ADUsers = Get-AdUser -Filter * -Properties SamAccountName, EmailAddress, Title, LastLogonDate, Name, GivenName, Surname, Company, Department, Country, EmployeeNumber, Enabled | Where {
    ($_.LastLogonDate -ge (Get-Date).AddDays(-30)) -and
    ($_.LastLogonDate -ne $null) -and
    ($_.EmailAddress -ne $null) -and
    ($_.EmployeeNumber -like '1000*') -and
    ($_.Enabled -eq 'True') -and
    ($_.EmailAddress -ne $emaildata)
} | Select GivenName, Surname, EmailAddress, Title
$ADUsers | Export-Csv C:\Lists\MainList.csv -NoTypeInformation

So it's this piece of the code that I'm attempting to work on.

($_.EmailAddress -ne $emaildata)

I've been looking for quite some time and everything I try hasn't worked, so I was hoping you guys would be able to help me out. Thanks!

The data in $emaildata looks like this

EmailAddress
------------
test1@test.com
test2@test.com
test3@test.com
test4@test.com
test5@test.com

Answer 1

$emailDataStringList = @( $emailData | Foreach-Object { return $_.EmailAddress.Trim() } )

.... | Where-Object { ...
    ($_.EmailAddress -ne $null) -and
    ($_.EmailAddress -ne '') -and
    ($emailDataStringList -notcontains $_.EmailAddress)
}

Your $emailData is not array of strings ( [string[]] ), it is array of objects with single property: EmailAddress ( according to your output ). There is many options to check if $adUser.EmailAddress is in list of $emailData, but simpliest is to convert array of objects to array of strings:

$emailDataStringList = @( $emailData | Foreach-Object { return $_.EmailAddress.Trim() } )

Sure there is a problem with Enabled: You compare it as string, you should compare it as bool:

$_.Enabled -eq $true

Also here may be a problem with lastLogonDate: in -and expressions should FIRST check for -ne $null and only next step you can be sure there is a valid value to operate with. Otherwise you can hit strange behaviour with $null -comparsionOperator [DateTime]

($_.LastLogonDate -ne $null) -and
($_.LastLogonDate -ge (Get-Date).AddDays(-30)) 

Also, set variable $dateToCheck = [DateTime]::Today.AddDays(-30) and in Where-Object expression compare $_.LastLogonDate -ge $dateToCheck. This will be faster, powershell will compare LastLogonDate with predefined value and it will not calculate new DateTime each iteration.

According to Get-ADUser you can use -LDAPFilter '(&(mail=*)(LastLogonTimeStamp=*))' and throw away -ne $null checks and minimize memory usage and network usage. LDAPFilter and Filter are processed server-side

Next, please, take an IMPORTANT note: There is no attribute LastLogonDate attribute in AD. In Get-AD* it is dynamically calculated from LastLogonTimeStamp. LastLogonTimeStamp is NOT replicated immediately - it is stored only on DC, who processed user logon and replicated to another DC only if another DC's value differs for more than ms-DS-Logon-Time-Sync-Interval value ( 14 days default ). So, you should know about it: LastLogonDate on your DC can be outdated for up to 14 days. ( See detailed explanation ). You MUST check each DC in your forest if you really need accurate LastLogonDate. This is quite heavy and time-taking task.

Next, force Get-ADUser and $... | Where { ... } to be an array (use array conversion brackets @() ). Otherwise, you may get problems with your code when those expressions return single user or no users ($null). Enclosing to @() brackets, you guaranttee that your variable will be an array with 0, 1 or more elements.

Next, take a note please that you can hit timeout-like error here, because there is a timeout on Get-AD* cmdlets and if expression after pipe takes much time, the Get-ADUser will be terminated, pipe will be destroyed and you hit error.

Better store Get-ADUser results into a variable and filter in a separate expression:

$attributes = @('samaccountname', 'lastlogondate', '.....')    
$ADUsers = @( Get-ADUser -options.... -Properties $attributes )
$ADUsers = @( $ADUsers | Where-object { ... } )
$ADUsers | Select $attributes | Export-Csv -options...

P.S. Maybe there is whitespaces in your AD or Database. Use .Trim() in Where check:

$emailAddresses = @( $emailAddresses | Foreach-Object { return $_.Trim() } ) 
$... | Where { $emailAddresses -contains $_.EmailAddress.Trim()  }

---

your code will be like this:

$dateToCheck = [DateTime]::Today.AddDays(-30)
$emailDataStringList = @( $emailData | Foreach-Object { return $_.EmailAddress.Trim() } )
$ADUserProperties = @('SamAccountName', 'EmailAddress', 'Title', 'LastLogonDate', 'Name', 'GivenName', 'Surname', 'Company', 'Department', 'Country', 'EmployeeNumber', 'Enabled')
$ADUserList = @( Get-AdUser -LDAPFilter '(&(mail=*)(LastLogonTimeStamp=*)(employeeNumber=1000*))' -Properties $ADUserProperties )
$ADuserList = @( $ADUserList |
    Where-Object { $_.Enabled -eq $True} | # This is simplies check, this should be first
    Where-Object { $_.LastLogonDate -ge $dateToCheck } | 
    Where-Object { $emailDataStringList -notcontains $_.EmailAddress.Trim()}  | # This is hardest cpu-taking check, this should be last
    Select @('GivenName', 'Surname', 'EmailAddress', 'Title')
)
$ADuserList | Export-Csv C:\Lists\MainList.csv -NoTypeInformation

---

If you need more accurate LastLogonDate, you should get ADUsers from EACH DC in your envinronment. Something like this:

$DCList = @(get-addomaincontroller -filter *) 
$ADUserList = @( $DCList | ForEach-Object { Get-ADUser -Server $_.DNSName ... -properties @('SID', 'lastlogontimestamp', ...) } )
$ADUserList = @( $ADUserList | Sort-Object -Descending -Property 'lastlogontimestamp' | Sort-Object -Property 'SID' -Unique )

According to MS docs, -Unique takes first object in the list, so it should take the most accurate LastLogonDate for each user