none
Get-ADUser Filter for a blank attribute value

    Question

  • I had a lot of trouble creating a filter to bring back user accounts that do not have the LastLogonTimeStamp value set. I'm looking for some feedback as my only solution is this beast: get-ADUser -Filter {-not((lastLogonTimeStamp -gt 0) -and (lastLogonTimeStamp -lt 999999999999999999))}

    I couldn't find a successful way to specify null, empty, or blank in this filter.

    The goal is to identify recently created user accounts that have not been used to access a web service in 14 days. I'm looking to filter on accounts with a blank LastLogonTimeStamp, and a whenCreated date of -14 days.

    Wednesday, April 6, 2011 8:24 PM

Answers

  • Here is the complete solution I used incase some else needs a similar solution, though be aware it also disables the user account!

    $TargetDate = Get-Date -Date (get-date).AddDays(-14)
    $SearchBase = "OU=People,DC=my,DC=domain,DC=com"
    $Filter = {(whenCreated -lt $TargetDate) -and (-not(lastLogonTimeStamp -like "*"))}
    get-ADUser -Filter $Filter -SearchBase $SearchBase | Disable-ADAccount

     

    Thanks again Tony and Richard!

    • Marked as answer by spork_ Wednesday, April 20, 2011 2:02 PM
    Wednesday, April 20, 2011 2:02 PM
  • I had a lot of trouble creating a filter to bring back user accounts that do not have the LastLogonTimeStamp value set. I'm looking for some feedback as my only solution is this beast: get-ADUser -Filter {-not((lastLogonTimeStamp -gt 0) -and (lastLogonTimeStamp -lt 999999999999999999))}

    I couldn't find a successful way to specify null, empty, or blank in this filter.

    The goal is to identify recently created user accounts that have not been used to access a web service in 14 days. I'm looking to filter on accounts with a blank LastLogonTimeStamp, and a whenCreated date of -14 days.


    This would filter on user accounts with a blank lastLogonTimestamp and a whenCreated of -14 days:


    # Date formatting

    $days = 14
    $Today = Get-date
    $SubtractDays = New-Object System.TimeSpan $days, 0, 0, 0, 0
    $StartDate = $Today.Subtract($SubtractDays)
    $startdate = $startdate.ToString("u") -Replace "-|:|\s"
    $startdate = $startdate -Replace "Z", ".0Z"

    # LDAP filter settings
    $filter = "(&(samaccounttype=805306368)(|(whenCreated<=$startDate)(!lastlogontimestamp=*)))"
    $prop1 = "samaccountname"
    $prop2 = "enabled"
    $prop3 = "lastlogontimestamp"
    $prop4 = "displayName"
    $prop5 = "pwdlastset"
    $prop6 = "whenCreated"


    # Find the users
    $adusers = Get-ADUser -LDAPFilter $filter -properties $prop1, $prop2, $prop3, $prop4, $prop5, $prop6

    $adusers


    Tony
    Thursday, April 7, 2011 3:17 AM
  • Note that the "|" character in the filter suggested by Tony is the "Or" operator, so it returns users where either the object was created more than 14 days in the past, or the lastLogonTimeStamp attribute has no value (which probably means they have never logged on). Maybe you want to use the "And" operator, which is "&".

     


    Richard Mueller - MVP Directory Services
    Thursday, April 7, 2011 2:40 PM

