SharePoint Learning Kit Session Time

Topics: Developer Forum, Project Management Forum, User Forum
Jun 19, 2012 at 1:05 PM

Someone at the company I work for would like to be able to see how long an employee is spending on different training modules that we are trying to implement through SLK. I've seen things with SCORM in that it contains a session time value which sounds like what we want. First off, is it possible to have this value store as a column in Sharepoint or in the SLK? And if so, how?

Thanks for your help!

Jul 13, 2012 at 9:14 AM

Hi everyone,

I am facing the same question. Has anyone managed to find how to get the time spent by a student on different training modules?

Thanks in advance,


Jul 16, 2012 at 5:19 PM

Wow, I'm in the same boat. We really want to be able to report on the amount of time a user spends in any given training module.

I hope we get some suggestions,


Jul 23, 2012 at 12:37 PM

For all i know, in SLK you can get the difference between training course started time and finished time.
Table AttemptItem, Columns: StartedTimestamp, FinishedTimestamp

for example select in db SLK (look column exec_time in format: dd:hh:mi:ss:ms):

	asi.Title COURSE_NAME, 
	asi.CreatedBy INSTRUCTOR_ID,
	asi.PointsPossible POS_MARK,
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), asi.DateCreated), 104) DATE_SET, 
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), asi.DueDate), 104) DUE_DATE,
	CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), ati.StartedTimestamp), 104) COURSE_DATE_START, 
		WHEN FinishedTimestamp = CONVERT(date, '1753-01-01 04:00:00.000') THEN NULL 
		ELSE CONVERT(char, DateADD(ss, DATEDIFF(ss, GetUTCdate(), GETDATE()), ati.FinishedTimestamp), 104) 
		WHEN lai.FinalPoints IS NULL THEN ati.TotalPoints 
		ELSE lai.FinalPoints END GET_MARK, 
		WHEN IsFinal = 1 THEN +  RIGHT('0' + CONVERT(varchar(2), DATEDIFF(day, ati.StartedTimestamp, ati.FinishedTimestamp)), 2) + ':' + RIGHT('0' + CONVERT(varchar(6), DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp)/3600), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp) % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, ati.StartedTimestamp,ati.FinishedTimestamp) % 60), 2)
FROM  dbo.AssignmentItem asi  
	LEFT JOIN LearnerAssignmentItem lai 
	ON lai.AssignmentId = asi.Id 
	LEFT JOIN AttemptStatus ats  
	ON ats.Id = lai.NonELearningStatus 
	LEFT JOIN AttemptItem ati  
	ON ati.LearnerAssignmentId = lai.Id AND ati.RootActivityId = asi.RootActivityId 
	LEFT JOIN SuccessStatus ss 
	ON ss.Id = ati.SuccessStatus  
	LEFT JOIN CompletionStatus cs  
	ON cs.Id = ati.CompletionStatus 
	LEFT JOIN UserItem ui ON ui.Id = lai.LearnerId
Aug 10, 2012 at 9:59 AM

Hi Dimitry,

thanks for the query. However it actually shows the difference between start and time which is not always the correct time for a stundet spent on a test.

For example  In case a student starts a test, works on it for 10 minutes and closes it, and then the next day opens it again for 10 minutes and completes it, the difference between start and finish will be more then 24 hours,, when in fact it is 20 minutes.

I see a column TotalTime in the ActivityAttemptItem table, but the values I get are pretty strange. Have you used it in any case?



Aug 16, 2012 at 1:12 PM

Hi, mariaveledinova!

I don't use the column TotalTime because if the instructor will assign a course to himself, the value of this column always will be = 0.

But if you use only traditional scheme: instructor-learner, you can use it and as a result of your example totaltime value will be 20 minutes.

select in db SLK:

convert(int, totaltime / 1000 / 60 / 60 / 60 / 24 / 365) count_days, 
convert(int, totaltime / 1000 / 60 / 60 / 60 / 24) count_hours,
convert(int, totaltime / 1000 / 60 / 60 / 60) count_minutes,
convert(int, totaltime / 1000 / 60 / 60) %60 count_seconds
from ActivityAttemptItem