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
