Cool Tips‎ > ‎Connectivity‎ > ‎

Connectivity Get Connection Counts by IP and Login

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        4 Apr 2012

Description

 Retrieve summaries of connections to the SQL server by both IP and login name

Code

Summary of Connections by IP:

-- Get a count of SQL connections by IP address
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];

 
 

Summary of Connections by Login Name:

-- Get a count of SQL connections by login_name
SELECT
  
login_name,
  
COUNT(session_id) AS [session_count]
FROM  sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name;


ċ
Get Connection Counts by IP and Login.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments