July 20, 2017 at 7:55 am
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
July 20, 2017 at 8:01 am
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
July 20, 2017 at 8:07 am
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
July 20, 2017 at 8:23 am
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
July 20, 2017 at 10:21 pm
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy