Thanks for the info Dwayne, I get that and I agree with you 100%.
I was still required to do a something to track something yet. What I decided to do was create a new table and run a script every night to export all of the 'LOGIN_SUCCESS' records from the dnn_EventLog table and all of the records with a valid userid and tabid in the dnn_sitelog table into a tblSiteHistory table.
I created a stored procedure which parses through the data and grabs each login and finds the last page log between before the next page login. Then I calculate the amount of time between those two times and display minutes active. I know it doesn't give perfect numbers, but it does give us an idea of the amount of time the user was on our website. From login to last page request.
Here's the Stored Procedure. It seems to work fairly well. If anyone has any comments, suggestions please let me know. It runs slowly for myself (admin users) because of the amount of records it goes through, but it's pretty quick with your standard web user.
CREATE PROCEDURE spGetUserLogHistory
@StartDate datetime,
@EndDate datetime ,
@UserId int = -1
AS
Select sh.Userid, 'LoginDate' = sh.LogDate,
'LastName' = ltrim(u.FirstName), 'FirstName' = ltrim(u.LastName),
'LastUserActivty' = max(sh2.LogDate),
'TimeElapsed' = (datepart(hour, (max(sh2.LogDate) - sh.LogDate)) * 60) + datepart(minute, (max(sh2.LogDate) - sh.LogDate)) + 1
from tblSiteHistory sh inner join
tblSiteHistory sh2 on sh.Userid = sh2.Userid inner join
dnn_users u on u.UserId = sh.Userid
where
--Get by UserId or All Users by default
(sh.UserId = @UserId or @UserId = -1)
--make sure you grab the login Records with this table
and sh.isLoginRecord = 1
--Grab records that aren't Login Records
and sh2.IsLoginRecord = 0
-- Date Range Filter
and sh.LogDate >= @StartDate
and sh.LogDate < @EndDate
-- find the last log before the next login records, if null get last record before timeout (today)
and sh2.LogDate < isnull((Select top 1 cast(str(datepart(year, sh3.LogDate)) + '-'
+ str(datepart(month, sh3.LogDate)) + '-'
+ str(datepart(day, sh3.LogDate)) + ' '
+ str(datepart(hour, sh3.LogDate)) + ':'
+ str(datepart(minute, sh3.LogDate) ) as datetime)
from tblSiteHistory sh3
where sh3.LogDate > sh.LogDate
and sh3.isLoginRecord = 1
and sh3.UserId = sh.UserId
order by sh3.LogDate), dateadd(day, 1,getdate()))
--make sure max logdate is greater than login date
and sh2.LogDate > sh.LogDate
--make sure logdates are the same date
and Datepart(year, sh.LogDate) = DatePart(year, sh2.LogDate)
and Datepart(month, sh.LogDate) = DatePart(month, sh2.LogDate)
and Datepart(day, sh.LogDate) = DatePart(day, sh2.LogDate)
group by sh.Userid, u.FirstName, u.LastName, sh.HistoryId, sh.LogGUID, sh.LogDate
order by ltrim(u.LastName), ltrim(u.FirstName), sh.LogDate
GO