Last Logon Via Active Directory Viewable from MSAccess

I have a user database in MSAccess.  I also have a VBS script that produces an Excel spreadsheet that lists last logons for users from Active Directory.  I would like to know how to get last logon information from Active Directory to appear on MSAccess form for any user I query on from my user database.  Basically I would like to pull up a user and there would be a text box indicating whether account needs to be disabled for inactivity - say a 60 day threash hold.  I am not proficient enough to make my VBS script to work in MSAccess VBA.  
oakvickAsked:
Who is Participating?
 
Nico BontenbalCommented:
You can try this function to return the last login date for a user
Public Function UserLastLogin(LoginName As String) As Date

Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim oRoot As Object
Dim oDomain As Object
Dim sBase As String
Dim sFilter As String
Dim sDomain As String

Dim sAttribs As String
Dim sDepth As String
Dim sQuery As String
Dim sAns As String

Dim user As Object

On Error GoTo ErrHandler:


Set oRoot = GetObject("LDAP://rootDSE")

sDomain = oRoot.Get("defaultNamingContext")
Set oDomain = GetObject("LDAP://" & sDomain)
sBase = "<" & oDomain.ADsPath & ">"

sFilter = "(&(objectCategory=person)(objectClass=user)(name=" _
  & LoginName & "))"
sAttribs = "adsPath"
sDepth = "subTree"

sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
                   
conn.Open _
  "Data Source=Active Directory Provider;Provider=ADsDSOObject"
  
Set rs = conn.Execute(sQuery)

If Not rs.EOF Then
    Set user = GetObject(rs("adsPath"))
    
    UserLastLogin user.LastLogin
End If

ErrHandler:

On Error Resume Next
If Not rs Is Nothing Then
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
End If

If Not conn Is Nothing Then
    If conn.State <> 0 Then conn.Close
    Set conn = Nothing
End If

Set oRoot = Nothing
Set oDomain = Nothing
End Function

Open in new window


0
 
Michael KnightCommented:
Do you have to have Access involved? If not, why not just use the tools that come with 2k3?

dsquery user -limit 0 - inactive 8      <--number of weeks
0
 
oakvickAuthor Commented:
Nicobo,

I placed the function in a textbox, i.e. "=UserLastLogin([name])", where name is the user name in my user database.  What convention does your function put the Active Directory user name?  I imagine that the name is my user db must match the convention of your function.  Or, how do I properly use your function?  Can I test it in the immediate window?

Thank you for your help!  Joe
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
oakvickAuthor Commented:
I get "00:00:00" when I enter "?userlastlogin(name)" via the immediate window.
0
 
oakvickAuthor Commented:
Okay.  I can get lastlogin from immediate window now by entering this, "?userlastlogin("smith, john")."  I created a textbox with the default value of "=userlastlogin([name])" where name is the textbox with the user name in it.  It returns "00:00:00".  I hard code "smith, john" and it returns the last login date.  How do I pass contents from name textbox to your function?

Joe
0
 
oakvickAuthor Commented:
I changed the "name" textbox name to "username".  Placed it into the control source property and it now works!  Cool beans!  

Thank you Nicobo!  Joe
0
 
Nico BontenbalCommented:
Thats strange. Are you absolutely sure the [name] field contains exactly the text "smith, john" or any other name that you can find when you use the immediate window?
Change the beginning of the function to:
Public Function UserLastLogin(LoginName As String) As Date
debug.print "|" & LoginName & "|" & len(LoginName)
Dim conn As New ADODB.Connection

Open in new window


Now run the function from the immediate window and then from the form. Then check the immediate window if the name is correct. Check for double or leading or trailing spaces and things like that. The length is added after the second | so you can check if there are any hidden characters.
0
 
Nico BontenbalCommented:
There was an error in the original script
UserLastLogin user.LastLogin

Open in new window

should be
UserLastLogin = user.LastLogin

Open in new window

But I suppose you found that yourself otherwise it would not work in the immediate window.
0
 
Nico BontenbalCommented:
You can change the line:
sFilter = "(&(objectCategory=person)(objectClass=user)(name=" _
  & LoginName & "))"

Open in new window

to
sFilter = "(&(objectCategory=person)(objectClass=user)(samAccountName=" _
  & LoginName & "))"

Open in new window

To find the user based on the login name, not the full name.
0
 
Nico BontenbalCommented:
Sorry things got a bit mixed up because you posted things while I was preparing my posts.
0
 
oakvickAuthor Commented:
Yeah.  I corrected that earlier.  Great work bro!  It works but I think I need to adjust now for multiple domain controllers.  I have one user who logged on recently but it is showing an old login date.

Thanks again, Joe
0
 
Nico BontenbalCommented:
That's strange. You can open "Active Directory - Users and Computers" and then select View, Advanced Features. In the property window of a user select the "Attribute Editor" tab, and find the lastLogon value. You can compare this value to the value returned by the script.
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.