Code to find out if anyone is logged into a computer on the network

[PappySki]

Greetings,

I've got a macro that references a list of IP Addresses that starts in A2 and then pings each address to provide a status for each IP Address. I would like to add a macro that would check each IP Address and let me know if anyone is currently logged into the machine. It would be nice to know who is logged in, but this isn't necessary.

Thanks

PappySki

 

[jimrward]

this is some code I have posted before, pass it a computer name or IP address, set it up as a user defined function and reference it in a cell

Public Function GetUserName(HostName As String)
'
' Returns the Username from the HostName for the computer.
'
' Author Unknown
' Mods Jim Ward, 25 March 2008
' Original version was to return the MAC address from the output text from
' a shell of nbtstat.
' This version uses a more elegent method to extract out the <03> record as a
' single line to the new output file, file is then read in first part extracted
' and file deleted.
'
' This is slow by nature as in a couple of seconds to run it for each hostname
' so if you have many to find and copy the function down be warned as it will
' not give instant results.

'
Dim SH As Object
Dim FSO As Object
Dim TS As Object
Dim Data As String
Dim UserName As String
'
On Error GoTo ErrorHandler

'
'****
' create a scripting shell object, and run our command through it
' if it is an IP address use -A, else for computer name use -a
'****
'
Set SH = CreateObject("wscript.shell")
If InStr(HostName, ".") Then
SH.Run "%comspec% /c nbtstat -A " _
& HostName & " | FIND ""<03>"" | FIND /I /V " & Chr(34) & HostName & Chr(34) & " > c:\nbuser.txt", 0, True
Else
SH.Run "%comspec% /c nbtstat -a " _
& HostName & " | FIND ""<03>"" | FIND /I /V " & Chr(34) & HostName & Chr(34) & " > c:\nbuser.txt", 0, True
End If
Set SH = Nothing
'Stop
'
'****
' open up the results file, should have a single line, extract the username
'****
'
Set FSO = CreateObject("scripting.filesystemobject")
Set TS = FSpentextfile("c:\nbuser.txt") 'text stream
UserName = ""
Do While Not TS.AtEndOfStream
Data = UCase(Trim(TS.readline))
UserName = Left(Data, InStr(Data, "<") - 1)
Loop
'
'****
' tidy up before we exit
'****
'
TS.Close
Set TS = Nothing
FSO.deletefile "c:\nbuser.txt"
Set FSO = Nothing
'
GetUserName = UserName
Exit Function
'
ErrorHandler:
GetUserName = "Error in GetUserName function"
End Function

 

[PappySki]

I don't see where this is pulling the IPs from or writing the username. I want it to check the IPs in column A starting in row 2 on Sheet1. I would like it to write the user names in column C starting in row 2, also on Sheet1.

 

[jimrward]

you will have to open the VBA editor, insert a module and paste in my code

you will then in column C or whereever, type =GETUSERNAME(A2) or wherever your data starts

 

[PappySki]

Thanks. I look forward to trying it out.

 

[PappySki]

Unfortunately, this doesn't work for me. Turns out nbtstat relies on Netbios which we don't have on our network. Any other ideas?

 

[jimrward]

what network do you have

 

[jimrward]

can you resolve the ping or tracert to an actual computer name, and do you have elevated privs on the network

 

[PappySki]

I've got the privileges. I can run nbtstat -A against an IP and get a NetBIOS table. But the table only includes:

ComputerName <00> Unique Registered
DomainName <00> Group Registered
ComputerName <20> Unique Registered

MAC Address

That's all.