Need LDAP Query for AD users not logged on in XX days

For some reason, our built in Server 2003 AD query for users not logged in in XX number of days does not work. I'd like to write the query myself and save it in Saved Queries, but I just can't figure this LDAP syntx out. I found the following for users that have never logged on:

(&(objectCategory=person)(objectClass=user))(|(lastLogon=0)(!(lastLogon=*)))

How can I modify this to return users that haven't logged on in the last XX number of days?
LVL 10
cjones_mcseAsked:
Who is Participating?
 
grayeCommented:
Wow!   Learn something every day!  

That's a very cool (but also very hidden feature) of AD Users and Computers.   I was familiar with the traditional search query builder that only allowed you to pick fields off of a pick list... I never knew there was a free-form LDAP query cabability

OK... to get what you need, you're gonna have to create the date you want to compare against in the format that LDAP uses for dates.   That's really the tought part.   LDAP uses a 64-bit number as a datefield that is comprised of the number of 1/10th seconds since January 1, 1601.  Details can be found here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adschema/adschema/a_lastlogon.asp

So you're really gonna need a calculator to be able to figure out how to generate dates that LDAP will understand.

So, let's assume that you've figured that part out... here is what the LDAP query would look like:

(&(&(objectCategory=person)(objectClass=user))(lastLogin<=27604693676718750))
0
 
grayeCommented:
That's kinda tough... the lastlogin attribute is not normally replicated amoung Domain Controllers.   That means the only way to really determine the LastLogin time is to poll all of the Domain Controllers (unless you just have one...) and then save the newest date.

Here is an example (from a working VB.Net application, so you should probably ignore a bunch of unrelated junk):

        de = New DirectoryEntry("LDAP://" & DOMAIN & "/" & OU)
        srch = New DirectoryServices.DirectorySearcher(de)
        srch.Filter = "(&(objectClass=user)(objectCategory=person))"

        For Each result In srch.FindAll()
            dir = result.GetDirectoryEntry
            Name = dir.Properties("name").Value
            NameDN = dir.Properties("distinguishedName").Value
            i = dir.Properties("userAccountControl").Value
            Acct_Disabled = CBool(i And &H2)
            Acct_Locked = CBool(i And &H10)
            Password_Reset = CBool(i And &H800000)

            ' since last login is stored in each domain controller,
            ' we must poll them all to see who as the latest date.
            ' BTW: This takes a while too...
            last = CDate("01/01/1980")
            For Each dc In dc_list
                TempDate = CDate("01/01/1980")
                buf = "WinNT://" & dc & "/" & dir.Properties("name").Value & ",user"
                Try
                    dc_acct = New DirectoryEntry(buf)
                    TempDate = dc_acct.Properties("lastlogin").Value
                Catch
                End Try
                If DateDiff("d", Last, TempDate) > 0 Then
                    Last = TempDate
                End If
            Next
        Next
0
 
cjones_mcseAuthor Commented:
We do have only one DC so we don't need to worry about polling any others. I'm sorry, I'm not seeing what I should be using in your example. Could you point out what's relative?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
grayeCommented:
Uh... well... everything!

The first few lines are setting up the LDAP query based upon a starting OU.   The big loop is going through each of the User accounts found anywhere below the OU starting point and recording a few interesting items (name, Distinguished Name, Account Disabled, Accout Locked, and Password Reset).   The inner loop is the one that polls all of the domain controllers for the "lastlogin" value.... but in your case it'd be a lot simplier (it'd be just like the other attributes above that point).

My example is using VB.Net and the DirectoryServices namespace of the .Net framework.   It wasn't intended to be a complete solution... it was designed to show the techinque for searching Active Directory and enumerating a few attributed.

What platform, programming language, etc are you using?
0
 
cjones_mcseAuthor Commented:
Not really using one. Going into AD Users and Computers, right click the domain, select find, change to custom search, advanced, and there is a box to put an LDAP query. That's where I put that query I posted in the original question. It works fine for its purpose, but I don't know how to modify that to pull only the users that are xx number of days since last logon. Everything I've tried has not given correct results.
0
 
cjones_mcseAuthor Commented:
Well that sucks. So you wouldn't happen to have any ideas why the functions that are supposedly available in a Server 2003 domain in Windows 2003 functionality mode are not working would you? If they were, I could just use the built in search for users that haven't logged in for the last 30, 60, 90, 120, 180 days. Looks like this is just going to be a complete pain the keester otherwise.
0
 
grayeCommented:
Yeah, I'd consider a "programming approach"... not too unlike my first example.

Using "raw" LDAP data isn't as easy as using the "cooked" ADSI interface. (for example, under ADSI, dates are actually dates!!)

Even a simple VBScript can use the ADSI interface and get data that's already been cooked.
0
 
cjones_mcseAuthor Commented:
Okay, well thanks. You answered my question anyway. There's no easy way to modify that simple query to return what I need. Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.