I occasionally use the following SQL statements to clean-up our Moodle system and identify old courses. I recommend backing up your database before using any and knowing enough SQL to understand what they are doing before running them. I usually use phpMyAdmin for execution and export, but use what ever works best for you.
Find Empty Moodle Courses
If you use an auto-enrolment module you will often find you have lots of courses created that are never used. We have used the LDAP and Database enrolment before and both have created large numbers of courses. Some are not used by teachers,others are just used as child courses for year wide meta courses. Either way at the end of the year you may wish to identify any class that hasn’t been used. (Perhaps to delete using the Bulk Deletion options).
Following SQL code I’ve written will generate a list containing a sum of most of the resources and activities a course has, allowing you to quickly identify those with little to no content. It bases this count on having no labels, resources, assignments, etc.
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount FROM mdl_course m LEFT JOIN ( SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount FROM ( SELECT course, count( * ) AS subcount FROM mdl_resource GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_quiz GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_assignment GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_survey GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_label GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_glossary GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_homework GROUP BY course UNION ALL SELECT course, count( * ) AS subcount FROM mdl_wiki GROUP BY course ) AS courseCount GROUP BY courseCount.course ) AS cCount ON cCount.course = m.id
You may need to edit above code if you do not have an activity installed (eg homework).
The following code generates a list of all your courses together with how many students are enrolled in each. Useful to find out if you have any courses with no one enrolled.
SELECT cr.shortname, cr.fullname, count( ra.id ) AS enrolled FROM `mdl_course` cr JOIN `mdl_context` ct ON ( ct.instanceid = cr.id ) LEFT JOIN `mdl_role_assignments` ra ON ( ra.contextid = ct.id ) WHERE ct.contextlevel =50 GROUP BY cr.shortname, cr.fullname ORDER BY `enrolled` ASC
You can export either of the above queries into Excel and manipulate it from there. Order by the counts then copy the short names’ of courses to delete, paste into a text file and upload to the Bulk Course Deletion addon. Goodbye excess courses.
Data Cleanup: Roles without Users
Sometimes when a user gets deleted Moodle doesn’t clean up after it self as well as it should. The following code will list all the rows in your role assignments table that no longer match to a user:
SELECT * FROM `mdl_role_assignments` WHERE `userid` NOT IN ( SELECT id FROM mdl_user )
If you wish to the delete all of these just run following. As always make sure you have a good backup before deleting anything from DB.
DELETE FROM `mdl_role_assignments` WHERE `userid` NOT IN ( SELECT id FROM mdl_user )
Thanks, these were helpful!
You ought to state the SQL server and version of said server you are using, because some of these statements look like they have specific SQL server requirements. (e.g. sub-selects not available in MySQL < 4.1)
It may be moot from the POV of MySQL (because the latest Moodle requires a MySQL version that can do this stuff) but you never know.
Thanks a lot for those useful queries!!
How to find out who created the course and who are the trainers?
Hey thanks for this – I made a function to get all enrolled users using your info:
I’m glad they could be of use.
How do I select Distinct quiz attempt for each user with time start and time finish and duration