Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Query ActiveDirect for username from computer name

Posted on 2013-02-04
19
1,676 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 22

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 22

Expert Comment

by:Joseph Moody
ID: 38850971
Are you an admin of the remote machines?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 

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:Nirmal Sharma
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
This article runs through the process of deploying a single EXE application selectively to a group of user.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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