SQL Query - Get number of students logged into a course

SQL Query - Get number of students logged into a course

by Kieran Briggs -
Number of replies: 2

Hi All, 

I'm trying to display in a report the number of unique students who have logged in to a course in the last 14 days.  I'm fine getting a last logged in for the whole site, but I don't know where to start for a specific course.

any help would be fantastic.

Average of ratings: -
In reply to Kieran Briggs

Re: SQL Query - Get number of students logged into a course

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers

Hi,

you can try something like that in MYSQL

SELECT count(distinct(t1.userid))
  FROM mdl_logstore_standard_log  t1
             ,mdl_role_assignments          t2
 WHERE t1.action           = 'viewed'
      AND t1.objecttable  = 'course'
      AND t1.timecreated > UNIX_TIMESTAMP(now()) - (86400*14) -- only work in mysql probably
      AND t1.objectid        = ***MYCOURSEID***
      AND t2.userid           = t1.userid
      AND t2.contextid      = t1.contextid
      AND t2.roleid            = 5 --MY ROLEID 5 is student in your DB. it's better to join with the table role !


Hope it's help.

Dominique 

Average of ratings: Useful (1)