Obtain the client information on sqlserver (Database Name, computer name, user name, Nic physical address, IP address, and program name for the Operation)

Obtain the client information (Database Name, computer name, user name, network interface card physical address, IP address, Program Name)

Create proc p_getlinkinfo
@ Dbname sysname = NULL, -- Name of the database to be queried. The connection information of all databases is queried by default.
@ Brief deip bit = 0 -- whether to display the IP address. This control is added because it is time-consuming to query the IP address.
As
Declare @ dbid int
Set @ dbid = db_id (@ dbname)

Create Table # Tb (ID int identity (128), dbname sysname, hostname nchar (128), loginname nchar (), net_address nchar (12), net_ip nvarchar (15 ), prog_name nchar (128 ))
Insert into # Tb (hostname, dbname, net_address, loginname, prog_name)
Select distinct hostname, db_name (dbid), net_address, loginame, program_name from Master .. sysprocesses
Where hostname <> ''and (@ dbid is null or dbid = @ dbid)

If @ includeip = 0 goto lb_show -- if the IP address is not displayed, it is displayed directly.

Declare @ SQL varchar (500), @ hostname nchar (128), @ ID int
Create Table # IP (hostname nchar (128), a varchar (200 ))
Declare TB cursor local for select distinct hostname from # TB
Open TB
Fetch next from TB into @ hostname
While @ fetch_status = 0
Begin
Set @ SQL = 'ping' + @ hostname + '-a-n 1-l 1'
Insert # IP (a) exec master .. xp_mongoshell @ SQL
Update # IP Set hostname = @ hostname where hostname is null
Fetch next from TB into @ hostname
End

Update # TB set net_ip = left (A, patindex ('%: %', a)-1)
From # TB a inner join (
Select hostname, A = substring (A, patindex ('Ping statistics for %: % ', a) + 20, 20) from # IP
Where a like 'Ping statistics for %: % ') B on A. hostname = B. hostname

Lb_show:
Select ID, database name = dbname, client name = hostname, user name = loginname
, Nic physical address = net_address, IP address = net_ip, application name = prog_name from # TB
Go

// display the connection information of all hosts:
exec p_getlinkinfo
// display the connection information of all hosts, including IP addresses:
exec p_getlinkinfo @ brief deip = 1
// display the information for connecting to the specified database:
exec p_getlinkinfo @ dbname = table name, @ brief deip = 1