Solved

Query ActiveDirect for username from computer name

Posted on 2013-02-04
19
1,633 Views
Last Modified: 2013-04-02
I need to get the network/"logged on" username from Active Directory, using the computer name that a user is logged on with. Would like to use VB/VBA or VBScript to do this.
The scenario is:
I can get the computer name for users that have a Microsoft Access database file open by examining the lockfile associated with it. But I can't get the username so that I can contact them to ask them to close the database so I can update the file. I've tried using the WMI Service object but it's not working, apparently, due to a permissions issue (it returns the error, "permission denied"). If I have the username, I can contact the user from our Outlook directory.
0
Comment
Question by:Concentus
  • 7
  • 5
  • 4
  • +2
19 Comments
 
LVL 21

Expert Comment

by:Joseph Moody
ID: 38850864
Set this script up as a logon script:

Set objSysInfo = CreateObject("ADSystemInfo") 

On Error Resume Next
Set objComputer = GetObject("LDAP://" & objSysInfo.ComputerName) 
 
objComputer.Put "managedBy", objSysInfo.Username 
objComputer.SetInfo

Open in new window


Next, give authenticated users the write permission to a computer's description.

Then you can use this power shell script to query the logged on user. You will need the Quest AD Cmdlets.

Add-PSSnapin Quest.ActiveRoles.ADManagement

$User= read-host "What is the firstname,last name, or Username of the user?"
Get-QADComputer -ManagedBy (Get-QADUser $User) | Format-Table Name,Managedby
pause
0
 

Author Comment

by:Concentus
ID: 38850949
Jmoody10,
Thanks for your response. Do you mean to set this code to run when users log on to the network? If so, I don't have the permissions to make that work. I have no network or ActiveDirectory permissions beyond standard users.
I hoping to find code similar to this:
    Dim cnn As Object 'ADODB.Connection
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Provider = "ADsDSOObject"
    cnn.Open "Active Directory Provider"
    'Create the query
    Dim strSQL As String
    strSQL = "SELECT distinguishedName" & _
             " FROM 'LDAP://" & strDomain & "'" & _
             " WHERE objectCategory='user'" & _
                   " AND samAccountName = '" & strUsername & "'"
    'Get the data and return the result
    Dim rst As Object 'ADODB.Recordset
    Set rst = cnn.Execute(strSQL, , 1)
    If Not rst.EOF Then _
        GetActiveDirectoryFullName = Split(Mid(rst.Fields("distinguishedName"), Len("CN=") + 1), ",")(0)

Open in new window

   
... except it queries the distinguishedName from Active Directory by passing the username (and domain). But username is the missing information for me. I'm trying to find the logged on username based on the computer name.
0
 
LVL 21

Expert Comment

by:Joseph Moody
ID: 38850971
Are you an admin of the remote machines?
0
 
LVL 57
ID: 38850995
Give the code below a shot.  I had it sitting to try it out, but never got around to it.  Not sure if it works or not.  If it doesn't, let me know and I'll play with it.

Jim.


Function getLDAPName(clockNumber As String)

     'Declare Variables
     Dim objAdoCon, objAdoCmd, objAdoRS
     Dim objUser, objRootDSE
     Dim strDomainDN, strUserFullName
     Dim intAnswer As Integer

    On Error GoTo Err_NoNetwork
     ' Get current logged in user name
     'strUserFullName = Environ("UserName")

     ' Get the DN of the user's domain
     Set objRootDSE = GetObject("LDAP://rootDSE")
     strDomainDN = objRootDSE.Get("defaultNamingContext")
     
     ' Search the domain for the user's account object
     Set objAdoCon = CreateObject("ADODB.Connection")

     objAdoCon.Open "Provider=ADsDSOObject;"
     Set objAdoCmd = CreateObject("ADODB.Command")
     Set objAdoCmd.ActiveConnection = objAdoCon

     objAdoCmd.CommandText = _       "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _       "objectCategory='person' AND objectClass='user' AND " & _       "sAMAccountName='" & clockNumber & "'"          
     Set objAdoRS = objAdoCmd.Execute

     ' If found, get the displayName attribute.
     If (Not objAdoRS.EOF) Then
       Set objUser = GetObject(objAdoRS.Fields("ADsPath").Value)

       'Get common name
       objUser.GetInfoEx Array("CN"), 0
       commonName = objUser.Get("CN")

       'get first name
       objUser.GetInfoEx Array("givenName"), 0
       firstName = objUser.Get("givenName")

       'get last name
       objUser.GetInfoEx Array("SN"), 0
       lastName = objUser.Get("SN")

       'get display name
       objUser.GetInfoEx Array("DisplayName"), 0
       DisplayName = objUser.Get("DisplayName")

       getLDAPName = commonName
     Else
      ' handle "not found" error here
      GoTo Err_NoNetwork
     End If        


