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

PappySki

New Member
Joined
May 12, 2009
Messages
12
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,711
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
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 = FSo_Opentextfile("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

New Member
Joined
May 12, 2009
Messages
12
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,711
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
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

New Member
Joined
May 12, 2009
Messages
12
Thanks. I look forward to trying it out.
 

PappySki

New Member
Joined
May 12, 2009
Messages
12
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,711
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
what network do you have
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,711
Office Version
2016, 2013, 2011, 2010, 2007
Platform
Windows
can you resolve the ping or tracert to an actual computer name, and do you have elevated privs on the network
 

PappySki

New Member
Joined
May 12, 2009
Messages
12
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.
 

Forum statistics

Threads
1,084,819
Messages
5,380,090
Members
401,645
Latest member
Me22

Some videos you may like

This Week's Hot Topics

Top