[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

how to retrieve network name

hi all,

i have constantly people posing a lock on a file on the network, without me knowing who this user is, when i go to the server under computer/manage/shared folder/open files/, i get the network names, however i still don't know who they are..

the same is when i want to look up at sql server/activity monitor, to see which user is running a statement that is blocking other transactions, there is a column host that shows me a user name, but the same issue as above...

therefore, since we use an access db linked to sql, and each user must enter their initial, before opening any form on the app, i would like to have a function that retrieves both, the network name as well as the host name displayed in sql monitor, and would save them in the initials table.
0
bfuchs
Asked:
bfuchs
  • 15
  • 5
  • 4
  • +3
3 Solutions
 
chaauCommented:
You can retrieve user Name, domain name and Computer name using Environ function:
Environ("username")

Open in new window

If domain is also required, you can use:
Environ("domainname") & "\" & Environ("username")

Open in new window

Computer name:
Environ("computername")

Open in new window

0
 
bfuchsAuthor Commented:
Hi chaau, I see today is your turn:)
Will test it with a few users pc's & let you know.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Environ ("UserName") is going to return the logged in Windows user name on a given system, not who is in a backend database.

mx
0
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.

 
bfuchsAuthor Commented:
@ DatabaseMX,
When tested in my pc, those answers from chaau seem to work fine
Environ("username") would be used for file locked in the server
Environ("computername") would be used for sql back end host name
do you have anything opposing it?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Environ("username") would be used for file locked in the server"
 Well,  Environ ("UserName") has nothing to do with a file locked on a server. If you log into a Windows PC - like your own desktop or laptop, Environ ("username") is going to return your Windows user name.
Similarly - Environ("ComputerName") is going to return the DeviceID (aka Computer Name) of your system. It's has nothing to do with the 'SQL back end host name"

Environ right now on my laptop
0
 
