Search code examples
powershellactive-directory

Too Many -or's in my filter in powershell?

I'm trying to come up with a script that will remove Computer objects from our corporate AD that have not logged in within 90 days. When I run the below and have it export to a csv, all the devices in the export have logon dates of over 90 days ago.

$LastLogonDate = (Get-Date).Adddays(-(90))
Get-ADComputer -Filter {LastLogonTimeStamp -le $LastLogonDate}

When I run the same script with some more filters related to Name -like "namingscheme" -or Name -like "anothernamingscheme" and so on, it adds an extra 660 devices that HAVE checked in within 90 days, most of them within the past 2 weeks. There are 37 "-or"'s in the command plus the 1 -"and" for a total of 38 name types it's looking for. Too many? I'm trying to look for devices that are definitely NOT our servers, but each office had its own naming convention (we are working on standardizing them). I'm sure there's a better way to do this? I tried to do a Name -ne "servername" but those naming conventions are across the board too.

Any thoughts on why when I start adding the Name options to the filter that the extra 660 devices show up? I hope this made sense...

$LastLogonDate = (Get-Date).Adddays(-(90))
Get-ADComputer -Filter {LastLogonTimeStamp -le $LastLogonDate}

The above gives an accurate reading of all devices that haven't checked in within 90 days. 1805 devices.

$LastLogonDate = (Get-Date).Adddays(-(90))
Get-ADComputer -Filter {LastLogonTimeStamp -le $LastLogonDate -and Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*" -or Name -like "office-*"} -Properties LastLogonTimeStamp, Name, OperatingSystem | Export-csv "c:\LastLogOn90Days.csv"

The above gives the extra 660 devices for a total of 2465. Note that the "office" name has been changed to office just for this post. They are all different actual names in the actual script and the names do report right in the export. Just 660 extra devices that have checked in within the 90 days are also reported compared to the first script

Solution

  • The reason is most likely missing parenthesis in your OR conditions:

    Get-ADComputer -Filter { LastLogonTimeStamp -le $LastLogonDate -and Name -like 'office-*' -or Name -like 'office-*' ... }

    Should be:

    Get-ADComputer -Filter { LastLogonTimeStamp -le $LastLogonDate -and (Name -like 'office-*' -or Name -like 'office-*' ...) }

    Why is that the case? Because of how the AD provider is translating the filter, you can use Convert-ADFilter to get a picture on how the provider translates your filters to LDAP syntax, see comparison below:

    $LastLogonDate = (Get-Date).Adddays(-90)
$convertADFilterSplat = @{
    Filter      = { LastLogonTimeStamp -le $LastLogonDate -and Name -like 'office-*' -or Name -like 'office-*' }
    CommandName = 'Get-ADComputer'
}

Convert-ADFilter @convertADFilterSplat

    Translates to LDAP Filter:

    (|
  (&
    (LastLogonTimeStamp<=133434401747575412)
    (name=office-*)
  )
  (name=office-*)
)

    Which is clearly wrong, you can read it as "(objects where their LastLogonTimeStamp is lower than or equal to 133434401747575412 AND Name starts with office-) OR (Name starts with office-)".

    Instead, if using parentheses to wrap the OR conditions:

    $LastLogonDate = (Get-Date).Adddays(-90)
$convertADFilterSplat = @{
    Filter      = { LastLogonTimeStamp -le $LastLogonDate -and (Name -like 'office-*' -or Name -like 'office-*') }
    CommandName = 'Get-ADComputer'
}

Convert-ADFilter @convertADFilterSplat

    Translates to:

    (&
  (LastLogonTimeStamp<=133434402342044131)
  (|
    (name=office-*)
    (name=office-*)
  )
)

    Which sounds much better, can be read as "(objects where their LastLogonTimeStamp is lower than or equal to 133434401747575412) AND (Name starts with office- OR Name starts with office-)".

    As aside, a reminder that LastLogonTimeStamp attribute is not precise, there can be up to 14 days difference. If you want to get accurate results you must query all your Domains Controllers using LastLogon attribute in your filter. See this answer for more details.