• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1835
  • Last Modified:

Query ActiveDirect for username from computer name

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
Concentus
Asked:
Concentus
  • 7
  • 5
  • 4
  • +2
2 Solutions
 
Joseph MoodyBlogger and wearer of all hats.Commented:
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
 
ConcentusAuthor Commented:
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
 
Joseph MoodyBlogger and wearer of all hats.Commented:
Are you an admin of the remote machines?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
ConcentusAuthor Commented:
@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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
ConcentusAuthor Commented:
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
 
datAdrenalineCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
ConcentusAuthor Commented:
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
 
datAdrenalineCommented:
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
 
datAdrenalineCommented:
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
 
ConcentusAuthor Commented:
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
 
Nirmal SharmaSolution ArchitectCommented:
Or esiest way to use Dynamic SpotAction Tool.
0
 
ConcentusAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
datAdrenalineCommented:
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
 
ConcentusAuthor Commented:
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
 
datAdrenalineCommented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now