No announcement yet.

Active Directory Query Scripting in MS Access

  • Filter
  • Time
  • Show
Clear All
new posts

  • Active Directory Query Scripting in MS Access

    I have an access database that contains a list of usernames. The users are placed in this database when they "update" an in-house application. Basically the in-house update program will log the username along with a couple of other comma delimited values into a log file and a script will then disseminate the vales into the database. The problem is that when these users leave the company they remain in the database. the only way I know that they are gone is that I see that their last update was months ago and I then try to find their usernames in Active Directory. If they do not exist i purge them.

    My question is how can I write a script within Access to query the usernames in the database against AD and then flag the users that no longer exist? I should like to point out that the "users" in my database are the actual valid Win NT user names.


  • #2
    Re: Active Directory Query Scripting in MS Access


    In my domain I run into a similar situation where active user accounts are left after an employee leaves the company. Try as I might, notifications are never sent to secure the user account.

    Instead I use a vbscript piped out to an Excel spreadsheet to loop through all the user accounts and show the "lastlogon" attribute of each user.
    Not an elegant way to get the job done however, you can run this script on a regular basis as part of your maintenance routine. The script should run on your domain without modification.

    Let me know if this works for you.

    Option Explicit 
    Dim objRootDSE, strConfig, objConnection, objCommand, strQuery 
    Dim objRecordSet, objDC 
    Dim strDNSDomain, objShell, lngBiasKey, lngBias, k, arrstrDCs() 
    Dim strDN, dtmDate, objDate, lngDate, objList, strUser 
    Dim strBase, strFilter, strAttributes, lngHigh, lngLow 
    'function to determine if account is disabled or not 
    'returns boolean value of true if the account is disabled, false if it is not 
    Function IsAccountDisabled( strDomain, strAccount ) 
       Dim objUser 
       Set objUser = GetObject("WinNT://" & strDomain & "/" & strAccount & ",user") 
       IsAccountDisabled = objUser.AccountDisabled 
    End Function 
    ' Create dictionary to keep track of 
    ' users and login times 
    Set objList = CreateObject("Scripting.Dictionary") 
    objList.CompareMode = vbTextCompare 
    ' rmueller's script 
    ' Obtain local Time Zone bias from machine registry. 
    Set objShell = CreateObject("Wscript.Shell") 
    lngBiasKey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _ 
    & "TimeZoneInformation\ActiveTimeBias") 
    If UCase(TypeName(lngBiasKey)) = "LONG" Then 
    lngBias = lngBiasKey 
    ElseIf UCase(TypeName(lngBiasKey)) = "VARIANT()" Then 
    lngBias = 0 
    For k = 0 To UBound(lngBiasKey) 
       lngBias = lngBias + (lngBiasKey(k) * 256^k) 
    End If 
    ' Determine configuration context and DNS domain from RootDSE object. 
    Set objRootDSE = GetObject("LDAP://RootDSE") 
    strConfig = objRootDSE.Get("configurationNamingContext") 
    strDNSDomain = objRootDSE.Get("defaultNamingContext") 
    ' Use ADO to search Active Directory for ObjectClass nTDSDSA. 
    ' This will identify all Domain Controllers. 
    Set objCommand = CreateObject("ADODB.Command") 
    Set objConnection = CreateObject("ADODB.Connection") 
    objConnection.Provider = "ADsDSOObject" 
    objConnection.Open "Active Directory Provider" 
    objCommand.ActiveConnection = objConnection 
    strBase = "<LDAP://" & strConfig & ">" 
    strFilter = "(objectClass=nTDSDSA)" 
    strAttributes = "AdsPath" 
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree" 
    objCommand.CommandText = strQuery 
    objCommand.Properties("Page Size") = 100 
    objCommand.Properties("Timeout") = 60 
    objCommand.Properties("Cache Results") = False 
    Set objRecordSet = objCommand.Execute 
    ' Enumerate parent objects of class nTDSDSA. Save Domain Controller 
    ' AdsPaths in dynamic array arrstrDCs. 
    k = 0 
    Do Until objRecordSet.EOF 
    Set objDC = _ 
    ReDim Preserve arrstrDCs(k) 
    arrstrDCs(k) = objDC.DNSHostName 
    k = k + 1 
    ' Retrieve lastLogon attribute for each user on each Domain Controller. 
    For k = 0 To Ubound(arrstrDCs) 
    strBase = "<LDAP://" & arrstrDCs(k) & "/" & strDNSDomain & ">" 
    strFilter = "(&(objectCategory=person)(objectClass=user))" 
    'added sAMAccount name for readability in output - el 
    strAttributes = "distinguishedName,lastLogon,sAMAccountName," 
    strQuery = strBase & ";" & strFilter & ";" & strAttributes _ 
       & ";subtree" 
    objCommand.CommandText = strQuery 
    On Error Resume Next 
    Set objRecordSet = objCommand.Execute 
    If Err.Number <> 0 Then 
       On Error GoTo 0 
       Wscript.Echo "Domain Controller not available: " & arrstrDCs(k) 
       On Error GoTo 0 
       Do Until objRecordSet.EOF 
         strDN = objRecordSet.Fields("sAMAccountName") 
         lngDate = objRecordSet.Fields("lastLogon") 
         On Error Resume Next 
         Set objDate = lngDate 
         If Err.Number <> 0 Then 
           On Error GoTo 0 
           dtmDate = #1/1/1601# 
           On Error GoTo 0 
           'stored as a 64 bit integer that VBScript cannot handle, must 
           'seperate it to work with it. 
           lngHigh = objDate.HighPart 
           lngLow = objDate.LowPart 
           If lngLow < 0 Then 
             lngHigh = lngHigh + 1 
           End If 
           If (lngHigh = 0) And (lngLow = 0 ) Then 
             dtmDate = #1/1/1601# 
             dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) _ 
               + lngLow)/600000000 - lngBias)/1440 
           End If 
         End If 
         If objList.Exists(strDN) Then 
           If dtmDate > objList(strDN) Then 
             objList(strDN) = dtmDate 
           End If 
           objList.Add strDN, dtmDate 
         End If 
    End If 
    Dim objNetwork,objExcel 
    dim intRow 
    intRow = 2 
    Set objNetwork = Wscript.CreateObject("Wscript.Network") 
    Set objExcel = CreateObject("Excel.Application") 
    objExcel.Visible = True 
    objExcel.Cells(1,1).value = "UserName" 
    objExcel.Cells(1,2).value = "Last Login" 
    objExcel.Cells(1,3).value = "Disabled" 
    ' Output latest lastLogon date for each user. 
    Dim easydate, iDiff,strDays 
    strDays = InputBox("Enter number of days: ","Last Login", "60") 
    strDays = CInt(strDays) 
    For Each strUser In objList 
    'get rid of the timestamp, only need the date 
    easydate = objList(strUser) 
    easydate = FormatDateTime(easydate,2) 
    'calculate the difference between last login date 
    'and now. 
    iDiff = DateDiff("d",easydate,Now) 
    ' check to see if account has been inactive for x number of days 
    If iDiff >= strDays Then 
    'output username and last login date 
    ' change 1/1/1601 to Never because it looks nicer 
    If easydate = "1/1/1601" Then 
     easydate = "Never" 
    End If  
    'Else WScript.Echo strUser & " ; " & easydate 
    'End If 
    objExcel.Cells(intRow,1).value = strUser 
    objExcel.Cells(intRow,2).value = easydate 
    'populated disabled column 
    'if IsAccountDisabled("LSMASTER",strUser) Then 
     objExcel.Cells(intRow,3).value = "DISABLED"' End if 
    intRow = intRow + 1 
    End If 
    'sort by login Date 
    Dim obrange, obrange2 
    Set obrange = objExcel.Range("A:K") 
    Set obrange2 = objExcel.Range("B2") 
    obrange.Sort obrange2,1,,,,,,1 
    ' Clean up. 
    Set objRootDSE = Nothing 
    Set objConnection = Nothing 
    Set objCommand = Nothing 
    Set objRecordSet = Nothing 
    Set objDC = Nothing 
    Set objDate = Nothing 
    Set objList = Nothing 
    Set objShell = Nothing 
    Set obrange = Nothing 
    Set obrange2 = Nothing 
    Set objExcel = Nothing 

    Good luck!

    "Education is not the filling of a pail, but the lighting of a fire." W. B. Yeats


    • #3
      Re: Active Directory Query Scripting in MS Access

      Randerso - Thanks for your advice however my issue would be quite the reverse. In my situation, the users would have already been removed from AD, just not from my access database. Therefore I would have a user in access and I would need a script to take the users compare them with AD. If there is a "null" value then it should flag a column in Access for deletion.


      • #4
        Re: Active Directory Query Scripting in MS Access

        Hmm,then if I just needed to get this done, again not an elegant solution, run a dsquery against AD, pipe the results out to an excel spreadsheet.
        From the results of the dsquery, select a column that contains a unique attribute common to your access database (sAMAccountName would be good) and do a vlookup in excel to compare values between the two columns. The results of the vlookup should give you the null reference for the user account to be deleted from your Access database

        "Education is not the filling of a pail, but the lighting of a fire." W. B. Yeats