I ended up starting from scratch to create the query I was looking for. I eliminated the duplicates by using DISTINCT. Based on the existing data I had, this did return the correct number of items.
This query will return a list of all users that:
1. Have been enrolled as a STUDENT in a course more than 30 days, AND
2a. Have never logged in or accessed the course, OR
2b. Have not completed the course and obtained a score.
I have ignored the use of the context table to ensure that I was only looking at a course as our setup is only using SCORM bsed courses, so not sure if this will work for others.
SELECT DISTINCT
c.fullname Course,
u.lastname 'last name',
u.firstname 'first name',
u.idnumber 'ID number',
(DATE_FORMAT(FROM_UNIXTIME(ue.timemodified),'%Y-%m-%d')) 'Enrolled on',
IF (ul.timeaccess is NULL,'never',
DATE_FORMAT(FROM_UNIXTIME(ul.timeaccess),'%Y-%m-%d')) 'Last Accessed on',
(DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))) 'Days Enrolled'
FROM prefix_user_enrolments ue
JOIN prefix_user u ON u.id=ue.userid
JOIN prefix_enrol en ON en.id=ue.enrolid
JOIN prefix_course c ON c.id=en.courseid
LEFT JOIN prefix_user_lastaccess ul ON (ul.courseid=c.id AND ul.userid=ue.userid)
JOIN prefix_role_assignments ra ON ra.userid=ue.userid
JOIN prefix_role as r ON r.id=ra.roleid
JOIN prefix_grade_items gi ON gi.courseid=c.id
LEFT JOIN prefix_grade_grades gg ON (gg.userid=ue.userid AND gg.itemid=gi.courseid)
WHERE ue.status=0 AND (DATEDIFF(NOW(), FROM_UNIXTIME(ue.timecreated))>30) AND r.name='student' AND (gg.finalgrade is NULL OR gg.finalgrade = 0)
ORDER BY c.fullname, u.lastname, u.firstname
Currently this lists all courses. if you use this in Configurable Reports and add a filter for courses add then add the following on the line before ORDER BY.
%%FILTER_COURSES:c.id%%