DirectorySearcher filter how to limit based on last 30 days

Question

I am searching Active Directory for users in a specific OU. I am only getting the users that have logged on in the last 30 days.

My search filter Query is:

string query = "(&(objectCategory=person)(objectClass=user)((lastLogon<=" + new DateTime(DateTime.Now.AddDays(-30).Ticks) + ")(mail=*))";

I get search filter is invalid I have used:

string query = "(&(objectCategory=person)(objectClass=user)((lastLogon=*)(mail=*))";

With no error

I have modified the last logon as follows:

(lastLogon<=1)

I am calling a method that does this

public static DataTable GetADusers() {
    try {
        string ou = "OU";

        using(PrincipalContext ctx = new PrincipalContext(ContextType.Domain, Environment.UserDomainName, ou)) {
            UserPrincipal user = new UserPrincipal(ctx);
            using(PrincipalSearcher ps = new PrincipalSearcher(user)) {
                DataTable results = new DataTable();

                results.Columns.Add("DisplayName ");
                results.Columns.Add("FirstName");
                results.Columns.Add("Initial");
                results.Columns.Add("LastName");
                results.Columns.Add("mail");
                results.Columns.Add("SamAccountName");
                results.Columns.Add("DistinguishedName");
                results.Columns.Add("lastLogon");

                int count = 0;

                int ctNull = 0;

                foreach(Principal p in ps.FindAll()) {
                    UserPrincipal u = p as UserPrincipal;
                    if (u != null) {
                        DirectoryEntry entry = (DirectoryEntry) p.GetUnderlyingObject();
                        DirectorySearcher search = new DirectorySearcher(entry);

                        string query = "(&(objectCategory=person)(objectClass=user)((lastLogon<=" + new DateTime(DateTime.Now.AddDays( - 30).Ticks) + ")(mail=*))";

                        search.Filter = query;
                        search.PropertiesToLoad.Add("DisplayName");
                        search.PropertiesToLoad.Add("GivenName");
                        search.PropertiesToLoad.Add("Initials");
                        search.PropertiesToLoad.Add("sn");
                        search.PropertiesToLoad.Add("mail");
                        search.PropertiesToLoad.Add("SamAccountName");
                        search.PropertiesToLoad.Add("DistinguishedName");
                        search.PropertiesToLoad.Add("lastLogon");

                        SearchResultCollection mySearchResultColl = search.FindAll();

                        foreach(SearchResult sr in mySearchResultColl) {
                            DataRow dr = results.NewRow();
                            DirectoryEntry de = sr.GetDirectoryEntry();
                            dr["EmployeeID"] = de.Properties["EmployeeID"].Value;
                            dr["DisplayName "] = de.Properties["DisplayName"].Value;
                            dr["FirstName"] = de.Properties["GivenName"].Value;
                            dr["Initial"] = de.Properties["Initials"].Value;
                            dr["LastName"] = de.Properties["sn"].Value;
                            dr["mail"] = de.Properties["mail"].Value;
                            dr["SamAccountName"] = de.Properties["SamAccountName"].Value;
                            dr["DistinguishedName"] = de.Properties["DistinguishedName"].Value;
                            //prepare for last logon
                            if (de.Properties["lastLogon"] != null && de.Properties["lastLogon"].Count > 0) {
                                Int64 lastLogonThisServer = new Int64();
                                IADsLargeInteger lgInt = (IADsLargeInteger) de.Properties["lastLogon"].Value;
                                lastLogonThisServer = ((long) lgInt.HighPart << 32) + lgInt.LowPart;
                                dr["lastLogon"] = DateTime.FromFileTime(lastLogonThisServer).ToString();
                            }
                            else {
                                dr["lastLogon"] = DateTime.MinValue.ToString();
                                ctNull++;
                            }

                            results.Rows.Add(dr);
                            count++;

                        }

                    }
                }
                Console.WriteLine(count);
                Console.WriteLine("Null");
                Console.WriteLine(ctNull);

                return results;
            }

        }
    }
    catch(NullReferenceException ex) {
        Console.WriteLine("data error" + ex);
        DataTable dt = new DataTable();
        return dt;
    }
}

The above function works well! There must be a way to check if the last logon is over 30 days old. I would appreciate any help. Thank You!

The answer below is correct thanks

I had to add the following code to place the data into the database:

            if (de.Properties["LastLogonTimestamp"] != null && de.Properties["LastLogonTimestamp"].Count > 0)
                                {
                                    Int64 lastLogonDateThisServer = new Int64();
                                    IADsLargeInteger lgInt = (IADsLargeInteger)de.Properties["LastLogonTimestamp"].Value;
                                    lastLogonDateThisServer = ((long)lgInt.HighPart << 32) + lgInt.LowPart;
                                    dr["LastLogonTimestamp"] = DateTime.FromFileTime(lastLogonDateThisServer).ToString();

                                }
                                else
                                {

                                    dr["LastLogonTimestamp"] = DateTime.MinValue.ToString();
                                    ctNull++;
                                }

I placed it below the lastLogon For the query filter: I had to reverse the < to get the data from now to that 30 day mark.

                            string query = "(&(objectCategory=person)(objectClass=user)(!userAccountControl:1.2.840.113556.1.4.803:=2)(!userAccountControl:1.2.840.113556.1.4.803:=65536)(userAccountControl:1.2.840.113556.1.4.803:=262144)(userPrincipalName=1*@mil)(lastlogon>=" + DateTime.Now.AddDays(-90).ToFileTime() + ")(lastLogonTimestamp>=" + DateTime.Now.AddDays(-90).ToFileTime() + ")(mail=*))";

Answer 1

In your query, you have two parentheses in front of lastLogon when you don't need them:

((lastLogon<=...

That's why you're getting the "search filter is invalid" error. That should be:

(lastLogon<=...

But you are also calculating the value wrong. You have this:

new DateTime(DateTime.Now.AddDays( - 30).Ticks)

But lastLogon does not use ticks.

Later on in your code, you are using FromFileTime to read back the lastLogon. Likewise, you can use ToFileTime to convert a date to the format you can use in the query. Like this:

DateTime.Now.AddDays(-30).ToFileTime()

But keep in mind that lastLogon does not replicate - it is only accurate on the last domain controller that the user authenticated against. So if your domain has more than one domain controller, you won't get an accurate value for most accounts.

The lastLogonTimestamp attribute was created for just this reason. It replicates at least every 2 weeks, so you know the value is accurate within 2 weeks. You can use the same format for that:

string query = "(&(objectCategory=person)(objectClass=user)(lastLogonTimestamp<="
    + DateTime.Now.AddDays(-30).ToFileTime() + ")(mail=*))";

You can read more about the lastLogonTimestamp attribute here: https://blogs.technet.microsoft.com/askds/2009/04/15/the-lastlogontimestamp-attribute-what-it-was-designed-for-and-how-it-works/