bfuchsAuthor Commented:
1- ok just tested with another pc, Joe is right with that, "username" did not match up with the name displayed on the windows server manager.
2- while "computername" variable did match with what i saw on sql server activity monitor (in two pc's tested so far).
3- any other suggestion?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I'm not familiar with the SQL Server activity monitor, but I suspect it would show the device ID connected. Unfortunately, that does not tell you who (person/user) is connected.  
Now in a similar vain, I have a table in a config database that related a DeviceID it's physical user. So, when I pull DeviceIDs from and Access LDB or ACCDB file - I can tell who that user really is.

There is a way to get the actual Windows user name from a device ID. However, whether it works or not depends on what specific access rights an IT dept has given 'you' in a particular 'domain' (for lack of a better word). Here at my work, unfortunately ... that code does not work - only returns error indicating I do not have permission ...
mx
0
 
bfuchsAuthor Commented:
hi,

I think will do more testing tom in regards to sql server, as if deviceid = host name and host name = environ("computername"), thats fine for me. as mention in original post, i have a way to link that to actual user names.

the only thing i am stuck so far is the windows name on the server, as you stated above, and i proved with testing, "username" does not return the desired results.

In order to clarify, i am attaching a screenshot of what exactly i am looking for.
you will see there a column named accessed by, and that is the missing point here..
Doc2.doc
0
 
Anthony PerkinsCommented:
I would suggest you download and install Adam Machanic's script sp_whoisactive from here:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
0
 
bfuchsAuthor Commented:
Hi Anthony,

some questions,

1- this looks like solution for the sql part, so far the environ ("computername") worked for the sql issue, need to test this little more this week tough, what i am looking now is for the windows file locked by column.

2- what is this script better than the sql activity monitor? how will i get the name of the user, or how can i call this from my access vba application?

3- just tried running it against a db that i was hooked up there, and this script didn't returned any values, does it only return if they are some transactions blocking?
0
 
Anthony PerkinsCommented:
2. I would suggest you read up on it and test it out.
3. It reports any activity that is currently occurring,  for history you will have to log the results to a table or file.
0
 
bfuchsAuthor Commented:
Hi Anthony,

below is just what i got when running this script, i did some reading, still no idea how this answers those questions above, and how will this resolve the question posted?


dd hh:mm:ss.mss      session_id      sql_text      login_name      wait_info      CPU      tempdb_allocations      tempdb_current      blocking_session_id      reads      writes      physical_reads      used_memory      status      open_tran_count      percent_complete      host_name      database_name      program_name      start_time      login_time      request_id      collection_time
00 00:00:00.126      121      <?query --
SELECT  *  FROM "dbo"."view_EmployeeExperience" WHERE ((@P1 = "EmployeeID"))
--?>      sa      NULL                       94                        0                        0      NULL                    1,120                        0                        0                   10,391      runnable                        0      NULL      USER64-PC      PlacementNP      Microsoft Office 2003      2014-02-17 16:58:56.400      2014-02-17 09:17:50.340      0      2014-02-17 16:58:56.607
0
 
Anthony PerkinsCommented:
well, it tells you what is running by who, since when, and if it is blocking (or being blocked by )  anyone.

In addition, it shows how many reads, writes, CPU and memory usage, open transactions, the originating computer, database and application name,

If all of this does not answer your question, perhaps you should invest in some of those third party tools such as the ones from IDERA, SQL Sentry and Confio.
0
 
bfuchsAuthor Commented:
hi Anthony,

as you can see from the peace i posted, the host name returned is user64-pc, now this is good when i have to deal only with a handful of pc's while here dealing with dozens i will never know who this is...
therefore i am trying to capture this host name in the app when i have access to the users name, to save them in a table.
0
 
Anthony PerkinsCommented:
while here dealing with dozens i will never know who this is...
For sure.  If you want to use that information and the host name is meaningless, then yes you will have to store the information in a cross reference table so that you can join the results from sp_whoisactive with this table.  And you should also know that if you are on a website or they spoof the host name then you will not be able to use that value.
0
 
bfuchsAuthor Commented:
Hi,

then yes you will have to store the information in a cross reference table

Okay, so what do you suggest in order to retrieve the host name at the application level (in my case MS Access)?

Also do you have any suggestion on how to retrieve the windows locked file (Attached screenshot above on post id 39855188)?
0
 
Anthony PerkinsCommented:
I am afraid I have not used MS Access in over 15 years, so I cannot help you there.
0
 
bfuchsAuthor Commented:
@Anthony,
Thanks for your efforts, I really appreciate them.

@chaau,
so far your environ("computername") suggestion works well for office 2000 mdb, however in office2003 ADP the hostname is not really accurate, its showing the pc name that the project was developed in for all users.
What i do see there in the activity manager is a column named "Net Address", which sows a combination of numbers and letters (12 in total), this seems to be more accurate, how can i retrieve that info?
0
 
bfuchsAuthor Commented:
Hi,
I know there is a way to get this info by running the following DOS command
IPCONFIG /ALL, and under physical address i see it.
Just wonder how can i get this using vba?
0
 
David Johnson, CD, MVPOwnerCommented:
Create a Share on a Server and give right NTFS and Share permissions
    Create a Batch file in which you put this script:
echo user: %username% computer: %computername% date: %date% > \\Server\Share\info.txt

Open in new window

   Use group policies to apply this Batch file as a logon script

At the end you will get in the text file info.txt:

    The user name
    The used computer
    The date of logon

 http://social.technet.microsoft.com/Forums/windowsserver/en-US/01eaf9dd-ac07-45bc-8d01-9fc85ba9db9f/find-computer-name-user-is-logged-onto?forum=winserverDS
0
 
andrewssd3Commented:
From your description of Net address, it sounds like you are looking for the MAC address of the user's network adapter. You can retrieve this value using WMI scripting:
Public Function getMACAddress(Optional ByVal strComputer As String = ".") As Variant

    Dim objWMIService As Object
    Dim colItems As Object
    Dim objItem As Object
    Dim vResults As Variant
    Dim i As Long

    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colItems = objWMIService.ExecQuery _
        ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
    
    vResults = Empty
    
    If Not colItems Is Nothing Then
        If colItems.Count > 0 Then
            ReDim vResults(colItems.Count - 1)
            i = 0
            For Each objItem In colItems
                vResults(i) = objItem.MACAddress
                i = i + 1
            Next objItem
        End If
    End If
    
    getMACAddress = vResults
    
End Function

Open in new window

This returns an array of MAC addresses (there may be more than one if the machine has multiple network adatpers), so you may want to store more than one for each user if applicable. You coulld call it using something like this:
Public Sub ShowMAC()

    Dim vMacs As Variant
    Dim vMACAddr As Variant
    
    vMacs = getMACAddress
    
    If Not IsEmpty(vMacs) Then
        For Each vMACAddr In vMacs
            Debug.Print vMACAddr
        Next vMACAddr
    End If
    
End Sub

Open in new window

Of course you could also put this sort of code into a login script as David Johnson suggests
0
 
bfuchsAuthor Commented:
Hi thanks for your replies,

@andrewssd3
It looks like your suggestion works well for the mac address.

@ David Johnson,
I tried first on my pc, and didn't see this info matching either the mac address nor the windows accessed by column.

@All,
there is one more thing still missing here, how do I get the name windows displays on the computer management under column accessed by? see attached screenshot above (ID:39855188)
0
 
andrewssd3Commented:
Isn't that just the computername as in chaau's first post?
0
 
bfuchsAuthor Commented:
no, the computername of chaau's post did return what it shows in sql host_name column, however as described above this did not work well for computers using office version 2003, were all host names were showing the same, taking it from the pc used to deploy the application.
0
 
andrewssd3Commented:
I'm afraid I don't know what username that is returning. I have tested on my Win7 homegroup machines and I see HomeGroupUser$ in that field, rather than the logged on Windows user.  This is not my area of expertise - is it worth trying to pose that as a separate question to the Windows Networking community?
0
 
bfuchsAuthor Commented:
OK, I guess will have to do that.
0
 
bfuchsAuthor Commented:
Thanks to all participants!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
!
0
 
bfuchsAuthor Commented:
Hi Joe,

While appreciating all experts efforts, I just tought its fair to designate the points to those posts that provided an accurate answer that I may end up using to solve at least one of the questions posted...

However if you do have a workable solution to the unanswered question, I will ask the moderator to reopen so I can make ajustments.

Thanks again,
Ben
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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