Exit_Sub:
     On Error resume next

     Set objUser = Nothing

     Set objAdoRS = Nothing
 
     Set objAdoCmd = Nothing

     objAdoCon.Close

     Set objAdoCon = Nothing

     Set objRootDSE = Nothing

     Set WshNetwork = Nothing

     Exit Function        

Err_NoNetwork:
      getLDAPName = "Error"
      GoTo Exit_Sub

 End Function
0
 

Author Comment

by:Concentus
ID: 38851091
@JMoody10, I don't have admin privileges on any PCs. Even if I log in locally, directly on the PC itself, using my login name assigned by our IT department, I only have standard, limited user permissions. Only IT technician users have privileges like admin. I'm only a database admin - it's a big company.

@JDettman,
I tried the code. With MS Access Option Explicit settings I had to declare some variables. My best guess was:
    Dim WshNetwork As Object
    Dim commonName As Variant
    Dim firstName As Variant
    Dim lastName As Variant
    Dim DisplayName As Variant

For clockNumber, I passed the name of a PC. I tried once with someone elses PC and once with mine.
The result said "Error".
This was base on an empty recordset, apparently.
After executing the query from this SQL:      
objAdoCmd.CommandText = _
     "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _
     "objectCategory='person' AND objectClass='user' AND " & _
     "sAMAccountName='" & clockNumber & "'"
On executing this line
If (Not objAdoRS.EOF) Then
 
... it executed:
    Else
      ' handle "not found" error here
      GoTo Err_NoNetwork
... so it apparently found no records from the SQL statement.

Maybe because I don't have the clockNumber parameter right? For clockNumber, I plugged in the computer name. (That's the only piece of data I have about who is using the file).
0
 
LVL 57
ID: 38851161
Would be the users signon:

sAMAccountName='" & clockNumber & "'

fot testing, change this line at the top:

'strUserFullName = Environ("UserName")

to

clockNumber = Envrion("UserName")

 If that works, then I'll adjust the code to use an API rather then a call to Envrion()  (which can be easily spoofed).

Jim.
0
 

Author Comment

by:Concentus
ID: 38851258
Jim,

I think we're close. I'm trying to get the username from the computer name. I don't have the username. Environ("UserName") only works for the user that's logged on - I won't have that because I'm using the MS Access Lockfile (it holds the computer name but not the win/network logged on username).
In that SQL string, instead of using the SAMAccountName in the Where clause, is there a way to use the Computer Name and get the SAMAccountName from it?
The code might look something like this:
    objAdoCmd.CommandText = _
     "SELECT ADsPath FROM 'LDAP://" & strDomainDN & "' WHERE " & _
     "objectCategory='person' AND objectClass='user' AND " & _
     "COMPUTER_NAME_FIELD='" & THE_COMPUTER_NAME_PASSED_AS_PARAMETER & "'"

Thanks for looking at this. I think we're getting close.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38851262
The thing is that Active Directory contains data pertaining to the user -- their name, username, email address, and a variety of other data the focus on a person and the permissions they have on your network.  Plus, AD allows you to group those people into groups that share the same permissions.

