You can re-use these queries directly in PHPMyAdmin, command line, or using the excellent configurable reports plugin.

Moodle URLs 

Most queries will need information that is found in Moodle web page addresses (URL, or Unique Resource Locator) so it’s important that you understand what to look for. Each page has a unique identifier (or URL), which can be found by either hovering your mouse over the resource/activity you’re interested in (URL should show up at the bottom of your window), or by simply visiting the page and checking the address in your web browser address bar. We’re particularly interested in the ID numbers.

Hover your mouse over name	Visit the activity

Examples of Moodle URL’s

Unix Epoch times

Moodle stores all time related data in the database as Unix Epoch time, or the number of seconds that have elapsed since 1st January 1970 at midnight UTC. For example, if a student submitted an assignment on 28th April 2013 at 6.59 am GMT time, then Moodle would store the time stamp for this event as 1367132340 in the database.

You can find out how to convert Epoch time into human readable date/time by looking at the ‘Number of hits per student, per day’ query below, or by using this online converter.

SQL queries

Here are some of the queries I have used. I am very much a beginner with SQL so keep in mind I might not have adhered to best practices. In all queries, change ‘prefix_’ with the actual prefix for your Moodle database (more often than not, it will be ‘mdl_’) – leave as is if using the configurable reports plugin. You shouldn’t be able to break anything using this but don’t come and blame me if you do.

Number of hits in a course

This MySQL query adds up how many clicks to activity/resources have been recorded in the logs for a specific course. 

SELECT course, COUNT(*) AS hits
FROM `prefix_log`
WHERE `course` = 1693
AND `userid` != 782
GROUP BY `course`
ORDER BY `hits` DESC

Change the course ID to suit your needs. In this example, I have removed clicks performed by myself (userid 782) as to not skew the results. You can find your own user ID by visiting your profile and checking the URL.

Number of hits per student

This query calculates the number of hits each student has performed in a given course for a specific period of time, and orders the results in decreasing number of hits.

SELECT u.firstname, u.lastname, COUNT(*) AS hits
FROM prefix_log l
LEFT JOIN prefix_user u ON l.userid=u.id
WHERE `course` = 1693 
AND `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782
GROUP BY `userid`
ORDER BY hits DESC

To customise, modify ‘course’ and ‘userid’, as explained for the query above. You can also change the Epoch values to adjust the period of time you are interested in.

Number of hits per student, per day

This query calculates the number of hits students have performed each day in a given course. The results are ordered chronologically. 

SELECT u.id, u.firstname, u.lastname, DATE(FROM_UNIXTIME(`time`)) AS `Date`, COUNT(*) AS hits 
FROM prefix_log l
LEFT JOIN prefix_user u ON l.userid=u.id
WHERE `course` = 1693
AND `userid` != 782
GROUP BY `userid`, DATE(FROM_UNIXTIME(`time`))
ORDER BY `Date`

Number of visits to resources/activities

Calculates how many times all resources & activities have been visited in a given course for a specific period of time.

SELECT COUNT(cmid) AS hits, cmid, module
FROM prefix_log 
WHERE `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782 
AND `course` = 1693 
GROUP BY `cmid` 
ORDER BY cmid ASC

To customise, see ‘Number of hits per student’ query above.

Number of visitors to resources/activities

Works out how many students have visited resources & activities at least once in a given course, for a specific period of time.

