Assistance needed with Search filter and date as string

Question

I have an export that will run as a monthly scheduled task that will write output to a CSV to contain employees that have been terminated in the last month. I've initialized variable to contain the start date ($lastMonth) and added a comparison to my Search filter, but am not getting any results. I definitely have one record that contains a terminationDate. I suspect my filter is incorrect. The terminationDate attribute is stored as a string and I'm attempting to compare it with a date. If I remove the terminationDate <=... filter, I get the one record I'm expecting. I'm attaching a snippet below...

$lastMonth = (Get-Date).AddMonths(-1).ToFileTime()

# Get AdsPath of target object
$targetObject = $Context.BindToObjectByDN("%distinguishedName%")
$targetObjectPath = $targetObject.ADsPath

# Search all users in the target OU
$userSearcher = New-Object "Softerra.Adaxes.Adsi.Search.DirectorySearcher" $NULL, $False
$userSearcher.SearchParameters.BaseObjectPath = $targetObjectPath
$userSearcher.SearchParameters.PageSize = 1000
$userSearcher.SearchParameters.SearchScope = "ADS_SCOPE_SUBTREE"
# Filter for ALL objects where employeeID doesn't contain zeros or 'NMU' and employeeID is not blank and mail is not blank and terminationDate is populated
$userSearcher.SearchParameters.Filter = "(&(objectCategory=person)(objectClass=user)(!(employeeID=000000))(!(employeeID=NMU))(!(employeeID=?))(employeeID=*)(mail=*)(terminationDate=*)([datetime]terminationDate>=$lastMonth))"
$properties = $eachFieldIn
$userSearcher.SetPropertiesToLoad($properties)

Comments

It looks like the get-date cmdlet stores the date as an Int64 not DateTime when using "ToFileTime". Aside from that, I'm not sure if using "[datetime]terminationDate" in the filter would actually work to convert the string to datetime. You could filter on anybody that has a terminationdate attribute populated and then do the date comparison after the fact, which I realize isn't as efficient, but still an alternative.

---

Hello,

Since dates are stored as plain text, you won't be able to compare them to the current date. In this case, the >= operator in your LDAP filter can only compare strings instead of comparing the actual dates.

To do what you need, you'll need to get the value of the terminationDate attribute of each user, convert it to a DateTime structure, and then compare it to the target date.

Can you provide the format in which dates are stored in the terminationDate attribute so we could provide you a script? Just a couple of examples of the dates stored in the attribute will be enough.

---

Screen capture attached.

Answer 1

Hello,

Here's a code snippet that will do the job:

# Search users
$userSearcher = $Context.TargetObject
$userSearcher.PageSize = 500
$userSearcher.SearchScope = "ADS_SCOPE_SUBTREE"

# Filter for ALL objects where employeeID doesn't contain zeros or 'NMU' and employeeID is not blank and mail is not blank and terminationDate is populated
$userSearcher.SearchFilter = "(&(objectCategory=person)(objectClass=user)(!(employeeID=000000))(!(employeeID=NMU))(!(employeeID=?))(employeeID=*)(mail=*)(terminationDate=*))"

# Add terminationDate to properties that will be fetched
$propertiesToLoad = $eachFieldIn + "terminationDate"
$userSearcher.SetPropertiesToLoad($propertiesToLoad)

try
{
    $searchResultIterator = $userSearcher.ExecuteSearch()
    $searchResults = $searchResultIterator.FetchAll()
    $lastMonth = (Get-Date).AddMonths(-1)

    foreach ($searchResult in $searchResults)
    {
        # Get termination date for the user
        $terminationDateString = $searchResult.Properties["terminationDate"].Value

        # Convert to DateTime
        $terminationDate = [Datetime]::ParseExact($terminationDateString, 'MM/dd/yyyy', $NULL)

        # Check if termination date Less-than or equal to $lastMonth
        if ($terminationDate -le $lastMonth)
        {
            # TODO: Your code here
        }
    }
}
finally
{
    # Release resources used by the search
    $searchResultIterator.Dispose()
}