SELECT
s.session_id,
--c.connection_id,
s.status,
s.host_name,
c.client_net_address,
CASE WHEN s.login_name = s.original_login_name THEN s.login_name ELSE s.login_name + ' (' + s.original_login_name + ')' END as login_name,
s.program_name,
--s.security_id,
--s.nt_domain,
--s.nt_user_name,
--s.original_security_id,
--s.original_login_name,
--c.auth_scheme,
--c.encrypt_option,
--c.connect_time,
--s.login_time,
--s.last_successful_logon, -- Requires 'common criteria compliance enabled' option via sp_configure.
--s.last_unsuccessful_logon, -- Requires 'common criteria compliance enabled' option via sp_configure.
--s.unsuccessful_logons, -- Requires 'common criteria compliance enabled' option via sp_configure.
s.total_elapsed_time as total_elapsed_time_ms,
s.cpu_time as cpu_time_ms,
(s.total_elapsed_time - s.cpu_time) as total_wait_time_ms, -- need to confirm this???
s.memory_usage as memory_usage_pages,
--s.total_scheduled_time as total_scheduled_time_ms,
s.reads,
s.writes,
s.logical_reads,
c.num_reads as net_reads,
c.num_writes as net_writes,
s.row_count,
s.prev_error,
--s.last_request_start_time,
s.last_request_end_time,
--s.host_process_id,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.protocol_version
--s.client_version as client_TDS_protocol_version,
--s.client_interface_name,
--s.text_size,
--s.language,
--s.date_format,
--s.date_first,
--s.quoted_identifier,
--s.arithabort,
--s.ansi_null_dflt_on,
--s.ansi_defaults,
--s.ansi_warnings,
--s.ansi_padding,
--s.ansi_nulls,
--s.concat_null_yields_null,
--CASE s.transaction_isolation_level
-- WHEN 0 THEN 'Unspecified'
-- WHEN 1 THEN 'ReadUncomitted'
-- WHEN 2 THEN 'ReadCommitted'
-- WHEN 3 THEN 'Repeatable'
-- WHEN 4 THEN 'Serializable'
-- WHEN 5 THEN 'Snapshot'
-- ELSE CAST(s.transaction_isolation_level AS VARCHAR(32))
--END as transaction_isolation_level_name,
--s.lock_timeout,
--s.deadlock_priority,
--s.context_info,
--s.endpoint_id,
--s.is_user_process
FROM
sys.dm_exec_sessions s
FULL OUTER JOIN sys.dm_exec_connections c on c.session_id = s.session_id
WHERE
s.session_id IS NULL
OR s.session_id > 50
ORDER BY
s.session_id