Active Directory does not maintain the information as to which computer(s) you are logged on to.  With that in mind, querying Active Directory for the information you seek is not going to yeild the results you want.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 38851342
<<I'm trying to get the username from the computer name. I don't have the username. Environ("UserName") only works for the user that's logged on - I won't have that because I'm using the MS Access Lockfile (it holds the computer name but not the win/network logged on username). >>

 That's a problem then.  I know your question stated that, but I assumed you would be able to get the user name.

 Windows does keep track of who is using a share (both user and computer name), so it should be possible to pull the list, but that's not going to be through AD.

 I'll see if I can't think of another way...

Jim.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Concentus
ID: 38851513
Okay. Thanks. I believe I understand. There's a user account, computer account and a group account in Active Directory. But correlating users to the computer they are logged on with is possibly a customization that Network administrators might implement? It's a dynamic, potentially constantly changing many-to-many look-up table where a user can be logged on to more than one PC and a PC can have more than one users.
This is, maybe, how something like the script Jmoody10 posted could be used. The script would have to be run from each PC whenever a user logs on. By default, it sounds like, Active Directory doesn't store this.
Typically, WMI Service can obtain this information, but something on our network is blocking it.
If anyone has any ideas how I can get the username by using the computer name without calling the WMI Service object, I'd appreciate the help.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38851693
You can try to use the API call of NetWkstaUserEnum.  I used to use that before I switched to WMI.  I could not find an example of my previous usage, but after a Bing search on the API call, this particular link seemed decent ...

http://www.andreavb.com/tip060006.html
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38851712
Here is another sample from a previous EE thread ...

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_20923841.html#a10658333

It may be a better sample since it is not based in a Class module.

That particular thread references another site as well that has tons of API calls for network information, including getting the remote user name ...

http://vbnet.mvps.org/index.html?code/network
0
 

Author Comment

by:Concentus
ID: 38851914
Thanks datAdrenaline,

On first effort (running previous EE thread), NetWkstaUserEnum returned a value of 5, which indicated an error. CopyMemory couldn't populate uinfo.

I will look at it some more, but not working yet.
0
 
LVL 35

Expert Comment

by:Nick Sui
ID: 38854220
Or esiest way to use Dynamic SpotAction Tool.
0
 

Author Comment

by:Concentus
ID: 38871355
Thanks for everybody's help. I'm still trying to solve this using everyone's responses. Might take a few more days before I can get through it. I'm using a temporary workaround until I can implement a better solution - I'm appending the username and PC name into a table when they open the database, then getting the PC name from the .laccdb lockfile and showing the username that was associated with it in my table.
0
 
LVL 57
ID: 38873547
Well if your willing to do that, just call the windows API for the User and computer name.

call WhoAmI(True) to get the username or WhoAmI(False) to get the station name.

Paste the below into a module.  Declares go at the top of the module.

Jim.

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 250 total points
ID: 38876928
With the proliferation of 64bit installations, I personally avoid API calls if at all possible.  So to get the local user name and computer name I suggest using something like this following:

Public Function WhoAmI(bReturnUserName As Boolean) As String

    Dim strUserName As String
    'Dim strUserDomain As String
    Dim strComputerName As String
    
    'Set the properties
    With CreateObject("wscript.network")
        strUserName = .UserName
        'strUserDomain = .UserDomain
        strComputerName = .ComputerName
    End With
    
    WhoAmI = IIf(bReturnUserName, strUserName, strComputerName)
    
End Function

Open in new window

0
 

Author Closing Comment

by:Concentus
ID: 38905582
Thanks for everybody's help. I was not able to find a way to get the username via computer name through Active Directory, WMI Service, or NetWkstaUserEnum. As noted by JDettman and DatAdrenaline, Active Directory does not contain this information (at least, not by default configuration). WMI Service appears to be configured to block access by non-admin users. NetWkstaUserEnum returned an error, not sure why (suspect it does not work on our current network/PC OS).

I had to use a work-around to solve this problem. I'm appending the username and PC name (using scripts posted by DatAdrenaline and JDettman) into a table when they open the database, then getting the PC name from the .laccdb lockfile and showing the username that was associated with it in my table.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 38905618
Thanks for the followup!  That technique you are employing is a common one.  So, while it may not be exactly what you want, it is a fairly reliable and widespread.

Good luck on your project!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Resolve DNS query failed errors for Exchange
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now