Moodle MySQL Queries

Here are some Moodle MySQL Queries that are useful for generating activity statistics:

From http://blog.weber.k12.ut.us/jreeve/some-simple-mysql-queries-for-moodle/

Find the most popular activities:

SELECT COUNT(l.id) hits, moduleFROM mdl_log lWHERE module != 'login' AND module != 'course' AND module != 'role'GROUP BY moduleORDER BY hits DESC

Find the most active users over the past 7 days
(change the “604800″ to the number of the appropriate number of seconds if you want to adjust this interval):

SELECT COUNT(l.id) hits, l.userid, u.username, u.firstname, u.lastnameFROM mdl_log l INNER JOIN mdl_user u ON l.userid = u.idWHERE l.time > UNIX_TIMESTAMP(NOW()) - 604800GROUP BY l.useridORDER BY hits DESC

Find the most active courses:
(You may need to change the second line to FROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id AND c.id != ‘1′ to omit home page hits)

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursenameFROM mdl_log l INNER JOIN mdl_course c ON l.course = c.idGROUP BY courseIdORDER BY hits DESC

Some custom written ones:

Find the number of resources per course:

SELECT COUNT(l.id) count, l.course, c.fullname coursenameFROM mdl_resource l INNER JOIN mdl_course c on l.course = c.idGROUP BY courseORDER BY count DESC