通过T-SQL获得连接客户端的IP和机器名

ehxz

/******************************************
　　
　　下面的SP是返回所有的客户端的IP和HOSTNAME,目的是可以通过JOB返回某一时间点的CLIENT 的连接情况.
　　
　　我当时写这个脚本的目的是经常有一些没有授权的客户机,通过SQLSERVER的CLIENT就连接到SQLSERVER,所以我可以定义一个JOB每隔30分钟运行一次这个存储过程,并且将内容写的一个LOG文件,这样可以大概记录有哪些CLIENT在连接SQLSERVER,当然大家可以可以修改这个脚本,使之返回更多的信息,比如CPU,MEMORY,LOCK....
　　
　　Author:黄山光明顶
　　
　　mail:leimin@jxfw.com
　　
　　version:1.0.0
　　
　　date:2004-1-30
　　
　　(如需转载,请注明出处!)
　　
　　*********************************************
　　
　　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 @cmdStr 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 @cmdStr='ping '+rtrim(@hostName)
　　
　　insert into #userip(txt) exec master..xp_cmdshell @cmdStr
　　
　　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