How to get the IP address and machine name of the client currently connected via T-SQL

How to get the IP address and machine name of the client currently connected via T-SQL
/*************************************** **************************************** **********************************

The following SP returns the IP addresses and hostnames of all clients. The objective is to return the connection status of the client at a certain time point through the job.

I wrote this script at the time to often have some unauthorized clients that connect to sqlserver through the sqlserver client, so I can define a job to run this stored procedure every 30 minutes, and write the content into a log file. This will probably record which clients are connected to sqlserver. Of course, you can modify this script to return more information, such as CPU, memory, lock ....

Author: Bright Summit of Huangshan

Mail: leimin@jxfw.com

Version: 1.0.0

Date: 2004-1-30

(If You Need To reprint it, please indicate the source !)

**************************************** **************************************** *************************/

Create proc usp_getclient_infor
As
Set nocount on

Declare @ RC int
Declare @ rowcount int

Select @ rc = 0
Select @ rowcount = 0

Begin
-- // Create temp table, save sp_who Information
Create Table # tspid (
Spid int null,
ECID int null,
Status nchar (60) null,
Loginname nchar (256) null,
Hostname nchar (256) null,
BLK bit null,
Dbname nchar (256) null,
CMD nchar (32)
)

-- // Create temp table save all SQL client IP and hostname and login time
Create Table # userip (
[ID] int identity (1, 1 ),
TXT varchar (1000 ),
)

-- // Create result table to return recordset
Create Table # result (
[ID] int identity (1, 1 ),
Clientip varchar (1000 ),
Hostname nchar (256 ),
Login_time datetime default (getdate ())

)
-- // Get host name by Exec sp_who, insert # tspid from sp_who,
Insert into # tspid (spid, ECID, status, loginname, hostname, BLK, dbname, CMD) exec sp_who

Declare @ brief STR varchar (100 ),
@ Hostname nchar (256 ),
@ Userip varchar (20 ),
@ Sendstr varchar (100)
 

-- // Declare a cursor from Table # tspid
Declare tspid cursor
For select distinct hostname from # tspid with (nolock) Where spid> 50
For read only

Open tspid
Fetch next from tspid into @ hostname
While @ fetch_status = 0
Begin
Select @ brief STR = 'ping' + rtrim (@ hostname)
 
Insert into # userip (txt) exec master... xp_mongoshell @ brief Str
 
Select @ rowcount = count (ID) from # userip

If @ rowcount = 2 -- // No IP feedback package
Begin
Insert into # result (clientip, hostname) values ('can not get feedback package from Ping! ', @ Hostname)
End
If @ rowcount> 2
Begin
Select @ userip = substring (txt, charindex ('[', txt) + 1, charindex (']', txt)-charindex ('[', txt)-1)
From # userip
Where TXT like 'pinging %'

Insert into # result (clientip, hostname) values (@ userip, @ hostname)
End
Select @ RC =error
If @ rc = 0
Truncate table # userip -- // clear # userip table

Fetch next from tspid into @ hostname
End

Close tspid
Deallocate tspid

Select * from # result with (nolock)

Drop table # tspid
Drop table # userip
Drop table # result
End
Go
Exec usp_getclient_infor