Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-05-11
8
Medium Priority
?
12,885 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:cjones_mcse
  • 4
  • 4
8 Comments
 
LVL 41

Expert Comment

by:graye
ID: 13982627
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
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13988284
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
 
LVL 41

Expert Comment

by:graye
ID: 13991528
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Author Comment

by:cjones_mcse
ID: 13991805
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
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 13996957
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
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13997416
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
 
LVL 41

Expert Comment

by:graye
ID: 13999871
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
 
LVL 10

Author Comment

by:cjones_mcse
ID: 14010723
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question