Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Finding the Client IP

It’s not often that I’ve had the need to trace back a client to their host computer, but there are time it’s handy. I had thought that the easy way to do this in the past was with sp_who.

However that’s not correct. I checked back in the SQL Server 2000 BOL entry for sp_who and it has hostname, but not IP. That could be the same, but not necessarily.

I saw a post where someone had listed this code

declare @IPAddress as varchar(15)
declare @data XML
set @data = EVENTDATA()
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')

That’s interesting. It uses the EVENTDATA(), which tracks data about events. I presume that the IP is in the clienthost all the time, and that it likely comes from the view below, but I can’t find documentation on that. The XSD schema for EventData is beyond me.

I kept thinking this had to be stored in a system table/view somewhere, and sure enough I found sys.dm_exec_sessions. It actually contains the client IP and port, so you can trace things down at a more detailed connection level.

On my local instance it doesn’t seem to work great, but if I connect remotely, I see an IP.

Comments

Posted by Glenn Berry on 6 April 2010

This query works pretty well:

SELECT ec.client_net_address, es.[program_name],

es.[host_name], es.login_name,

COUNT(ec.session_id) AS [connection count]

FROM sys.dm_exec_sessions AS es  

INNER JOIN sys.dm_exec_connections AS ec  

ON es.session_id = ec.session_id  

GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name  

ORDER BY ec.client_net_address, es.[program_name];

Posted by Jason Brimhall on 6 April 2010

Interesting to see the very same topic about which I also wrote a lengthy blog today.

jasonbrimhall.info

Posted by Denny Cherry on 7 April 2010

Steve,

Why not just use?

select client_net_address

from sys.dm_exec_connections

where session_id = @@spid

Denny

Posted by Steve Jones on 7 April 2010

That's what I used. Should have posted the code. I showed the code that someone else posted, and I hadn't considered.

Posted by Ken Lee-263418 on 11 April 2010

Thanks for the tips on getting IP addresses. I've always pinged the hostname if I was interested in that info. But then, I'd want to know the current IP address being used, not the address when and incidence occurred.

I never even think of using sp_who, I always use sp_who2 because of the additional information it provides. Of course, I'm not interested in an IP address at the time I'm using it. I'm interested in blocking, how long (LastBatch tells that for batch transactions that should be measured in milliseconds and getting minutes instead.) and if it is affecting processing. (CPUTime)

Posted by Ken Lee-263418 on 11 April 2010

Whoops, meant "an incidence"

Leave a Comment

Please register or log in to leave a comment.