1

We license our software by number of workstations.

I have a query that I have used for years to get an accurate count of the workstations logged in to my SQL Server database. For simplicity, all users use the same login name/password. This is built in to the script that attaches to the DB. They have access only to that DB with the exception of

USE [Master] GRANT VIEW SERVER STATE to MyUser

The query that has been working is below:

SELECT COUNT(Users) AS UserCount FROM  (SELECT COUNT(Master.dbo.sysprocesses.hostname) AS Users FROM   Master.dbo.sysprocesses LEFT OUTER JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid WHERE (Master.dbo.sysdatabases.name = 'MyDatabase') GROUP BY Master.dbo.sysprocesses.net_address) AS UserCount_1

Basically this relies on the mac address (Master.dbo.sysprocesses.net_address), since both Workstation names and ip addresses can be duplicated.

Recently this has stopped working at a number of customers. Suddenly individual workstations are showing multiple net addresses for the same workstation causing a substantial overcount of users. This may be related to SQL Server 2012 - not sure.

What I need is a very reliable way to get a count of workstations logged in to my database.

If anyone can tell me why I am suddenly getting multiple net_addresses for each workstation and how to prevent that that would be one possible solution.

Otherwise if anyone can give me a rock solid way to get a workstation count other than the above that would be great. Our largest customer is 50 users by the way.

HERE IS AN EXAMPLE:

SELECT Master.dbo.sysprocesses.hostname AS Users, Master.dbo.sysprocesses.net_address FROM Master.dbo.sysprocesses 
LEFT OUTER JOIN Master.dbo.sysdatabases ON Master.dbo.sysdatabases.dbid = Master.dbo.sysprocesses.dbid 
WHERE Master.dbo.sysdatabases.name = 'mydb' GROUP BY Master.dbo.sysprocesses.hostname, Master.dbo.sysprocesses.net_address

RETURNS:

DAVID-PC  001CC490239E
FLOOR1    001CC41D8012
FLOOR2    CB8FEE6C5856
FLOOR3    A50B18FF1516
KER-PC7   6C626DEA68CC
LIZ-PC    A4E553460E35
LIZ-PC    EFE3F0E20260
LIZ-PC    FD32F7B30360
PAP       9D35A704C29C
PAP       CFB724BA1183
PAP       D1A58A8878E6
PAP       E9B116CA34B8
PAP       F38B335A7AE6

Thanks in advance for any help.

KimM
  • 17
  • 4

2 Answers2

0

You can get an accurate count of the users logged in to SQL Server database by the following query:

SELECT                 
    DB_NAME(dbid) as DB
    COUNT(dbid) as Numb
    loginame as LoginNa
FROM                   
    sys.sysprocesses   
WHERE                  
    dbid > 0           
GROUP BY               
    dbid, loginame

Also you can get full details of connected users by this:

sp_who2 'Active'
mnshahab
  • 770
  • 7
  • 16
  • the question is for distinct workstations, not logons – Tahbaza Mar 11 '15 at 01:02
  • I should have clarified: for simplicity there is only one login name. All users use the same username and password and have access to only the one database. – KimM Mar 11 '15 at 01:03
0

Here is a way that will give you the number of IPs or MAC addresses connected to a given database. I'd go with IP as you might have multiple NICs/MACs in a given high end workstation.

SELECT COUNT(DISTINCT c.client_net_address) as DistinctIPCount,
    COUNT(DISTINCT p.net_address) as DistinctMACCount
FROM sys.dm_exec_connections c INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
    INNER JOIN sysprocesses p ON s.session_id = p.spid
WHERE s.is_user_process = 1
    AND p.dbid = DB_ID('yourdbnamehere')
Tahbaza
  • 9,486
  • 2
  • 26
  • 39
  • Well, that works in that it tells me I have 7 ip addresses and 16 MAC addresses on one of my servers which is at issue. The question is why am I getting multiple MACs (net_address) from the same IP/workstation (sysprocesses.hostname) – KimM Apr 06 '15 at 22:53
  • Wouldn't let me edit my comment above. Here is an example: DAVID-PC 001CC490239E FLOOR1 001CC41D8012 FLOOR2 CB8FEE6C5856 FLOOR3 A50B18FF1516 KER-PC7 6C626DEA68CC LIZ-PC A4E553460E35 LIZ-PC EFE3F0E20260 LIZ-PC FD32F7B30360 PAP 9D35A704C29C PAP CFB724BA1183 PAP D1A58A8878E6 PAP E9B116CA34B8 PAP F38B335A7AE6 – KimM Apr 06 '15 at 22:55
  • Assuming 1 MAC address per computer is not something you should do. 1 MAC address per NIC, and a given computer/workstation often will have multiple NICs. See this SO answer (http://stackoverflow.com/questions/26583712/can-one-computer-have-two-mac-addresses) – Tahbaza Apr 07 '15 at 21:41
  • I am not worried about multiple NICs. That is so rare in our situation I would just tell the customer to deal with it. As you can see from the above I have one computer showing 5 MACs and I can guarantee you there is only one. – KimM Apr 09 '15 at 20:18
  • I am getting the same behavior, for the same hostname (APP Server) I have 32 different net_address values... and I know as per a fact that the server in question does NOT have 32 NICs... Also the server is not a VM, but a physical box, so no dynamic MAC or anything like that happening. So why is this behavior?? Any clue? – luiggig Sep 04 '15 at 12:04