Solved

how to retrieve network name

Posted on 2014-02-12
30
395 Views
Last Modified: 2014-02-25
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
Comment
Question by:bfuchs
  • 15
  • 5
  • 4
  • +3
30 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 150 total points
ID: 39854848
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39854877
Hi chaau, I see today is your turn:)
Will test it with a few users pc's & let you know.
0
 
LVL 75
ID: 39854890
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39854927
@ 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
 
LVL 75
ID: 39854946
"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
 
LVL 3

Author Comment

by:bfuchs
ID: 39854960
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
 
LVL 75
ID: 39854973
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39855188
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 39857885
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39863307
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39863791
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39865982
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39866249
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39866437
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39869157
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39869248
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39869352
I am afraid I have not used MS Access in over 15 years, so I cannot help you there.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39871566
@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
 
LVL 3

Author Comment

by:bfuchs
ID: 39871705
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
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
ID: 39878822
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
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 250 total points
ID: 39878958
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39883733
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39883988
Isn't that just the computername as in chaau's first post?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39884122
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39886788
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39886866
OK, I guess will have to do that.
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39886894
Thanks to all participants!!
0
 
LVL 75
ID: 39886910
!
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39887085
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

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

Being a system administrator some time we require to do things remotely, one of them is installing software. Here I am going to tell you how to install software through wmic (Windows management instrument console). I am not at all saying that this i…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

16 Experts available now in Live!

Get 1:1 Help Now