How to get the IP address, computer name and other information of the front-end connection in SQL Server

In some requirements, it may be necessary to know some system information about the front-end programs connected to SQL Server, such as the computer name of the front-end connection, the IP address, when to start requesting the connection, and when to end the connection.
If you are not familiar with SQL Server's system functions or views, this functionality may seem more complex, but in fact, the dynamic management view of SQL Server has given us this information, let's take a look at two dynamic management views.
1, sys.dm_exec_sessions
session_id smallint identifies the session associated with each active primary connection.
Login_time DateTime When the session was established.
HOST_NAME nvarchar (128) The host associated with the session.
Program_name nvarchar (128) The program associated with the session.
host_process_id int the process ID associated with the session.
client_version int the interface version that the client uses to connect to the server.
Client_interface_name nvarchar (32) The interface name used by the client to connect to the server.
SECURITY_ID varbinary (85) The Microsoft Windows security ID that is associated with the login name.
Login_name nvarchar (128) The SQL logon name associated with the session.
Nt_domain nvarchar (128) The domain from which the session connection was established.
Nt_user_name nvarchar (128) The user name associated with the session.
Status nvarchar (30) The state of the session. Possible values: 1, run-currently running one or more request 2, sleep-currently not running any request 3, Hibernate-session is in pre-logon state
Context_info the context_info value of the varbinary (128) session.
Cpu_time int the CPU time (in milliseconds) that the session occupies.
Memory_usage int the number of 8 KB memory pages that the session occupies.
The Total_scheduled_time int plan contains the total time (in milliseconds) spent on the execution of the requested session.
Total_elapsed_time int the elapsed time (in milliseconds) since the session was established.
endpoint_id int the ID of the endpoint associated with the session.
Last_request_start_time datetime The start time of the most recent session request. This includes the request that is currently executing.
Last_request_end_time datetime When the last session request was completed.
Reads bigint the number of reads performed by requests in that session during the session.
Writes bigint the number of writes performed by the request in the session during the session.
Logical_reads bigint The number of logical reads that have been performed on the session.
Is_user_process bit If the session is a system session, it is 0. Otherwise, it is 1.
Text_size the TEXTSIZE setting of the int session.
Language nvarchar (128) session language settings.
Date_format nvarchar (3) session DateFormat settings.
Date_first the Datefirst settings for the smallint session.
The QUOTED_IDENTIFIER setting of the QUOTED_IDENTIFIER bit session.
The ARITHABORT setting of the ARITHABORT bit session.
The ansi_null_dflt_on setting of the ANSI_NULL_DFLT_ON bit session.
The Ansi_defaults setting of the ANSI_DEFAULTS bit session.
The ansi_warnings setting of the ANSI_WARNINGS bit session.
The ANSI_PADDING setting of the ANSI_PADDING bit session.
The ANSI_NULLS setting of the ANSI_NULLS bit session.
The Concat_null_yields_null setting of the CONCAT_NULL_YIELDS_NULL bit session.
Transaction_isolation_level The transaction isolation level of the smallint session. 0 = unspecified 1 = uncommitted Read 2 = Read Committed 3 = repeatable 4 = Serializable 5 = Snapshot
Lock_timeout the LOCK_TIMEOUT setting of the int session. The value is measured in milliseconds.
Deadlock_priority the deadlock_priority setting of the int session.
Row_count bigint The number of rows returned by the session so far.
The ID of the most recent error returned by the Prev_error int session.

For example, we want to see those hosts connected to SQL Server servers, you can use the following SQL statement:
Select distinct host_name from sys.dm_exec_sessions
To see those users connected to SQL Server servers:
Select distinct login_name from sys.dm_exec_sessions

2.sys.dm_exec_connections
This view returns the details of each connection connected to the SQL Server servers, and the following is the column returned by Sys.dm_exec_connections:
Column name Data type description
The session_id int identifies the session associated with this connection.
most_recent_session_id INT displays the session ID of the most recent request associated with this connection.
Connect_time the timestamp when the datetime connection was established.
Net_transport nvarchar (40) describes the physical transport protocol used by the connection.
Protocol_type nvarchar (40) specifies the protocol type of the payload. This parameter is currently distinguishable from TDS (TSQL) and SOAP.
protocol_version int the version of the data access protocol associated with this connection.
endpoint_id int Unique identifier of the endpoint associated with this connection. This endpoint_id can be used to query the sys.endpoints view.
Encrypt_option nvarchar (40) indicates whether an encrypted Boolean value is enabled for this connection.
Auth_scheme nvarchar (40) specifies the SQL server/nt authentication that is used with this connection.
Node_affinity smallint Displays the SOS node associated with this connection.
Num_reads int the number of read packets that have occurred in this connection.
Num_writes int the number of write packets that have occurred in this connection.
Last_read datetime The timestamp of the last read operation in this connection.
Last_write datetime The timestamp of the last write operation in this connection.
net_packet_size int the size of the network packet used for information and data.
client_net_address varchar (40) The host address of the client to which this server is connected.
Client_tcp_port the port number on the client computer with which the connection is associated.
local_net_address varchar (40) Displays the IP address of the destination server for this connection. Available only for connections that use the TCP transport provider.
Local_tcp_port int If this connection uses a TCP transport, the TCP port of the destination server for that connection is displayed.
The connection_id uniqueidentifier uniquely identifies each connection.
PARENT_CONNECTION_ID uniqueidentifier identifies the primary connection that the MARS session is using.
Most_recent_sql_handle varbinary (64) The SQL handle of the last request executed on this connection. The Most_recent_sql_handle column is always synchronized with the most_recent_session_id column.
For example, I want to see the IP of the client IP that is currently connected and the server on which SQL Servers are located, using the following SQL query:
Select client_net_address ' Client IP ', local_net_address ' server IP ' from sys.dm_exec_connections where [email protected] @spid
The function of the @ @spid is to return the session ID of the current process.

How to get the IP address, computer name, etc. of the front-end connection in SQL Server