|
|
Posted by Luukkanen Tapio on 03/22/06 14:41
Maybe you could periodically select stuff from master.db.sysprocesses, and
count the number of distinct remote client workstation names or number
of distinct client user names over some suitable period?
-tapio
Something developed out of the following might give a guess of the
number of distinct "real users" you have?
Of course this needs changes if you use sqlserver security.
if not exists (select 1 from sysobjects
where name = 'connstats' and type='U')
begin
create table connstats (
client varchar(20)
,ntuser varchar(20)
,program varchar(40)
,dbname varchar(20)
,ts datetime
)
end
go
if exists (select 1 from sysobjects
where name = 'dbu_gather_stats' and type='p')
drop procedure dbu_gather_stats
go
create procedure DBU_gather_stats
as
begin
insert into connstats(client, ntuser, program, dbname, ts)
select p.hostname, p.nt_username, p.program_name
,d.name, getdate()
from master.dbo.sysprocesses as p
,master.dbo.sysdatabases as d
where p.dbid = d.dbid
end
go
grant execute on DBU_gather_stats to public
go
if exists (select 1 from sysobjects
where name = 'dbu_stats' and type='p')
drop procedure dbu_stats
go
create procedure DBU_stats
as
begin
select
"date"=substring(convert(varchar,ts,126),1,10)
,"clients"=count(distinct client)
,"users"=count(distinct ntuser)
,"programs"=count(distinct program)
from master..connstats
group by
substring(convert(varchar,ts,126),1,10)
end
go
grant execute on DBU_stats to public
go
Navigation:
[Reply to this message]
|