Get total user session with client IP address

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  July 20, 2017 at 7:55 am

  #338365

  Hi.
  The following script fetching number of user session. Is there way to get total user session along with client IP or host name?

  
SELECT date_time=GETDATE(),
  DB_NAME(dbid) as DBName,
  COUNT(dbid) as NumberOfConnections,
  loginame as LoginName
FROM
  sys.sysprocesses
WHERE
  dbid > 0 and DB_NAME(dbid) ='PRO'
GROUP BY
  dbid, loginame
   go


  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 20, 2017 at 8:01 am

  #1951848

  There's a hostname column in sysprocesses - have you looked at that?  You shouldn't be using sysprocesses at all, though - it's only provided for backwards compatibility with SQL Server 2000.  Go to the sysprocesses page at Microsoft and it'll give you a link to a page that maps old-style system tables to their equivalent catalog views.

  John

• Lowell

  SSC Guru

  Points: 323504

  More actions

  July 20, 2017 at 8:07 am

  #1951849

  sys.dm_exec_sessions has whether a spid/session_id is a user process or a system process.

  thios seems to work for me, is this what you are after?

  SELECT date_time=GETDATE(),
DB_NAME(procz.dbid) as DBName,
COUNT(procz.dbid) as NumberOfConnections,
SUM (CASE WHEN sess.is_user_process=1 THEN 1 ELSE 0 END) AS UserSessions,
SUM (CASE WHEN sess.is_user_process=1 THEN 0 ELSE 1 END) AS SystemSessions,
loginame as LoginName
--select *
FROM
sys.sysprocesses procz
INNER JOIN sys.dm_exec_sessions sess ON procz.spid = sess.session_id
GROUP BY
procz.dbid, procz.loginame

  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!

• Thom A

  SSC Guru

  Points: 99126

  More actions

  July 20, 2017 at 8:23 am

  #1951857

  Using the newer views, that John suggested:
  USE master;
GO

  SELECT GETDATE() AS date_time,
       d.[name] AS DBName,
       COUNT(c.connection_id) AS Connections,
       s.login_name AS LoginName,
       c.client_net_address AS IPAddress
FROM sys.dm_exec_connections c
    JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
    JOIN sys.databases d on s.database_id = d.database_id
GROUP BY d.[name], s.login_name, c.client_net_address;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  July 20, 2017 at 10:21 pm

  #1951999

  Hi Thom..
  Second JOIN condition not valid due to database_id field not available in sys.dm_exec_sessions,  
  

  USE master;

  GO

  SELECT GETDATE() AS date_time,

  --d.[name] AS DBName,

  COUNT(c.connection_id) AS Connections,

  s.login_name AS LoginName,

  c.client_net_address AS IPAddress

  FROM sys.dm_exec_connections c

  JOIN sys.dm_exec_sessions s on c.session_id = s.session_id

  --JOIN sys.databases d on s.database_id = d.database_id

  GROUP BY s.login_name, c.client_net_address;

  working and get result each IP address have total number of connection after commended JOIN statement

  Thanks