SELECT COUNT(Distinct userid) AS hits, cmid, module 
FROM prefix_log
WHERE `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782 
AND `course` = 1693
GROUP BY `cmid`
ORDER BY cmid ASC

To customise, see ‘Number of hits per student’ query above.

Number of hits during lessons

This query works out the number of hits performed during lesson time. I can then subtract it from the ‘Hits in a course’ query to find out the ratio of hits in/outside lessons.

SELECT course, COUNT(*) AS hits
FROM `prefix_log` 
WHERE (
(`time` BETWEEN 1363923300 AND 1363926600)
OR (`time` BETWEEN 1364190300 AND 1364196900)
OR (`time` BETWEEN 1364362800 AND 1364366400)
OR (`time` BETWEEN 1365564000 AND 1365567000)
OR (`time` BETWEEN 1365658800 AND 1365665700)
OR (`time` BETWEEN 1366004400 AND 1366008000)
OR (`time` BETWEEN 1366255800 AND 1366259400)
OR (`time` BETWEEN 1366350000 AND 1366356900)
OR (`time` BETWEEN 1366695600 AND 1366699200)
OR (`time` BETWEEN 1366687800 AND 1366691400)
)
AND `userid` != 782
AND `course` = 1693
GROUP BY `course`

Note: this query could possibly be simplified if your lesson times are always the same every week. My timetable is an absolute mess, with a 6 day cycle with Wednesdays following a different schedule.

Badges awarded to each student

This query works out what badges have been unlocked by every student in a given course. As it is a rather long query, I have attached it to a text file, that you can download here.  Here is a simplified sample:

SELECT 
userid,
case BADGE_1
when 3 then 'y'
else '-'
END as BADGE_1,
case BADGE_2
when 1 then 'y'
else '-'
END as BADGE_2
from
(SELECT 
userid,
sum(col14415) + sum(col14416) + sum(col14417) as BADGE_1,
sum(col14413) as BADGE_2
from
(SELECT 
userid,
case coursemoduleid
when 14413 then 1
else 0
end as col14413,
case coursemoduleid
when 14415 then 1
else 0
end as col14415,
case coursemoduleid
when 14416 then 1
else 0
end as col14416,
case coursemoduleid
when 14417 then 1
else 0
end as col14417
FROM
mdl_course_modules_completion
WHERE
completionstate = 1
GROUP BY userid , coursemoduleid) t
GROUP BY userid) t2
WHERE
userid BETWEEN 1223 AND 1243
ORDER BY userid ASC

I might create a short video showing how to customise this query, if there is interest. However, if you take a look at the attached file along with this sample, you should be able to work out what to do to make it suit your needs.

Note: It takes a long time to set up.

Activity has been completed, but student has not marked it complete

This query finds all of the resources & activities that have been visited by a student, but have not been marked as actually completed by the student. A student might not have marked the resource/activity as completed for several reasons, such as forgetting to do it, not feeling confident he/she has understood the contents and would like to revisit,etc. The query creates a clickable link to the resource to help you judge the possible reason(s).

SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, l.module AS type, l.course, CONCAT('<a href="%%WWWROOT%%/mod/',l.module,'/',l.url,'" target="_new" class="broken_link">',l.cmid,'</a>') AS module
FROM 
prefix_log l
LEFT JOIN prefix_user u ON u.id = l.userid
WHERE NOT EXISTS
(
SELECTNULL
FROM prefix_course_modules_completion c
WHERE l.cmid = c.coursemoduleid 
AND c.userid = l.userid
)
AND l.course = 1693
AND l.userid !=782
AND l.cmid !=0
AND l.module !="data"
AND l.module !="checklist"
GROUP BY l.userid
ORDER BY l.course ASC, u.firstname

Due to the way I have set up the conditions, it is possible that a student has visited the ‘database’ and/or the ‘checklist’ activities and the system has not yet marked it as completed. For this reason, I have removed it from the list of modules to be evaluated (e.g. l.module !="data")

Activity has not been visited, but student has marked it done

This query works out the opposite of the one above. Sometimes students might mark an activity as completed, even though they have not visited it. This query works out just that.

SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, concat('<a href="%%WWWROOT%%/mod/',p.name,'/view.php?id=',c.coursemoduleid,'" target="_new" class="broken_link">',c.coursemoduleid,'</a>') AS module, p.name AS type, m.course
FROM 
prefix_course_modules_completion c
LEFT JOIN prefix_course_modules m ON c.coursemoduleid = m.id
LEFT JOIN prefix_user u ON c.userid = u.id
LEFT JOIN prefix_modules p ON m.module = p.id
WHERE NOT EXISTS
(
SELECTNULL
FROM prefix_log l
WHERE c.coursemoduleid = l.cmid
AND c.userid = l.userid
)
AND m.module != 9
AND m.course = 1693
AND c.completionstate = 1
ORDER BY c.userid

I have removed the ‘label’ module type from this query as it may cause problems, as a label can be marked as complete but it is impossible for a student to visit it (i.e. click on it and open a specific page for it). I probably should also be removing the ‘turnitintool’ type as well but have not done it yet.

You can find more useful Moodle SQL queries here.