November 13, 2012 at 12:43 pm
I have a table that tracks when users log in and log out of our primary accounting system. Each time someone logs in a row is added to the table with the persons user Name, the Date/Time they logged in and what IP address they logged in from. When the user logs out or is forcibly logged out (due to automatic timeout from inactivity ) the same row is updated with the Date/Time the user logged out via the dtLogOut . The next time they log in a new rows is added. This continues on and on and on…
An active login is where the dtLogIn is not null and the dtLogOut is null
What I’d like to do is get some aggregations from this that include:
How many users were logged in at 15 minutes past 5PM
How many active sessions did we have at the top of each hour between 9AM and 4PM
What time of the day had the most users logged in at the same time
What users have more than one active session.
I would think an easy way to do this would be to decide on the smallest level of measurement, say 15 minutes and then every 15 minutes run a job that gets these aggregations and store them in a table. After a few weeks I’d have some calculated values to use. The problem is how to do this with existing data?
The DDL for the table follows
Suggestions?
CREATE TABLE dbo.USERLOGIN
(
hID NUMERIC (18,0) IDENTITY(1,1) NOT NULL,
UserID VARCHAR(256) NOT NULL,
IPLoc VARCHAR (250) NULL,
LogIn DATETIME NULL,
Logout DATETIME NULL,
CONSTRAINT PK_USERLOGIN PRIMARY KEY CLUSTERED ( hID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
)
ON [PRIMARY]
GO
Kindest Regards,
Just say No to Facebook!November 13, 2012 at 1:18 pm
YSL I have a pair of TableValued functions for this, that i use to join against existing data so i can group by the rounding up or rounding down to the nearest increment I'm interested in.
some quick code to populate your sample table:
--a handful of fake data
insert into USERLOGIN (UserID,IPLoc,LogIn,Logout)
SELECT TOP 5000
ROW_NUMBER() OVER (PARTITION BY t1.object_id ORDER BY t1.object_id),
'192.168.1.'
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1),
CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
FROM sys.columns t1
cross join (select x='0123456789') a
my roundup table...so a time of x:01 minutes rounds up to teh first 15 minute interval, for example:
CREATE FUNCTION [dbo].[MinutesTableRoundUp] ()
RETURNS TABLE
AS
RETURN
SELECT 0 As Minutes,60 AS Five,50 AS Tens,60 AS Fifteen,60 AS Thirty UNION ALL
SELECT 1,5,10,15,30 UNION ALL
SELECT 2,5,10,15,30 UNION ALL
SELECT 3,5,10,15,30 UNION ALL
SELECT 4,5,10,15,30 UNION ALL
SELECT 5,5,10,15,30 UNION ALL
SELECT 6,10,10,15,30 UNION ALL
SELECT 7,10,10,15,30 UNION ALL
SELECT 8,10,10,15,30 UNION ALL
SELECT 9,10,10,15,30 UNION ALL
SELECT 10,10,20,15,30 UNION ALL
SELECT 11,15,20,15,30 UNION ALL
SELECT 12,15,20,15,30 UNION ALL
SELECT 13,15,20,15,30 UNION ALL
SELECT 14,15,20,15,30 UNION ALL
SELECT 15,15,20,15,30 UNION ALL
SELECT 16,20,20,30,30 UNION ALL
SELECT 17,20,20,30,30 UNION ALL
SELECT 18,20,20,30,30 UNION ALL
SELECT 19,20,20,30,30 UNION ALL
SELECT 20,20,20,30,30 UNION ALL
SELECT 21,25,30,30,30 UNION ALL
SELECT 22,25,30,30,30 UNION ALL
SELECT 23,25,30,30,30 UNION ALL
SELECT 24,25,30,30,30 UNION ALL
SELECT 25,25,30,30,30 UNION ALL
SELECT 26,30,30,30,30 UNION ALL
SELECT 27,30,30,30,30 UNION ALL
SELECT 28,30,30,30,30 UNION ALL
SELECT 29,30,30,30,30 UNION ALL
SELECT 30,30,30,30,30 UNION ALL
SELECT 31,35,40,45,60 UNION ALL
SELECT 32,35,40,45,60 UNION ALL
SELECT 33,35,40,45,60 UNION ALL
SELECT 34,35,40,45,60 UNION ALL
SELECT 35,35,40,45,60 UNION ALL
SELECT 36,40,40,45,60 UNION ALL
SELECT 37,40,40,45,60 UNION ALL
SELECT 38,40,40,45,60 UNION ALL
SELECT 39,40,40,45,60 UNION ALL
SELECT 40,40,40,45,60 UNION ALL
SELECT 41,45,50,45,60 UNION ALL
SELECT 42,45,50,45,60 UNION ALL
SELECT 43,45,50,45,60 UNION ALL
SELECT 44,45,50,45,60 UNION ALL
SELECT 45,45,50,45,60 UNION ALL
SELECT 46,50,50,60,60 UNION ALL
SELECT 47,50,50,60,60 UNION ALL
SELECT 48,50,50,60,60 UNION ALL
SELECT 49,50,50,60,60 UNION ALL
SELECT 50,50,50,60,60 UNION ALL
SELECT 51,55,60,60,60 UNION ALL
SELECT 52,55,60,60,60 UNION ALL
SELECT 53,55,60,60,60 UNION ALL
SELECT 54,55,60,60,60 UNION ALL
SELECT 55,55,60,60,60 UNION ALL
SELECT 56,60,60,60,60 UNION ALL
SELECT 57,60,60,60,60 UNION ALL
SELECT 58,60,60,60,60 UNION ALL
SELECT 59,60,60,60,60
and an example of the data joined up:
--"round" dates to the nearest half hour
SELECT s.*,
mt.*
--"round" dates to the nearest half hour
FROM USERLOGIN s
INNER JOIN dbo.MinutesTable() mt
ON DATEPART(mi, s.LogIn) = mt.[Minutes]
with that kind of raw data, i can eliminate columns and add count() and group by what we are looking for , say, the Fifteen or the Thirty column for 15/30 minute increments.
Lowell
November 13, 2012 at 2:54 pm
Lowel
Thank you for the reply but I'm not following how this can be used to get the answers I'm looking for. Could you perhaps provide an example using you r sample data how I would find out how many users were active (logged in) at say 1PM on 11/10/2012 or what time period of the day on 11/12/2012 had the most users connected at one time?
Thanks
Kindest Regards,
Just say No to Facebook!November 13, 2012 at 3:17 pm
YSLGuru (11/13/2012)
LowelThank you for the reply but I'm not following how this can be used to get the answers I'm looking for. Could you perhaps provide an example using you r sample data how I would find out how many users were active (logged in) at say 1PM on 11/10/2012 or what time period of the day on 11/12/2012 had the most users connected at one time?
Thanks
my sample data does not necessarily have the specific time slot you asked for ;
i changed mine to a million rows instead.
one example, the setup first:
CREATE TABLE dbo.USERLOGIN
(
hID NUMERIC (18,0) IDENTITY(1,1) NOT NULL,
UserID VARCHAR(256) NOT NULL,
IPLoc VARCHAR (250) NULL,
LogIn DATETIME NULL,
Logout DATETIME NULL,
CONSTRAINT PK_USERLOGIN PRIMARY KEY CLUSTERED ( hID ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
)
ON [PRIMARY]
GO
--a handful of fake data
insert into USERLOGIN (UserID,IPLoc,LogIn,Logout)
SELECT TOP 1000000
ROW_NUMBER() OVER (PARTITION BY t1.object_id ORDER BY t1.object_id),
'192.168.1.'
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1)
+ substring(x,(abs(checksum(newid()))%15)+1,1),
CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
FROM sys.columns t1
cross join (select x='0123456789') a
cross join sys.columns t2
lets just review the data before grabbing totals.
SELECT s.*,
mt.*
FROM USERLOGIN s
INNER JOIN dbo.MinutesTableRoundUp() mt
ON DATEPART(mi, s.LogIn) = mt.[Minutes]
WHERE mt.fifteen = 15 --the 0-15 mintute grouping window
and DATEADD(dd, DATEDIFF(dd,0,s.LogIn), 0) = '2008-04-10'
AND DATEPART(hh, s.LogIn) = 12
and a simple count...we could also group by IP or something if needed.
SELECT COUNT(*) As TotalUsersInTimeWindow
FROM USERLOGIN s
INNER JOIN dbo.MinutesTableRoundUp() mt
ON DATEPART(mi, s.LogIn) = mt.[Minutes]
WHERE mt.fifteen = 15 --the 0-15 mintute grouping window
and DATEADD(dd, DATEDIFF(dd,0,s.LogIn), 0) = '2008-04-10'
AND DATEPART(hh, s.LogIn) = 12
SELECT COUNT(*) As TotalUsersInTimeWindow,
IPLoc
FROM USERLOGIN s
INNER JOIN dbo.MinutesTableRoundUp() mt
ON DATEPART(mi, s.LogIn) = mt.[Minutes]
WHERE mt.fifteen = 15 --the 0-15 mintute grouping window
and DATEADD(dd, DATEDIFF(dd,0,s.LogIn), 0) = '2008-04-10'
AND DATEPART(hh, s.LogIn) = 12
group by IPLoc
SELECT COUNT(*) As TotalUsersInTimeWindow,
UserID
FROM USERLOGIN s
INNER JOIN dbo.MinutesTableRoundUp() mt
ON DATEPART(mi, s.LogIn) = mt.[Minutes]
WHERE mt.fifteen = 15 --the 0-15 mintute grouping window
and DATEADD(dd, DATEDIFF(dd,0,s.LogIn), 0) = '2008-04-10'
AND DATEPART(hh, s.LogIn) = 12
group by UserID
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply