Concurrent Users in the database investigation

Have you looked at the Concurrent Users Analysis report in the Miscellaneous folder?  It can give by Date or By Date and hour...

I didn't know that report existed!

It seems like i've manually tried to recreate that haha, thanks for the tip Mary

what's really cool is now you can look at the stored procedure behind that report and see how close you were with your own code! :-)  I tend to pick apart other reports as a learning thing- super helpful.

Doesn't concurrent mean at any one time (not on any given date)? I'm guessing that the report factors in both the logon_dt and logout_dt fields to calculate the number of concurrent users.

Yes i literally just did that and looking through the stored procedure RP_CONCURRENT_USERS hahaha.

Here is the stored procedure for the report Mary French referenced. RP_CONCURRENT_USERS

USE [impresario]
GO

/****** Object:  StoredProcedure [dbo].[RP_CONCURRENT_USERS]    Script Date: 9/12/2023 9:47:07 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[RP_CONCURRENT_USERS]
	@start_dt datetime,
	@end_dt datetime,
	@groupby char(1) = 'D',		--Group by (D)ay or (H)our
	@include_detail char(1) = 'N'
AS

/***********************************************************************************************
CWR 6/28/2022  DEV-13457

Modified CWR 7/20/2022 DEV-14154  Calculated concurrent periods in a better fashion, weeding out
duplicate consecutive logins.  This relies on user_id, group, and location.  Not sure about the future
of location so this may have to be addressed later

Modified CWR 7/25/2022 DEV-14154 We're measuring concurrency in one hour increments only.  This means
that if different user/user group/location combinations are recorded 3 times in the same hour it will be counted as 3
concurrent logins for that hour even if they don't overlap in that particular hour.  The maximum date
range allowed is 6 months for performance reasons

Exec RP_CONCURRENT_USERS @start_dt = '2022-6-1', @end_dt = '2022-7-1', @groupby = 'H'
select * from TA_AUDIT_LOGON_INTERNAL where logon_dt > '2022-6-28'


***********************************************************************************************/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

If DATEDIFF(Month, @start_dt, @end_dt) > 6
  BEGIN
	Raiserror('Date range cannot exceed 6 months', 11, 2)
        Return
  END

If @groupby not in ('H', 'D')
	Select @groupby = 'D';

-- operate on full days only
Select	@start_dt = Convert(Datetime, Convert(Date, @start_dt)),
	@end_dt = DateAdd(MS, -3, Convert(Datetime, DateAdd(Day, 1, Convert(Date, @end_dt))))

Create Table #periods(id int identity, start_dt datetime not null, end_dt datetime not null, concurrent_logins int not null default 0)

-- measure in hourly periods
Insert	#periods(start_dt, end_dt)
Select	Convert(Datetime, Convert(Char(8), y.unique_date, 112) + ' ' + Convert(varchar(2), a.offset-1) + ':00:00'),
	Convert(Datetime, Convert(Char(8), y.unique_date, 112) + ' ' + Convert(varchar(2), a.offset-1) + ':59:59.997')
From	[dbo].TALLY_NUMBER a
	CROSS JOIN (Select Distinct Convert(Date, logon_dt) as unique_date
		From	[dbo].TA_AUDIT_LOGON_INTERNAL
		Where	logon_dt between @start_dt and @end_dt
			and status = 0)  as y
Where	a.offset between 1 and 24

-- here's the actual work
Update	a
Set	concurrent_logins = 
		(Select count (*) 
		From	(Select distinct [user_id], tess_ug_id, [location]
			From	[dbo].TA_AUDIT_LOGON_INTERNAL x
			Where	x.logon_dt >= @start_dt 
				and x.status = 0
				and Coalesce(logout_dt, Convert(Datetime, Convert(CHAR(8), logon_dt, 112) + ' 23:59:59.997'))  >= a.start_dt and x.logon_dt <= a.end_dt) x
		) 
From	#periods a


Select	start_dt, 
	DatePart(hour, start_dt) as [Hour], 
	sum(concurrent_logins) as concurrent_logins 
Into	#periods_summed
From	#periods 
Group By start_dt, DatePart(hour, start_dt)

-- debug
--	select 'periods', * from #periods
--	select 'summed', * from #periods_summed

 -- and the output
Select 'Detail' as stage,
	Cast(start_dt as Date) as [Date],
	Case When @groupby = 'D' Then null Else DatePart(hour, start_dt) End as [Hour],
	Max(concurrent_logins) as [Concurrent Logins],
	Cast(null as int) as dow,
	Cast(null as int) as avg_peak_logons,
	Cast(null as int) as peak_logons
From	#periods_summed 
Where	concurrent_logins > 0
	and @include_detail = 'Y'
Group By Cast(start_dt as Date), Case When @groupby = 'D' Then null Else DatePart(hour, start_dt) End
UNION All
Select 'Dow' as stage,
 	Cast(null as Date) as [Date],
	Case When @groupby = 'D' Then null Else [Hour] End,
	Cast(null as Int) as [Concurrent Logins],
	DatePart(DW, start_dt) as dow,
	Avg(concurrent_logins) as avg_peak_logons,
	Max(concurrent_logins) as peak_logons
From	#periods_summed
Where	concurrent_logins > 0
Group by DatePart(DW, start_dt), Case When @groupby = 'D' Then null Else [Hour] End
UNION ALL
Select 'Totals' as stage,
 	Cast(null as Date) as [Date],
 	Cast(null as Int) as [Hour],
	Cast(null as Int) as [Concurrent Logins],
	Cast(null as int) as dow,
	Avg(concurrent_logins) as avg_peak_logons,
	Max(concurrent_logins) as peak_logons
From	#periods_summed
Where	concurrent_logins > 0



RETURN


GO