Author: Gábor Zsolt Nagy

A report was needed by my client to list participants in a course with access info, so those who did not access the course can be warned to perform the mandatory quiz before closing it.

SELECT
 u.firstname,
 u.lastname,
 u.idnumber,
 c.shortname,
 IF(ue.status = 0, 'Active', 'Suspended') as status,
 e.enrol,
 FROM_UNIXTIME(ue.timecreated, '%m/%d/%Y %h:%i:%s %p') as enrollment_date,
 IF(ue.timestart = 0, 'No End Date',FROM_UNIXTIME(ue.timestart, '%m/%d/%Y %h:%i:%s %p')) as start_enrollment_date,
 IF(ue.timeend = 0, 'No End Date',FROM_UNIXTIME(ue.timeend, '%m/%d/%Y %h:%i:%s %p')) as end_enrollment_date,
 COALESCE((
 SELECT
 FROM_UNIXTIME(la.timeaccess,'%m/%d/%Y %h:%i:%s %p')
 FROM
 mdl_user_lastaccess as la
 WHERE
 u.id = la.userid
 AND
 c.id = la.courseid
 ), 'Never Accessed') as last_access,
 COALESCE((
 SELECT
 r.name
 FROM 
 mdl_role_assignments AS ra,
 mdl_context AS ctx,
 mdl_role as r
 WHERE 
 ctx.instanceid = c.id
 AND
 ra.contextid = ctx.id
 AND
 ra.userid = u.id
 AND
 r.id = ra.roleid
 AND
 (
 e.id = ra.itemid
 OR
 ra.itemid = 0
 )
 LIMIT
 1
 ), 'No Role Assigned') as role,
 (
 SELECT
 c2.shortname
 FROM
 mdl_course as c2
 WHERE
 e.customint1 = c2.id
 ) as child_course
FROM
 mdl_user_enrolments as ue,
 mdl_enrol as e,
 mdl_course as c,
 mdl_user as u
WHERE
 ue.userid = u.id
 AND
 ue.enrolid = e.id
 AND
 e.courseid = c.id
 AND
 c.id = 4
ORDER BY
 u.lastname,
 u.firstname,
 c.startdate DESC,
 c.shortname

Thanks for James Henestofel.
https://moodle.org/mod/forum/discuss.php?d=255646