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.nameFind 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
