Moodle Administrators – Here are most Useful Moodle SQL Queries

Moodle Administrators are often tasked to get different reports out of Moodle. Any admin user with good SQL knowledge can dive in to Moodle Database to extract different reports out of Moodle database. However you need to understand how to write the SQL queries for Moodle database.

This is the best collection of SQL queries which can be utilized to make custom reports on your Moodle site. Few of the most useful SQL queries are:

Student (user) COUNT in each Course

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS USER ON USER.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5
# AND course.fullname LIKE '%2013%'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC

Most popular ACTIVITY

SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC

Courses without Teachers

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
FROM prefix_course AS c
ORDER BY Teachers ASC

Monthly Usage by Role

SELECT month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month, year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year, prefix_role.name as user_role, COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users FROM prefix_stats_user_monthly Inner Join prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid Inner Join prefix_context ON prefix_role_assignments.contextid = prefix_context.id Inner Join prefix_role ON prefix_role_assignments.roleid = prefix_role.id WHERE prefix_context.contextlevel = 50 AND `prefix_stats_user_monthly`.`stattype` = 'activity' AND prefix_stats_user_monthly.courseid <>1 GROUP BY month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_stats_user_monthly.stattype, prefix_role.name ORDER BY year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_role.name

Find PDF Files in Moodle

SELECT

    prefix_files.filename AS PDF,

    prefix_course.fullname AS Course,

CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=', prefix_course.id,'">link</a>') AS 'Link To Course',

   prefix_course_modules.course     AS 'Course id'

FROM prefix_files

INNER JOIN prefix_context 

ON prefix_files.contextid = prefix_context.id

INNER JOIN prefix_course_modules

ON prefix_context.instanceid = prefix_course_modules.id

INNER JOIN prefix_course

ON prefix_course_modules.course = prefix_course.id

WHERE (prefix_files.filename LIKE '%pdf%')

GROUP BY prefix_course.id

Here is a nice presentation from MoodleMoot by Amit Vij, Kulari Lokuge, Prateek Jindal from Monash University. You can refer to this presentation to design your own SQL queries. Check out the presentation here.

You can also use the popular report plugins like:

• Ad-hoc database queries : https://moodle.org/plugins/view/report_customsql
• Configurable Reports : https://moodle.org/plugins/view/block_configurable_reports