Create proc usp_getClient_infor
as
set nocount on
Declare @rc int
Declare @RowCount int
Select @rc=
Select @RowCount=
begin
//create temp table save sp_who information
create table #tspid(
spid int null
ecid int null
status nchar() null
loginname nchar() null
hostname nchar() null
blk bit null
dbname nchar() null
cmd nchar()
)
//create temp table save all SQL client IP and hostname and login time
Create table #userip(
[id]int identity()
txt varchar()
)
//Create result table to return recordset
Create table #result(
[id]int identity()
ClientIP varchar()
hostname nchar()
login_time datetime default(getdate())
)
//get host name by exec sp_who insert #tspid from sp_who
insert into #tspid(spidecidstatusloginnamehostnameblkdbnamecmd) exec sp_who
declare @cmdStr varchar()
@hostName nchar()
@userip varchar()
@sendstr varchar()
//declare a cursor from table #tspid
declare tspid cursor
for select distinct hostname from #tspidwith (nolock) where spid>
for read only
open tspid
fetch next from tspid into @hostname
While @@FETCH_STATUS =
begin
select @cmdStr=ping +rtrim(@hostName)
insert into #userip(txt) exec masterxp_cmdshell @cmdStr
select @rowcount=count(id) from #userIP
if @RowCount= //no IP feedback package
begin
insert into #Result(ClientIPhostname) values(Can not get feedback package from Ping!@hostname)
end
if @RowCount>
begin
select @userip=substring(txtcharindex([txt)+charindex(]txt)charindex([txt))
from #userIP
where txt like Pinging%
insert into #Result(ClientIPhostname) values(@userIP@hostname)
end
select @rc=@@error
if @rc=
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
