INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Determine old AD Users and Computers by SgtJarrow
Posted: 29 Dec 03 (Edited 21 Sep 06)

First, I have to thank gtog in thread183-587842 for his basic code there....it lead me down the path to victory.

My boss wanted a way to search all users and computers in Active Directory and determine stale (older than 90 days) accounts.  According to Microsoft and MSDN, the AD key in question would be the pwdLastSet.  But this is a crazy long integer.  More research and I found that the PasswordLastChanged key is the pwdLastSet key converted to readable day and time.  I also found a few references to VB scripts for checking a single user.

Not being a true VB programmer, I began searching for ways to do this same stuff in Access.  And I finally found it.  Here it is if you are interested.

Create a new database and make sure you have a reference to the Microsoft DAO 3.6 object library.  Create a new table called tblAccounts and include the following fields:

Account         String     255     Primary Key
NeverExpires    Yes/No
LastDate        Date/Time    General Format
Expired         Yes/No
WillExpire      Date/Time    General Format

Paste the following into a new module:

' ****************************************************

Sub QueryAD(strType As String)
    On Error Resume Next
    Set oConnection1 = CreateObject("ADODB.Connection")
    Set oCommand1 = CreateObject("ADODB.Command")
    ' Open the connection.
    oConnection1.Provider = "ADsDSOObject"  ' This is the ADSI OLE-DB provider name
    oConnection1.Open "Active Directory Provider"
    ' Create a command object for this connection.
    Set oCommand1.ActiveConnection = oConnection1
    ' Compose a search string.
    If strType = "Users" Then
        oCommand1.CommandText = "SELECT * FROM 'LDAP://ldapservername' WHERE objectcategory ='Person' AND objectclass='User'"
    Else
        oCommand1.CommandText = "SELECT * FROM 'LDAP://ldapservername' WHERE objectcategory ='Computer'"
    End If
    ' Execute the query.
    Set rs = oCommand1.Execute
    DoCmd.RunSQL ("DELETE * FROM tblAccounts")
    Dim rsAccounts As DAO.Recordset
    Set rsAccounts = CurrentDb.OpenRecordset("tblAccounts", dbOpenDynaset)
    '--------------------------------------
    ' Navigate the record set
    '--------------------------------------
    With rsAccounts
        Do While Not rs.EOF
            Set objUser = GetObject(rs.Fields(0))
            If objUser.Get("userAccountControl") And &H10000 Then
                .AddNew
                .Fields("Account") = Mid(objUser.Name, 4)
                .Fields("NeverExpires") = True
                .Update
            Else
                .AddNew
                .Fields("Account") = Mid(objUser.Name, 4)
                .Fields("LastDate") = objUser.PasswordLastChanged
                .Fields(&quot;Expired&quot;) = IIf(objUser.PasswordLastChanged <= DateAdd(&quot;d&quot;, -90, Date), True, False)
                .Fields(&quot;WillExpire&quot;) = DateAdd(&quot;d&quot;, 90, objUser.PasswordLastChanged)
                .Update
            End If
            rs.MoveNext
        Loop
    End With
End Sub

' ****************************************************

To find the expired users, simply use:

Call QueryAD(&quot;Users&quot;)

and for computers its:

Call QueryAD(&quot;Computers&quot;)

Your info will appear in the table for your editing/querying/exporting/etc.

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close