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 COUNTCOUNT(l.id) hits, module
FROMmoduleFROM mdl_log l
WHERElWHERE module != ‘login’'login' AND module != ‘course’'course' AND module != ‘role’
'role'GROUP BY module
ORDERmoduleORDER 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 COUNTCOUNT(l.id) hits, l.userid, u.username, u.firstname, u.lastname
FROMlastnameFROM mdl_log l INNER JOIN mdl_user u ON l.userid = u.id
WHEREidWHERE l.time > UNIX_TIMESTAMP(NOWNOW()) –- 604800
GROUP604800GROUP BY l.userid
ORDERuseridORDER 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 COUNTCOUNT(l.id) hits, l.course courseId, c.fullname coursename
FROMcoursenameFROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id
GROUPidGROUP BY courseId
ORDERcourseIdORDER BY hits DESC

Some SSCcustom written ones:

Find the number of resources per course:

SELECT COUNTCOUNT(l.id) count, l.course, c.fullname coursename
FROMcoursenameFROM mdl_resource l INNER JOIN mdl_course c on l.course = c.id
GROUPidGROUP BY course
ORDERcourseORDER BY count DESC