通过T-SQL获得连接客户端的IP和机器名
/******************************************<br /> <br /> 下面的SP是返回所有的客户端的IP和HOSTNAME,目的是可以通过JOB返回某一时间点的CLIENT 的连接情况.<br /> <br /> 我当时写这个脚本的目的是经常有一些没有授权的客户机,通过SQLSERVER的CLIENT就连接到SQLSERVER,所以我可以定义一个JOB每隔30分钟运行一次这个存储过程,并且将内容写的一个LOG文件,这样可以大概记录有哪些CLIENT在连接SQLSERVER,当然大家可以可以修改这个脚本,使之返回更多的信息,比如CPU,MEMORY,LOCK....<br /> <br /> Author:黄山光明顶<br /> <br /> mail:leimin@jxfw.com<br /> <br /> version:1.0.0<br /> <br /> date:2004-1-30<br /> <br /> (如需转载,请注明出处!)<br /> <br /> *********************************************<br /> <br /> Create proc usp_getClient_infor<br /> as<br /> set nocount on<br /> <br /> Declare @rc int<br /> Declare @RowCount int<br /> <br /> Select @rc=0<br /> Select @RowCount=0<br /> <br /> begin<br /> --//create temp table ,save sp_who information<br /> create table #tspid(<br /> spid int null,<br /> ecid int null,<br /> status nchar(60) null,<br /> loginname nchar(256) null,<br /> hostname nchar(256) null,<br /> blk bit null,<br /> dbname nchar(256) null,<br /> cmd nchar(32)<br /> )<br /> <br /> --//create temp table save all SQL client IP and hostname and login time<br /> Create table #userip(<br /> int identity(1,1),<br /> txt varchar(1000),<br /> )<br /> <br /> --//Create result table to return recordset<br /> Create table #result(<br /> int identity(1,1),<br /> ClientIP varchar(1000),<br /> hostname nchar(256),<br /> login_time datetime default(getdate())<br /> <br /> )<br /> --//get host name by exec sp_who ,insert #tspid from sp_who,<br /> insert into #tspid(spid,ecid,status,loginname,hostname,blk,dbname,cmd) exec sp_who<br /> <br /> declare @cmdStr varchar(100),<br /> @hostName nchar(256),<br /> @userip varchar(20),<br /> @sendstr varchar(100)<br /> <br /> --//declare a cursor from table #tspid<br /> declare tspid cursor<br /> for select distinct hostname from #tspid with (nolock) where spid>50<br /> for read only<br /> <br /> open tspid<br /> fetch next from tspid into @hostname<br /> While @@FETCH_STATUS = 0<br /> begin<br /> select @cmdStr='ping '+rtrim(@hostName)<br /> <br /> insert into #userip(txt) exec master..xp_cmdshell @cmdStr<br /> <br /> select @rowcount=count(id) from #userIP<br /> <br /> if @RowCount=2 --//no IP feedback package<br /> begin<br /> insert into #Result(ClientIP,hostname) values('Can not get feedback package from Ping!',@hostname)<br /> end<br /> if @RowCount>2<br /> begin<br /> select @userip=substring(txt,charindex('[',txt)+1,charindex(']',txt)-charindex('[',txt)-1)<br /> from #userIP<br /> where txt like 'Pinging%'<br /> <br /> insert into #Result(ClientIP,hostname) values(@userIP,@hostname)<br /> end<br /> select @rc=@@error<br /> if @rc=0<br /> truncate table #userip --//clear #userIP table<br /> <br /> fetch next from tspid into @hostname<br /> end<br /> <br /> close tspid<br /> deallocate tspid<br /> <br /> select * from #result with(nolock)<br /> <br /> drop table #tspid<br /> drop table #userip<br /> drop table #result<br /> end<br /> go<br /> exec usp_getClient_infor<br />
页:
[1]