Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

How to get IP address

DECLARE       @Interfaces TABLE
       (
              RowID INT IDENTITY(0, 1),
              Interface CHAR(38),
              IP VARCHAR(15)
       )
 
INSERT @Interfaces
       (
              Interface
       )
EXEC   master..xp_regenumkeys     N'HKEY_LOCAL_MACHINE',
                           N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces'
 
DECLARE       @RowID INT,
       @IP VARCHAR(15),
       @Key NVARCHAR(200)
 
SELECT @RowID = MAX(RowID)
FROM   @Interfaces
 
WHILE @RowID >= 0
       BEGIN
              SELECT @Key = N'System\CurrentControlSet\Services\TcpIP\Parameters\Interfaces\' + Interface
              FROM   @Interfaces
              WHERE RowID = @RowID
 
              EXEC   master..xp_regread   N'HKEY_LOCAL_MACHINE',
                                         @Key,
                                         N'DhcpIPAddress',
                                         @IP OUTPUT
 
              IF @IP <> '0.0.0.0'
                     UPDATE @Interfaces
                     SET    IP = @IP
                     WHERE RowID = @RowID
 
              SET    @RowID = @RowID - 1
       END
                                 
SELECT IP
FROM   @Interfaces
WHERE IP IS NOT NULL

Print | posted on Wednesday, July 16, 2008 1:18 PM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Feedback

Gravatar

# re: How to get IP address

you could also just run

select * from sys.dm_exec_connections
where client_net_address != '<local machine>'

and check the local_net_address column
7/16/2008 1:46 PM | Mladen
Gravatar

# re: How to get IP address

If you have xp_cmdshell enabled on your SQL Server, you can also use the following:

set nocount on
create table #ip(data varchar(256))
insert #ip exec master..xp_cmdshell 'ipconfig | findstr "IP Address"'
select substring(data, charindex(':', data)+2, 25) IPAddress from #ip where data like '%[0-9].%[0-9].%[0-9].[0-9]%'
drop table #ip

This should also pick up IPs for multiple NICs on your server, but I haven't tested that.
7/16/2008 3:43 PM | Rob Volk
Gravatar

# re: How to get IP address

I executed the given code, but it will not fetch the data correctly
7/17/2008 6:26 AM | Nidhi
Gravatar

# re: How to get IP address

Nice article
need to know about this registry....
7/17/2008 7:35 AM | Smitha
Gravatar

# re: How to get IP address

because i like to find others ip sdderes
2/7/2009 9:39 AM | pranay choksi
Gravatar

# re: How to get IP address

I have tested the procedure given above to get the client IP but it provides server IP. To get client IP who modifies my database,
I changed Mladen's query which is as under. You may try this

select top 1 * from {any of the table on server}
select top 1 client_net_address from sys.dm_exec_connections
where client_net_address != '<local machine>'
order by last_read desc

Which shows my IP. I used to store this thru triggers when ever crucial data changes. Please test at your end and update the feedback

11/28/2011 11:01 AM | Madhu Menon
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET