Extended Events & sys.dm_exec_connections

Question

Post reply

Extended Events & sys.dm_exec_connections

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
December 20, 2016 at 5:14 am

#319861

I'm trying to set up an Extended Event to capture local_net_address (from sys.dm_exec_connections), but I just cannot find it.
I've looked in
- connection_accept
- existing_connection
- login
- login_event
- preconnect_completed
and I'm working my way through the other events still, but there are tons of them, so if anybody knows where this is (or even if it definitely exists within XEvents, please could you give me shout? (I've tried Googling but I keep getting false positives.)
Thanks very much

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

local_net_address is the server itself, don't you mean the client_net_address instead?

--EDIT--

anyway, I don't think IP address for the connector is exposed anywhere in Extended Events. you might get away with mapping hostname back to ip's but that might be blank or spoofed, since it's part of the connection string.

i found it. kinda.

in SQL2016 at least, i found the event connection_accept has connection_id/is_duplicated/peer_address/peer_port/provider_type

it says "connection_accept

Occurs when a new connection is accepted by the server. [highlight="#ffff11"]Last address octet is removed.[/highlight]"

so this might not help much.

i got values like peer_address172.20.100.xx

-EDIT-

I know i can capture that in a server trigger via the ConnectionProperty() functions;

i've created login triggers that captured details like this:

-- --the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

CURRENT_USER AS CurrentUser,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

SUSER_ID() AS sUserid,

SUSER_SNAME() AS sUserName,

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

Thanks for replying; no, client_net_address is the address of the originating connection. I want to know where it's connecting to (i.e. I want to determine whether a connection is going straight to an instance or is using an Availability Group listener). I can't see a way to capture it in Extended Events.