Overview of amount or which users started scorm training

Overview of amount or which users started scorm training

by George Bosveld -
Number of replies: 8

Hi there,

I'm new to Moodle and it's been a few years since I had my basic SQL lessons, but I think the ad hoc database queries tool or the configurable reports tool would be the right tool for what I need.

I hope anyone can help me.

I'd like to get an overview of which users and/or how many users started a scorm training for the first time ever per month.
I guess the querie needs to filter the minimum/lowest date for the scorm launch activity before choosing a specified month, but I'm getting stuck with even setting up that bit of code.

I think the display should include the course name, Activity name and the amount of the first time ever launches by the users.

I hope anyone can give me some tips and tricks or get me going in the right direction.

Kind regards,

George Bosveld

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

I needs to be something like

SELECT c.name, sc.name, u.username,
        count(scs.id) AS num_starts
FROM {course} c
JOIN {scorm} sc ON sc.course = c.id
JOIN {scorm_something} scs ON ... some condition ...
JOIN {user} u ON u.id = scs.userid
WHERE ... some extra conditions ...
GROUP BY c.name, sc.name, u.username
ORDER BY c.name, sc.name, u.username

I am afraid that I don't know much about the SCORM module, so I cannot tell you the details you need to fill in the bits in italics.

Average of ratings: -
In reply to Tim Hunt

Re: Overview of amount or which users started scorm training

by George Bosveld -

Thank you for your reply.

I saw in the log that the action column shows a scorm launch action, could that be used as the item to pick the dates? something like WHERE action LIKE '%launch' ?

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by George Bosveld -

also I see in the table prefix_log that there is a certain timestamp, but not like I can erad it's a specific day/time, but ten numbers. I think this table with the user table should get me somehere, I see a.o. a timestamp, user id, action and the info shows which URL is launched (http etc direct link to where the scorm trainign is located I guess) (and the url column  i.e. says view.php?id=14)

Hmm I just realize, is time in this table a launch time or duration time?

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by George Bosveld -

right now I got all launches using this query:

SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d %H:%i:%S' ) , u.firstname, u.lastname, u.institution , sc.reference
FROM prefix_log l
JOIN prefix_user u ON l.userid = u.id
JOIN prefix_scorm sc ON l.course = sc.course
WHERE l.action LIKE 'launch'
ORDER BY l.time DESC


I tihnk I'm on the right track with this, is it possible to get like 2 drop down menus to choose a start and end date?


Thanks in advance smile

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by George Bosveld -

SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d %H:%i:%S' ) AS datum, CONCAT(u.firstname,' ',u.lastname) AS cursist, c.fullname AS Cursus, sc.reference AS training
FROM prefix_log l
JOIN prefix_user u ON l.userid = u.id
JOIN prefix_scorm sc ON l.course = sc.course
JOIN prefix_course c ON sc.course = c.id
WHERE l.action LIKE 'launch'
ORDER BY l.time DESC

 

is what I have now, I don't know how to get the first date for someone to launch a specific training in a course, can anyone help? selecting a MIN date needs to happen but I tried putting it in the first line but I didn't get it working

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by George Bosveld -

it seems when I have the scorm table connected (which i want for the scorm training name) I get the 3 results, I now see that the log doesn't state the scorm launch, merely that there is a launch in module #

Does anyone know how to get a connection?

The URL states "view.php?id=51", those IDs stay the same with each launch, but that ID # is not the same as the scorm's ID. is there a way to get the view.php's ID link linked to the id of the scorm training?

Average of ratings: -
In reply to George Bosveld

Re: Overview of amount or which users started scorm training

by Ron Meske -
Picture of Particularly helpful Moodlers

I believe the dates you are looking for are in the scoes_track table.  To link this table to a course you connect the following tables:

prefix_course, prefix_scorm, and prefix_scorm_scoes_track

course field id to scorm field course and scorm field id to scoes_track field scormid

I had done something similar to report all completed SCOs and the date of completion.  I documented it in the ad-hoc contibuted reports (http://docs.moodle.org/23/en/ad-hoc_contributed_reports#Site-wide_completed_SCORM_activites_by_Course_name).

Average of ratings: -
In reply to Ron Meske

Re: Overview of amount or which users started scorm training

by George Bosveld -

Hi Ron, thank you for your reply.

I found the table and got the following codes to show the first times users started a scorm training within a course (sorted by course-scorm and sorted by date)

 

Sorted by date:

SELECT DATE_FORMAT( FROM_UNIXTIME( sst.timemodified ) , '%Y/%m/%d %H:%i:%S' ) AS datum, CONCAT(u.firstname,' ',u.lastname) AS cursist, c.fullname AS Cursus, s.name AS ScormName, s.reference AS ScormPakket

FROM prefix_scorm_scoes_track sst
JOIN prefix_scorm s ON s.id = sst.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON sst.userid = u.id
JOIN prefix_role_assignments ra ON u.id = ra.userid

WHERE sst.element = 'x.start.time' AND
ra.roleid = '5'

GROUP BY CONCAT(u.firstname,' ',u.lastname), c.fullname, s.name, s.reference
ORDER BY sst.timemodified DESC

 

Sorted by training:

SELECT c.fullname AS Cursus, s.name AS ScormName, s.reference AS ScormPakket, CONCAT(u.firstname,' ',u.lastname) AS cursist, DATE_FORMAT( FROM_UNIXTIME( sst.timemodified ) , '%Y/%m/%d %H:%i:%S' ) AS datum

FROM prefix_scorm_scoes_track sst
JOIN prefix_scorm s ON s.id = sst.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON sst.userid = u.id
JOIN prefix_role_assignments ra ON u.id = ra.userid

WHERE sst.element = 'x.start.time' AND
ra.roleid = '5'

GROUP BY CONCAT(u.firstname,' ',u.lastname), c.fullname, s.name, s.reference
ORDER BY c.fullname, s.name, s.reference, sst.timemodified DESC

 

---

 

using the following query i tried to make a count of how many first attempt there were each month but this doesnt really work as it counts the total ever per course:

SELECT YEAR( FROM_UNIXTIME( sst.timemodified )) AS jaar, MONTH( FROM_UNIXTIME( sst.timemodified )) AS maand, c.fullname AS Cursus, s.name AS ScormName, s.reference AS ScormPakket, (SELECT COUNT(DISTINCT userid) FROM prefix_scorm_scoes_track WHERE sst.element = 'x.start.time' AND ra.roleid = '5') AS aantal

FROM prefix_scorm_scoes_track sst
JOIN prefix_scorm s ON s.id = sst.scormid
JOIN prefix_course c ON c.id = s.course
JOIN prefix_user u ON sst.userid = u.id
JOIN prefix_role_assignments ra ON u.id = ra.userid

GROUP BY u.id, c.fullname, s.name, s.reference

ORDER BY sst.timemodified DESC

 

I feel I should put that whole selection method within the counting part...

Average of ratings: -