All replies

  • I would use:

    Get-ADUser -LDAPFilter "(|(lastLogonTimeStamp=0)(!lastLogonTimeStamp=*))"

     

    The "|" character is the OR operator in LDAP syntax, and "!" is the NOT operator, while "*" is the wildcard character. The first clause is True if the value of lastLogonTimeStamp is 0, the second clause is True if the attribute has no value (it is not true that there is a value).

     


    Richard Mueller - MVP Directory Services
    Thursday, April 7, 2011 1:46 AM
  • I had a lot of trouble creating a filter to bring back user accounts that do not have the LastLogonTimeStamp value set. I'm looking for some feedback as my only solution is this beast: get-ADUser -Filter {-not((lastLogonTimeStamp -gt 0) -and (lastLogonTimeStamp -lt 999999999999999999))}

    I couldn't find a successful way to specify null, empty, or blank in this filter.

    The goal is to identify recently created user accounts that have not been used to access a web service in 14 days. I'm looking to filter on accounts with a blank LastLogonTimeStamp, and a whenCreated date of -14 days.


    This would filter on user accounts with a blank lastLogonTimestamp and a whenCreated of -14 days:


    # Date formatting

    $days = 14
    $Today = Get-date
    $SubtractDays = New-Object System.TimeSpan $days, 0, 0, 0, 0
    $StartDate = $Today.Subtract($SubtractDays)
    $startdate = $startdate.ToString("u") -Replace "-|:|\s"
    $startdate = $startdate -Replace "Z", ".0Z"

    # LDAP filter settings
    $filter = "(&(samaccounttype=805306368)(|(whenCreated<=$startDate)(!lastlogontimestamp=*)))"
    $prop1 = "samaccountname"
    $prop2 = "enabled"
    $prop3 = "lastlogontimestamp"
    $prop4 = "displayName"
    $prop5 = "pwdlastset"
    $prop6 = "whenCreated"


    # Find the users
    $adusers = Get-ADUser -LDAPFilter $filter -properties $prop1, $prop2, $prop3, $prop4, $prop5, $prop6

    $adusers


    Tony
    Thursday, April 7, 2011 3:17 AM
  • Richard,

    Your solution works, but I was able to further tweak it the filter as the lastlogontimestamp will generally be large numbers and not 0. Your use of the negated wildcard is exactly what I was looking for. This filter will get me the results I need:

    Get-ADUser -LDAPFilter "(!lastLogonTimeStamp=*)"

    Thanks for helping, Richard! I'm really glad to have posted the question!

    Thursday, April 7, 2011 1:31 PM
  • The lastLogon attribute, which is similar, can have the value 0. I haven't yet figured out why sometimes the value is missing, and sometimes 0. I have even seen domains where no users have missing lastLogon, but some are 0. However, I have not found any cases where lastLogonTimeStamp is 0, so perhaps it is safe to assume it never happens.

     


    Richard Mueller - MVP Directory Services
    Thursday, April 7, 2011 2:00 PM
  • Tony,

    Thank you for the solution! Unforntunately, in some basic testing I'm receiving more results than expected. I'm going to take a closer look and will follow up. Thanks for taking the solution to the next step my including the whenCreated filtering.  

    Thursday, April 7, 2011 2:04 PM
  • Note that the "|" character in the filter suggested by Tony is the "Or" operator, so it returns users where either the object was created more than 14 days in the past, or the lastLogonTimeStamp attribute has no value (which probably means they have never logged on). Maybe you want to use the "And" operator, which is "&".

     


    Richard Mueller - MVP Directory Services
    Thursday, April 7, 2011 2:40 PM
  • Good suggestion Richard.  I was playing with some code I used previously which uses the OR operator.  It would work better with the AND operator for what spork_ is wanting to achieve.
    Tony
    Thursday, April 7, 2011 8:22 PM
  • Here is the complete solution I used incase some else needs a similar solution, though be aware it also disables the user account!

    $TargetDate = Get-Date -Date (get-date).AddDays(-14)
    $SearchBase = "OU=People,DC=my,DC=domain,DC=com"
    $Filter = {(whenCreated -lt $TargetDate) -and (-not(lastLogonTimeStamp -like "*"))}
    get-ADUser -Filter $Filter -SearchBase $SearchBase | Disable-ADAccount

     

    Thanks again Tony and Richard!

    • Marked as answer by spork_ Wednesday, April 20, 2011 2:02 PM
    Wednesday, April 20, 2011 2:02 PM
  • Dear.

    What if I need an attribute from a specific user, but the attribute is empty.

    The following command works when there is a value:

    [string]$CustomAttribute01=(get-aduser -filter {(mail -eq $email)} | select-object -expandProperty CustomAttribute01)

    But it returns an error when it's empty.

    Can I hide or avoid the error?

    Regards,

    Peter


    Peter Van Keymeulen, IT Infrastructure Solution Architect, www.edeconsulting.be

    Thursday, February 23, 2012 12:43 PM
  • Does the following help?

    [string]$CustomAttribute01=(get-aduser -filter {(mail -eq $email) -and (-not(CustomAttribute01 -like "*"))} | select-object -expandProperty CustomAttribute01)


    Richard Mueller - MVP Directory Services

    Thursday, February 23, 2012 1:21 PM
  • Thanks Richard. But I get the same error.

    Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.

    peter


    Peter Van Keymeulen, IT Infrastructure Solution Architect, www.edeconsulting.be

    Thursday, February 23, 2012 1:43 PM
  • [string]$CustomAttribute01 = get-aduser -filter {(mail -eq $email)} | Where {$_.CustomAttribute01} | select-object -expandProperty CustomAttribute01

    Thursday, February 23, 2012 1:58 PM
  • same issue.

    Peter Van Keymeulen, IT Infrastructure Solution Architect, www.edeconsulting.be

    Thursday, February 23, 2012 3:17 PM
  • same issue.

    Peter Van Keymeulen, IT Infrastructure Solution Architect, www.edeconsulting.be


    PS > Get-ADUser -fi {name -eq "br1"} | select -exp GivenName
    Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj
     to a non-null value.
    At line:1 char:41
    + Get-ADUser -fi {name -eq "br1"} | select <<<<  -exp GivenName
        + CategoryInfo          : InvalidArgument: (:) [Select-Object], PSArgumentNullException
        + FullyQualifiedErrorId : ArgumentNull,Microsoft.PowerShell.Commands.SelectObjectCommand
    
    #I have no problem,return $null instead of the error
    PS > Get-ADUser -fi {name -eq "br1"} | ? {$_.GivenName} |select -exp GivenName
    PS C:\Users\Administrator>

    Thursday, February 23, 2012 5:08 PM
  • I am also facing the Same problem,

    I need the Empty Attribute data also, But it returns only if the attribute value available .

    Unable to find the solution can any one help me, Here is the code iam using

    I Need the Custom attribute Value which is Empty or less than 69.

    $mailid = get-content mail.txt
    $output = foreach ($user in $mailid) {get-aduser -filter {((mail -eq $user) -AND (customattribute -le "69"))} -properties name,mail,customattribute}
    $output | select name,mail,customattribute|export-csv opt.csv


    Wednesday, May 29, 2013 11:39 AM
  • This question is very old but still pops up in search so I thought I would post a solution that works in powershell 5.1:

    Get-ADUser -filter {LastLogonDate -notlike "*"}

    No idea if this was available back in 2011 but I confirmed it works now. It returned accounts that had null value for LastLogonDate, and did not return accounts that had a value in that attribute.


    Friday, July 13, 2018 6:47 PM