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

    下面的SP是返回所有的客戶端的IP和HOSTNAME,目的是可以通過(guò)JOB返回某一時(shí)間點(diǎn)的CLIENT 的連接情況.

     我當(dāng)時(shí)寫(xiě)這個(gè)腳本的目的是經(jīng)常有一些沒(méi)有授權(quán)的客戶機(jī),通過(guò)SQLSERVER的CLIENT就連接到SQLSERVER,所以我可以定義一個(gè)JOB每隔30分鐘運(yùn)行一次這個(gè)存儲(chǔ)過(guò)程,并且將內(nèi)容寫(xiě)的一個(gè)LOG文件,這樣可以大概記錄有哪些CLIENT在連接SQLSERVER,當(dāng)然大家可以可以修改這個(gè)腳本,使之返回更多的信息,比如CPU,MEMORY,LOCK....

Author:黃山光明頂

mail:leimin@jxfw.com

version:1.0.0

date:2004-1-30

(如需轉(zhuǎn)載,請(qǐng)注明出處!)

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

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