I want list of user who has access the course in particular time duration, for this I have written the following SQL query can anyone tell me if this one is the best solution for my requirement.
SELECT COUNT(DISTINCT userid, courseid) FROM `mdl_logstore_standard_log` WHERE `eventname` LIKE '%course_viewed%' AND (`timecreated` BETWEEN 1609459200 AND 1617235199) And `userid` NOT IN (0,1) AND `courseid` != 1;
Maybe something like this:
SELECT u.username, c.shortname, FROM_UNIXTIME(la.timeaccess) AS last_access
FROM mdl_user_lastaccess la
INNER JOIN mdl_user u ON u.id = la.userid
INNER JOIN mdl_course c ON c.id = la.courseid
WHERE c.id = 4 AND
u.id NOT IN (0, 1) AND
timeaccess BETWEEN 1609459200 AND 1617235199;
I based the above on this query. Change or remove the c.id = 4 course ID as needed.
Hi Leon,
the last access can not be used here because it only gets the last access and not the access within a time range.
I think without an additional access log table the Moodle log is the only way to get this information. But you have to make sure the logs are still available and not deleted through the rotation.
Best regards
Andreas
Since you already search for distinct userid, you can drop the eventname parameter and just keep the courseid. This will save time by removing the need to search within the eventname field.
You can also group the results by the course id.
SELECT courseid, COUNT(DISTINCT userid) AS users
FROM `mdl_logstore_standard_log`
WHERE (`timecreated` BETWEEN 1609459200 AND 1617235199)
AND `userid` NOT IN (0,1) AND `courseid` != 1
GROUP BY courseid;
