Has anyone compared two datetimes to see if they are in the same time frame?
Our contract with hosting services allows us 10 concurrent users.
Nobody users were complaining, but I had a hunch we might be close to that limit.
I wrote some SQL to find count the max users per day aggregated per day over the last 6 months:
from the table TA_AUDIT_LOGON_INTERNAL, which i'm hoping was the right table for a login history table.
the table looks like this:
SQL:
SELECT CAST(logon_dt AS DATE) AS ActivityDate, COUNT(DISTINCT user_id) AS UniqueUserCount FROM TA_AUDIT_LOGON_INTERNAL WHERE logon_dt >= DATEADD(MONTH, -6, GETDATE()) AND status = 0 GROUP BY CAST(logon_dt AS DATE) ORDER BY ActivityDate; /* TA_AUDIT_LOGIN_INTERNAL status dictionary 0 = successful login 1 = invalid tessitura login 2 = login is already locked 3 = SQL Server login failed 4 = too many invalid attempts so locking login 5 = invalid password entered 6 = no app access per group security settings */
result:
graphed it out in excel:
so i can see we have max 20 users per day, but on average12ish per day. So we need probably need to increase our concurrent user limit.
This was a great, but i'm curious to actually count concurrent users (not just total users) by comparing logon_dt to logout_dt aggregated per day?
I'm thinking of a third column and the logic would be something like "If count of concurrent users >10 then "1" and then have some subquery to compare time frames per day and count how many"
just confused on how to do that!Any help is appreciated!
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