/*****************************************************************************************************************
下面的sp是返回所有的客戶端的ip和hostname,目的是可以通過job返回某一時間點的client 的連接情況.
我當時寫這個腳本的目的是經常有一些沒有授權的客戶機,通過sqlserver的client就連接到sqlserver,所以我可以定義一個job每隔30分鐘運行一次這個存儲過程,并且將內容寫的一個log文件,這樣可以大概記錄有哪些client在連接sqlserver,當然大家可以可以修改這個腳本,使之返回更多的信息,比如cpu,memory,lock....
author:黃山光明頂
mail:[email protected]
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 @[email protected]@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
國內最大的酷站演示中心!
