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

小文05

　　/****************************************** 　　 　　下面的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 　　 <