A friend of mine wanted to conduct a license audit of her installation and used sysprocesses to get the info. On each server she created a DTS job that ran the following query:
select getdate() as StatsDate, ss.srvname as ServerName,
rtrim(db.name) as DBName, rtrim(hostname) as HostName,
rtrim(program_name) as ProgramName, rtrim(nt_domain) as Domain,
rtrim(nt_username) as UserName, rtrim(net_address) as MACAddress,
rtrim(loginame) as LoginName
from master..sysprocesses sp
join master..sysdatabases db
on sp.dbid = db.dbid
cross join master..sysservers ss
where sp.loginame <> 'sa'
order by db.name, hostname, loginame
then wrote it to a text file. She ran it at the top of the hour, on some days every 5 or 15 minutes, over an extended period of time and collected the scripts on her machine via a scheduled task that ran at 5 minutes after the hour that did this:
copy C:\SQLUserStats\ConnectionStats.txt + \\server1\c$\SQLUserStats\sqluserstats.txt C:\SQLUserStats\ConnectionStats.txt
copy C:\SQLUserStats\ConnectionStats.txt + \\server2\c$\SQLUserStats\sqluserstats.txt C:\SQLUserStats\ConnectionStats.txt
Worked like a charm. Suck the text file into a table and you can slice and dice the data to your heart's contentment.
One thing it would not show: if connections came through a gateway or application server that could spoof a MAC address or user name, you could have several dozen users hidden that you won't readily see. You also wouldn't see users who logged in for less than an hour and didn't cross that top of the hour boundary, so there were days that it ran every 15 minutes or every 5 minutes. Still, it was deemed adequate by her management to give a reasonable number of user connections.
Regardless, sysprocesses saved a whole lot of work for her.
-----Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson