CREATE VIEW sys.dm_broker_connections AS
SELECT
connection_id,
transport_stream_id,
state,
state_desc = sn.name,
connect_time,
login_time,
authentication_method,
principal_name,
remote_user_name,
last_activity_time,
is_accept,
login_state,
login_state_desc = ln.name,
peer_certificate_id,
encryption_algorithm = encalg,
encryption_algorithm_desc = ag.name,
receives_posted,
is_receive_flow_controlled,
sends_posted,
is_send_flow_controlled,
total_bytes_sent,
total_bytes_received,
total_fragments_sent,
total_fragments_received,
total_sends,
total_receives,
peer_arbitration_id
FROM OpenRowset (TABLE SBCONNECTIONENDPOINTS) as c
LEFT OUTER JOIN sys.syspalvalues ag ON ag.class = 'EPET' AND ag.value = c.encalg
LEFT OUTER JOIN sys.syspalvalues sn ON sn.class = 'EPCS' AND sn.value = c.state
LEFT OUTER JOIN sys.syspalvalues ln ON ln.class = 'EPLS' AND ln.value = c.login_state
WHERE c.endpoint_type = 0 -- x_eetBroker
|
