Client IP Address Search

Does anyone have a method of tracing a clients IP address
from the SPID available inside SQL Server to the sp_who2
command?Here's one I prepared earlier :-)
create proc get_hostip (@.spid int = NULL)
as
set nocount on
declare @.host varchar(100)
declare @.ip varchar(15)
declare @.cmd varchar(200)
declare @.temp varchar(255)
create table #ip(iptext varchar(255))
If @.spid is null select @.host = host_name()
else
select @.host = max(hostname)
from master.dbo.sysprocesses
where spid = @.spid
if @.host is not null
begin
set @.cmd = 'ping -n 1 ' + @.host
insert #ip exec master..xp_cmdshell @.cmd
select @.ip = ISNULL(substring(iptext,(charindex('[',iptext)+1),
(charindex(']',iptext)-(charindex('[',iptext)+1))),'')
from #ip
where charindex('[',iptext)>0
end
drop table #ip
select NULLIF(rtrim(@.host),'') as 'Hostname',
rtrim(@.ip) as 'IP_Address'
return
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David B" <david.brough@.nygard.com> wrote in message
news:02e401c39ffb$8a4c74e0$a401280a@.phx.gbl...
Does anyone have a method of tracing a clients IP address
from the SPID available inside SQL Server to the sp_who2
command?|||I am sure you know this will not work for remote hostname. There is no way
for you to resolve my current hostname (client2) for example. This is
because my hostname is not registered with your DNS or WINS and I'm not on
your local subnets so broadcast will not reach me.
As far as name resolutions on windows is concerned, there are 2 kinds:
1. Netbios name resolution - there is no dot in the name. The order for
resolution is as follow:
NetBIOS name cache
WINS
Broadcasts
LMHOSTS
Hosts
DNS
2. Hostname resolution - the order is as follow:
Hosts
DNS
NetBIOS name cache
WINS
Broadcasts
LMHOSTS
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eCdcf9$nDHA.3320@.tk2msftngp13.phx.gbl...
> Here's one I prepared earlier :-)
> create proc get_hostip (@.spid int = NULL)
> as
> set nocount on
> declare @.host varchar(100)
> declare @.ip varchar(15)
> declare @.cmd varchar(200)
> declare @.temp varchar(255)
> create table #ip(iptext varchar(255))
> If @.spid is null select @.host = host_name()
> else
> select @.host = max(hostname)
> from master.dbo.sysprocesses
> where spid = @.spid
> if @.host is not null
> begin
> set @.cmd = 'ping -n 1 ' + @.host
> insert #ip exec master..xp_cmdshell @.cmd
> select @.ip = ISNULL(substring(iptext,(charindex('[',iptext)+1),
> (charindex(']',iptext)-(charindex('[',iptext)+1))),'')
> from #ip
> where charindex('[',iptext)>0
> end
> drop table #ip
> select NULLIF(rtrim(@.host),'') as 'Hostname',
> rtrim(@.ip) as 'IP_Address'
> return
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "David B" <david.brough@.nygard.com> wrote in message
> news:02e401c39ffb$8a4c74e0$a401280a@.phx.gbl...
> Does anyone have a method of tracing a clients IP address
> from the SPID available inside SQL Server to the sp_who2
> command?
>
>|||Yeah, I should have stressed its limitations, it is of course dependent on
resolving the hostname.
My PC resolves a local ping as [::1] , not sure why :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"oj" <nospam_ojngo@.home.com> wrote in message
news:e8jj7QAoDHA.1764@.tk2msftngp13.phx.gbl...
I am sure you know this will not work for remote hostname. There is no way
for you to resolve my current hostname (client2) for example. This is
because my hostname is not registered with your DNS or WINS and I'm not on
your local subnets so broadcast will not reach me.
As far as name resolutions on windows is concerned, there are 2 kinds:
1. Netbios name resolution - there is no dot in the name. The order for
resolution is as follow:
NetBIOS name cache
WINS
Broadcasts
LMHOSTS
Hosts
DNS
2. Hostname resolution - the order is as follow:
Hosts
DNS
NetBIOS name cache
WINS
Broadcasts
LMHOSTS
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eCdcf9$nDHA.3320@.tk2msftngp13.phx.gbl...
> Here's one I prepared earlier :-)
> create proc get_hostip (@.spid int = NULL)
> as
> set nocount on
> declare @.host varchar(100)
> declare @.ip varchar(15)
> declare @.cmd varchar(200)
> declare @.temp varchar(255)
> create table #ip(iptext varchar(255))
> If @.spid is null select @.host = host_name()
> else
> select @.host = max(hostname)
> from master.dbo.sysprocesses
> where spid = @.spid
> if @.host is not null
> begin
> set @.cmd = 'ping -n 1 ' + @.host
> insert #ip exec master..xp_cmdshell @.cmd
> select @.ip = ISNULL(substring(iptext,(charindex('[',iptext)+1),
> (charindex(']',iptext)-(charindex('[',iptext)+1))),'')
> from #ip
> where charindex('[',iptext)>0
> end
> drop table #ip
> select NULLIF(rtrim(@.host),'') as 'Hostname',
> rtrim(@.ip) as 'IP_Address'
> return
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "David B" <david.brough@.nygard.com> wrote in message
> news:02e401c39ffb$8a4c74e0$a401280a@.phx.gbl...
> Does anyone have a method of tracing a clients IP address
> from the SPID available inside SQL Server to the sp_who2
> command?
>
>