Note: You are currently viewing documentation for Moodle 3.11. Up-to-date documentation for the latest stable version of Moodle may be available here: ad-hoc contributed reports.

ad-hoc contributed reports

From MoodleDocs

Introduction

Note: The Moodle Docs wiki software was upgraded substantially in July 2021 and required all query code to be updated for language syntax presentation. I have done that and taken the opportunity to make some updates and cleaning up on this page. For more details, please see the discussion on Upgrade of Moodle docs and Ad-hoc reports page. Thanks, Randy.

Licensing and Disclaimer

Disclaimer:

All queries on this page are provided as is. You are completely responsible for using them and for any and all effects they may have on your site, including any data loss. In the standard wording of the GNU license that accompanies all Moodle code, all queries and content here are:

distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Precautions:

  • Backup your database before using queries
  • Never use a query on a production site, only on a test site
  • If you don't know what you are doing, then don't do it


Licensing:

All queries and content here are property of their original copyright holders and are licensed here under the GNU General Public License.

Moodle Report Plugins

There are two plugins commonly used in Moodle for making and viewing SQL reports:


The discussion forum dedicated to these plugins and how to make and use queries with them is located here: Configurable reports block (plugin). You will also find more information in the Analytics and reporting forum.

You can install and use either of these plugins into your Moodle site for reports. If you are more focused on emailing reports, first look at the Ad-hoc database queries. If you need to share reports with teachers and students in courses, then look at the Configurable Reports plugin.

Some Technical Notes for Report Users

Table Names and prefix_

You will see many of the queries below use "prefix_" to start table names, e.g. prefix_user. This prefix_ is used before table names when you are using either of the two plugins, Ad-hoc reports or Contributed reports. These two plugins replace prefix_ with the actual prefix set in your Moodle configuration config.php file. This is because while the default table prefix is mdl_, when you install Moodle you can designate any prefix you want, so it is customizable.

Queries written to be used with either of those two plugins should always have prefix_ to begin all table names instead of the actual prefix.

When you use tools outside those two Moodle plugins, such as PhpMyAdmin, Workbench, etc., you have to use the actual table names as they are in the database including the actual prefix set for your own site.

Therefore:

  • If a query below has "prefix_" in its table names and you want to use that query in a tool such as phpMyAdmin, you will need to replace those with the actual prefix of your site's tables.
  • If a query below has "mdl_" or some other prefix in its table names, and you are using that in either of the two Moodle plugins, you will need to replace those explicit prefixes with "prefix_".


You will receive a "No explicit prefix" error in either of those plugins if you use code with the actual table prefix in it.

About %%FILTER_ and similar Variables

You will see a number of queries with variables in them that start and end with two %% percentage signs, e.g. %%WWWROOT%% and several of them that start with %%FILTER_. Theses variables are specific to one or both of the two Moodle plugins. They are not standard SQL code and will not work in code done in other tools outside of those two plugins.

In addition, while these two plugins do share some of the variable, some only work in one plugin or the other. For example, %%WWWROOT%% works in both; the %%C%%, %%S%% and %%Q%% variables only work in the Ad-hoc database queries plugin; and those starting %%FILTER_ only work in the Configurable Reports plugin. You will receive a syntax error if you use a variable that the plugin does not support.

Find more details in the section on Variables below.

About Date and Time Formatting

Dates and times in Moodle are stored internally in the database as the Unix Epoch timestamp which is the number of seconds since January 1, 1970 at 00:00 UTC, which in the database will be represented as 0.

To convert them to "human readable" formats we use everyday, you will need to do that in your query with code. There are numerous examples in the reports on this page: a search of this page for "FROM_UNIXTIME" will show you how to use and convert the dates. Note that when times are converted into standard date and times from timestamps are in UTC format and you may want to convert those to a local time zone.

The functions FROM_UNIXTIME and DATE_FORMAT that are common in the reports below are MySQL specific functions, which you can use in MySQL and MariaDB sites.

For other version of SQL, you will need to edit such queries to get them to work properly. For Postgres, see the manual page on Data Type Formatting Functions. There are a number of examples specific to Postgres among the queries below. For MSSQL, see this documentation at How to Return the Unix Timestamp in SQL Server. For Oracle, call Larry Ellison on his yacht and ask why he doesn't have a rocket yet like all the other vainglorious billionaires.

There are many date and time formats used in various countries, but all can be formatted based on the original timestamp value. For a sample in MySQL, you can use the following query to show you the install date of your Moodle site in several formats:

SELECT
l.timecreated AS "Timestamp",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d/%m/%Y') AS "Common world format",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%m/%d/%Y') AS "US format",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%Y-%m-%d') AS "ISO standard format",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%Y-%m-%d %H:%i') AS "ISO standard format with 24 hour time"
FROM prefix_logstore_standard_log l
WHERE l.id =1

See the MySQL documentation for all the possible date formatting options.

Note: for timestamps that are 0, this sometimes represents "Never" or "No date" for some fields in Moodle. For example, if a user's first login is 0, that means they have never logged in at all, not that their first log in was January 1, 1970 (since Moodle did not exist yet). If you want to distinguish such dates in your output, you can use a conditional to test for the 0, for example:

IF(u.firstaccess = 0, "Never",DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d %H:%i')) AS 'FirstAccess',

which will show "Never" when the timestamp is 0 and the properly formatted date otherwise, allowing you to sort the column easily.

Standard Logs and Legacy Logs

The log table is the where events in the system are stored. It is a key table for certain types of reports, especially anything related to system events like logins, accessing pages, creating and deleting objects, changing settings, etc.

Moodle made a major update to the log system in Moodle 2.7 in 2014. The older log format using the table prefix_log are called "Legacy logs" while the current logstore system are called "Standard" logs. Unless your Moodle site was running such logs in the 2.7 and before era, and you for some reason chose not to update to the new log format, then you will be using the new log system, with a log table prefix_logstore_standard_log.

Use Standard Log Reports

Use the reports in the Standard Log Reports section below for current versions of Moodle unless you are sure you have ancient log data from very old Moodles in your system for some reason.

Since the legacy log table still exists in newer versions of Moodle, these Legacy queries may actually run but will not return results in later versions of Moodle using the new logstore system. You can check this for yourself with the following query on your site, which will show you the 0 count for the empty Legacy log table compared with your active Standard log table:

SELECT 
(SELECT COUNT(*) FROM prefix_log) AS "Legacy_log",
COUNT(*) AS "Standard log"
FROM prefix_logstore_standard_log
Finding Old Legacy Log Reports

If you wish to find and use queries from this old system, you should search in the Ad-hoc pages for earlier versions of Moodle to find such reports, which will use the table prefix_log or mdl_log. Reports from Moodle 2.0 to 3.10 will contain such queries.

All these old queries have been removed from the pages for Moodle 3.11 and newer. If you are the author of one of these old queries, please consider updating your query for current Moodle logstore system and move it into the main Log Reports section of the current Moodle version. Thanks.

Suggestions for Contributors

Everyone is free to contribute to this page. If you have working queries that you think would be of use to community members, please share them here. The following suggestions would make your contributions more useful.

Versioning

  • Only post queries verified to work in the current version of this page for its Moodle version: all Moodle docs pages are specific to one version of Moodle and are rolled over twice yearly at new releases in May and November
  • Please do not post versions of queries for old, out-of-support versions (if you want to, use the version page specific to that version, not the current one)
  • Please remove any query you have written that no longer works in the current version of Moodle: especially in cases of structural changes to tables that render old queries broken (e.g. changes in the Logs in 2.7, Messaging in 3.6, etc.)

Adding annotations for others

  • If you are using a SQL dialect other than MySQL, please note which dialect it is and any specific functions or other quirks present
  • Please consider putting your name and/or a link as "Contributed by"
  • Please consider adding a short description of what your report does and its expected output
  • If the code includes any non-default, third party plugins, please specifically note that in your description
  • If your code includes any domain names, course or user names, or other references specific to your site, consider making those more generic
  • Please use English since this is the English version of the Moodle documentation
  • If there is a discussion or other information about the query from a forum discussion, feel free to link to that discussion

Commonly used table aliases

Over the years, some common conventions have arisen for table aliases. You will see these used often on these reports.

If you use these aliases, then your queries will be easier for others to read. But of course, any legal alias can be used.

  • c = prefix_course
  • cat or cc = prefix_course_categories
  • cm = prefix_course_modules
  • ctx or con = prefix_context
  • e = prefix_enrol
  • f = prefix_files
  • l or log = prefix_logstore_standard_log
  • m = prefix_module
  • r = prefix_role
  • ra = prefix_role_assignments
  • u = prefix_user
  • ue = prefix_user_enrolments

Other Useful Things

Please see the sections at the end of this page for other useful things including the database schema, commonly used snippets of code, the Config Reports variables and filters, and other nice things.



The REPORTS

USER and ENROLMENT Reports

Count number of distinct learners and teachers enrolled per category (including all its sub categories)

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) as teachers
FROM prefix_course AS c #, mdl_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments  AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments  AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE c.category = cats.id
AND (
	cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID with the category id you want to count (eg: 80)
	OR cats.path LIKE '%/CATEGORYID'
	)
AND lra.roleid=5
AND tra.roleid=3

Student (user) Count in each Course

Including (optional) filter by: year (if included in course fullname).

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

Enrolment count in each Course

Shows the total number of enroled users of all roles in each course. Sorted by course name.

SELECT c.fullname, COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
GROUP BY c.id
ORDER BY c.fullname

List of all site users by course enrollment (Moodle 2.x)

SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) as Role
,(SELECT name FROM prefix_role WHERE id=en.roleid) as RoleName

FROM prefix_course as course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id

Enrolled users,which did not login into the Course, even once (Moodle 2)

Designed forMoodle 2 table structure and uses special plugin filter : %%FILTER_SEARCHTEXT:table.field%%

SELECT
user2.id as ID,
ul.timeaccess,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
user2.idnumber AS IDNumber,
user2.phone1 AS Phone,
user2.institution AS Institution,

IF (user2.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess

,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id and courseid=c.id) as CourseLastAccess

,(SELECT r.name
FROM  prefix_user_enrolments AS uenrol
JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
JOIN prefix_role AS r ON e.id = r.id
WHERE uenrol.userid=user2.id and e.courseid = c.id) AS RoleName

FROM prefix_user_enrolments as ue
JOIN prefix_enrol as e on e.id = ue.enrolid
JOIN prefix_course as c ON c.id = e.courseid
JOIN prefix_user as user2 ON user2 .id = ue.userid
LEFT JOIN prefix_user_lastaccess as ul on ul.userid = user2.id
WHERE c.id=16 AND ul.timeaccess IS NULL
%%FILTER_SEARCHTEXT:user2.firstname%%

Enrolled users who have never accessed a given course (simpler version)

SELECT username, firstname, lastname, idnumber
FROM prefix_user_enrolments ue
JOIN prefix_enrol en ON ue.enrolid = en.id
JOIN prefix_user uu ON uu.id = ue.userid
WHERE en.courseid = 123456
AND NOT EXISTS (
    SELECT * FROM prefix_user_lastaccess la
    WHERE la.userid = ue.userid
    AND la.courseid = en.courseid
)

(Replace 123456 near the middle with your courseid)

Lists "loggedin users" from the last 120 days

SELECT id,username,FROM_UNIXTIME(`lastlogin`) as days
FROM `prefix_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120

and user count for that same population:

SELECT COUNT(id) as Users  FROM `prefix_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120

Users loggedin within the last 7 days

SELECT
    l.* FROM mdl_logstore_standard_log l
WHERE
   l.eventname = '\\core\\event\\user_loggedin'
   AND FROM_UNIXTIME(l.timecreated, '%Y-%m-%d') >= DATE_SUB(NOW(), INTERVAL 7 DAY)

SELECT l.eventname FROM mdl_logstore_standard_log l
GROUP BY l.eventname

Lists the users who have only logged into the site once

SELECT id, username, firstname, lastname, idnumber
FROM prefix_user
WHERE prefix_user.deleted = 0
AND prefix_user.lastlogin = 0
AND prefix_user.lastaccess > 0

Log in and Log out history complete for a specific user

Contributed by: Randy Thornton

This query uses the logs to show the complete login and logout history for a particular user. You can use it as the basis for further refining the report. Replace the ## in the WHERE clause below with the id number of the user you wish to see. Warning: as always with queries from the logs, this can take a long time to run and may return more data that the maximum limit allowed.

SELECT
l.id AS "Log_event_id",
l.timecreated AS "Timestamp",
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%Y-%m-%d %H:%i') AS "Time_UTC",
l.action,
u.username,
l.origin,
l.ip
FROM prefix_logstore_standard_log l
JOIN prefix_user u ON u.id = l.userid
WHERE l.action IN ('loggedin','loggedout')
AND l.userid = ##
ORDER BY l.timecreated

Students in all courses of some institute

What is the status (deleted or not) of all Students (roleid = 5) in all courses of some Institute

SELECT c.id, c.fullname, u.firstname, u.lastname, u.deleted
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND u.institution = 'please enter school name here'

Full User info (for deleted users)

Including extra custom profile fields (from prefix_user_info_data)

SELECT *
FROM prefix_user as u
JOIN prefix_user_info_data as uid ON uid.userid = u.id
JOIN prefix_user_info_field as uif ON (uid.fieldid = uif.id AND uif.shortname = 'class')
WHERE `deleted` = "1" and `institution`="your school name" and `department` = "your department" and `data` = "class level and number"

User's courses

change "u.id = 2" with a new user id

SELECT u.firstname, u.lastname, c.id, c.fullname
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE u.id = 2

List Users with extra info (email) in current course

blocks/configurable_reports replaces %%COURSEID%% with course id.

SELECT u.firstname, u.lastname, u.email
FROM prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
JOIN prefix_user AS u ON u.id = ra.userid

List Students with enrollment and completion dates in current course

This is meant to be a "global" report in Configurable Reports containing the following: firstname, lastname, idnumber, institution, department, email, student enrolment date, student completion date Note: for PGSQL, use to_timestamp() instead of FROM_UNIXTIME() Contributed by Elizabeth Dalton, Moodle HQ

SELECT
u.firstname
, u.lastname
, u.idnumber
, u.institution
, u.department
, u.email
, FROM_UNIXTIME(cc.timeenrolled)
, FROM_UNIXTIME(cc.timecompleted)

FROM prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_completions AS cc ON cc.course = c.id AND cc.userid = u.id

List of users who have been enrolled for more than 4 weeks

For Moodle 2.2, by Isuru Madushanka Weerarathna

SELECT uenr.userid As User, IF(enr.courseid=uenr.courseid ,'Y','N') As Enrolled,
IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') As EnrolledMoreThan4Weeks
FROM prefix_enrol As enr, prefix_user_enrolments AS uenr
WHERE enr.id = uenr.enrolid AND enr.status = uenr.status

User's accumulative time spent in course

A sum up of the time delta between logstore_standard_log user's records, considering the a 2-hour session limit.

Uses: current user's id %%USERID%% and current course's id %%COURSEID%%

And also using a date filter (which can be ignored)

The extra "User" field is used as a dummy field for the Line chart Series field, in which I use X=id, Series=Type, Y=delta.

SELECT
l.id,
l.timecreated,
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y') AS dTime,
@prevtime := (SELECT max(timecreated) FROM mdl_logstore_standard_log
		WHERE userid = %%USERID%% and id < l.id ORDER BY id ASC LIMIT 1) AS prev_time,
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
l.timecreated-@prevtime AS delta,
"User" as type

FROM prefix_logstore_standard_log as l,
(SELECT @delta := 0) AS s_init
# Change UserID
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%

List of attendees/students that were marked present across courses

This report will pull all Present students across a specific category. Contributed by: Emma Richardson

SELECT u.firstname AS "First Name", u.lastname AS "Last Name", u.Institution AS "District",c.fullname AS "Training", DATE_FORMAT(FROM_UNIXTIME(att.sessdate),'%d %M %Y')AS Date

FROM prefix_attendance_sessions AS att
JOIN prefix_attendance_log AS attlog ON att.id = attlog.sessionid
JOIN prefix_attendance_statuses AS attst ON attlog.statusid = attst.id
JOIN prefix_attendance AS a ON att.attendanceid = a.id
JOIN prefix_course AS c ON a.course = c.id
JOIN prefix_user AS u ON attlog.studentid = u.id

WHERE attst.acronym = "P"
AND c.category = INSERT YOUR CATEGORY ID HERE
ORDER BY c.fullname

Courses without Teachers

Actually, shows the number of Teachers in a course.

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

List of deactivated users in a course

List of deactivated users in a specific course

SELECT username, idnumber,
concat('<a target="_new" href="%%WWWROOT%%/user/profile.php?id=',uu.id,'">',uu.id,'</a>') as userid_and_link,
firstname, lastname, email, suspended as 'suspended/deactivated: 1'
FROM prefix_user_enrolments ue
JOIN prefix_enrol en ON ue.enrolid = en.id
JOIN prefix_user uu ON uu.id = ue.userid
WHERE en.courseid = 1234567
AND suspended = 1


All users individual timezone settings

Contributed by: Randy Thornton

If you allow users to set their own time zones, this can sometimes lead to confusion about due dates and times for assignments. This shows all active users with their personal time zone settings if any.

SELECT 
u.username, 
IF(u.timezone=99,"-Site Default-",u.timezone) AS "User Timezone"
FROM prefix_user u
WHERE u.deleted = 0
ORDER BY u.timezone DESC

ROLES and PERMISSIONS REPORTS

Count all Active Users by Role in a course category (including all of its sub-categories)

SELECT COUNT(DISTINCT l.userid) as active
FROM mdl_course as c
JOIN mdl_context AS ctx ON  ctx.instanceid=c.id
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user_lastaccess as l ON ra.userid = l.userid
JOIN mdl_course_categories AS cats ON c.category = cats.id
WHERE c.category=cats.id AND (
	cats.path LIKE '%/80/%'
	OR cats.path LIKE '%/80'
)
AND ra.roleid=3  AND ctx.contextlevel=50  #ra.roleid= TEACHER 3, NON-EDITING TEACHER 4, STUDENT 5
AND  l.timeaccess > (unix_timestamp() - ((60*60*24)*NO_OF_DAYS)) #NO_OF_DAYS change to number


Role assignments on categories

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.name,'</a>') AS category,
cc.depth, cc.path, r.name AS role,
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',usr.id,'">',usr.lastname,'</a>') AS name,
usr.firstname, usr.username, usr.email
FROM prefix_course_categories cc
INNER JOIN prefix_context cx ON cc.id = cx.instanceid
AND cx.contextlevel = '40'
INNER JOIN prefix_role_assignments ra ON cx.id = ra.contextid
INNER JOIN prefix_role r ON ra.roleid = r.id
INNER JOIN prefix_user usr ON ra.userid = usr.id
ORDER BY cc.depth, cc.path, usr.lastname, usr.firstname, r.name, cc.name

Compare role capability and permissions

Compatibility: MySQL and PostgreSQL

SELECT DISTINCT mrc.capability
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '1' AND rc.contextid = '1') AS Manager
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '2' AND rc.contextid = '1') AS Course_Creator
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '3' AND rc.contextid = '1') AS Teacher
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '4' AND rc.contextid = '1') AS Assistant_Teacher
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '5' AND rc.contextid = '1') AS Student
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '6' AND rc.contextid = '1') AS Guest
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '7' AND rc.contextid = '1') AS Authenticated
,(SELECT 'X' FROM prefix_role_capabilities AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '8' AND rc.contextid = '1') AS Auth_front
FROM prefix_role_capabilities AS mrc

Special Roles

SELECT ra.roleid,r.name
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_role_assignments AS ra
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN prefix_course AS c ON ctx.instanceid = c.id
WHERE ra.roleid > 6

Note: for the meaning of the number 6 see the section on Role ids below.

Permissions Overrides on Categories

(By: Séverin Terrier )

SELECT rc.id, ct.instanceid, ccat.name, rc.roleid, rc.capability, rc.permission,
DATE_FORMAT( FROM_UNIXTIME( rc.timemodified ) , '%Y-%m-%d' ) AS timemodified, rc.modifierid, ct.instanceid, ct.path, ct.depth
FROM `prefix_role_capabilities` AS rc
INNER JOIN `prefix_context` AS ct ON rc.contextid = ct.id
INNER JOIN `prefix_course_categories` AS ccat ON ccat.id = ct.instanceid
AND `contextlevel` =40

All Role Assignments with contexts

Contributed by: Randy Thornton

This lists all the roles that have been assigned in the site, along with the role shortname and the type of context where it is assigned, e.g. System, Course, User, etc. The last column, the context instance id, is the id number of the particular object where the assignment has been made. So, if the context is course, then the context instance id means the course id; if a category, then the category id, and so forth. So you can then use that number to locate the particular place where the role is assigned.

SELECT
u.username,
r.shortname AS "Role",
CASE ctx.contextlevel 
  WHEN 10 THEN 'System'
  WHEN 20 THEN 'Personal'
  WHEN 30 THEN 'User'
  WHEN 40 THEN 'Course_Category'
  WHEN 50 THEN 'Course'
  WHEN 60 THEN 'Group'
  WHEN 70 THEN 'Course_Module'
  WHEN 80 THEN 'Block'
 ELSE CONCAT('Unknown context: ',ctx.contextlevel)
END AS "Context_level",
ctx.instanceid AS "Context instance id"
FROM prefix_role_assignments ra
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_role r ON r.id = ra.roleid
JOIN prefix_context ctx ON ctx.id = ra.contextid
ORDER BY u.username

COURSE REPORTS

Lists "Totally Opened Courses" (visible, opened to guests, with no password)

(By: Séverin Terrier )

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course',
concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins',
e.sortorder
FROM prefix_enrol AS e, prefix_course AS c
WHERE e.enrol='guest' AND e.status=0 AND e.password='' AND c.id=e.courseid AND c.visible=1


Most Active courses

SELECT count(l.userid) AS Views
FROM `mdl_logstore_standard_log` l, `mdl_user` u, `mdl_role_assignments` r
WHERE l.courseid=35
AND l.userid = u.id
AND (l.timecreated > UNIX_TIMESTAMP('2015-01-01 00:00:00') AND l.timecreated <= UNIX_TIMESTAMP('2015-01-31 23:59:59'))AND r.contextid= (
	 SELECT id
	 FROM mdl_context
	 WHERE contextlevel=50 AND instanceid=l.courseid
 )
AND r.roleid=5
AND r.userid = u.id

Last access time of users to a course

Contributed by: Randy Thornton

This shows all users and their last access time to courses.

SELECT 
u.username, 
c.shortname AS "Course",
DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%Y-%m-%d %H:%i') AS "Last access time"
FROM prefix_user_lastaccess la
JOIN prefix_user u ON u.id = la.userid
JOIN prefix_course c ON c.id = la.courseid
ORDER BY u.username, c.shortname

Least active or probably empty courses

Contributed by: Randy Thornton

It is difficult to know sometimes when a course is actually empty or was never really in use. Other than the simple case where the course was created and never touched again, in which case the course timecreated and timemodified will be the same, many courses created as shells for teachers or other users may be used once or a few times and have few or no test users enrollments in them. This query helps you see the range of such courses, showing you how many days if any it was used after initial creation, and how many user are enrolled. It denotes a course never ever modified by "-1" instead of "0" so you can sort those to the top. By default it limits this to courses used within 60 days of creation, and to courses with 3 or less enrollments (for example, teacher and assistant and test student account only.) You can easily adjust these numbers. The query includes a link to the course as well.

SELECT
c.fullname,
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'CourseLink',
DATE_FORMAT(FROM_UNIXTIME(c.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated',
DATE_FORMAT(FROM_UNIXTIME(c.timemodified), '%Y-%m-%d %H:%i') AS 'Timemodified',
CASE
 WHEN c.timecreated = c.timemodified THEN '-1'
 ELSE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated))
END AS 'DateDifference',
COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
LEFT JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated) ) < 60
GROUP BY c.id
HAVING COUNT(ue.id) <= 3
ORDER BY c.fullname

Count unique teachers with courses that use at least X module (Moodle19)

You can remove the outer "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQL query and get the list of the Teachers and Courses.

SELECT COUNT(*)
FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE  ra.roleid = 3
GROUP BY u.id
HAVING Modules > 5) AS ActiveTeachers

Resource count for each Course

SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
GROUP BY course
ORDER BY count DESC

Common resource types count for each Category

Query but for Moodle2+

SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
mcc.path,

(SELECT COUNT(*)
FROM prefix_url AS u
JOIN prefix_course AS c ON c.id = u.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS URLs,

(SELECT COUNT(*)
FROM prefix_folder AS f
JOIN prefix_course AS c ON c.id = f.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS FOLDERs,

(SELECT COUNT(*)
FROM prefix_page AS p
JOIN prefix_course AS c ON c.id = p.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS PAGEs,

(SELECT COUNT(*)
FROM prefix_book AS b
JOIN prefix_course AS c ON c.id = b.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS BOOKs,

(SELECT COUNT(*)
FROM prefix_label AS l
JOIN prefix_course AS c ON c.id = l.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS LABELs,

(SELECT COUNT(*)
FROM prefix_tab AS t
JOIN prefix_course AS c ON c.id = t.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS TABs

FROM prefix_course_categories AS mcc
ORDER BY mcc.path

Detailed Resource Count by Teacher in each course

Including (optional) filter by: year, semester and course id.

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
, c.id
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
  FROM prefix_role_assignments AS ra
  JOIN prefix_user AS u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher

, (CASE
WHEN c.fullname LIKE '%תשעב%' THEN '2012'
WHEN c.fullname LIKE '%תשעא%' THEN '2011'
END ) as Year
, (CASE
WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A'
WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B'
WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C'
END ) as Semester
,COUNT(c.id) AS Total
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs

FROM `prefix_resource` as r
JOIN `prefix_course` AS c on c.id = r.course
#WHERE type= 'file' and reference NOT LIKE 'http://%'

#WHERE 1=1
#%%FILTER_YEARS:c.fullname%%
#AND c.fullname LIKE '%2013%'

GROUP BY course
ORDER BY COUNT(c.id) DESC

List all Courses in and below a certain category

Use this SQL code to retrieve all courses that exist in or under a set category.

$s should be the id of the category you want to know about...

SELECT prefix_course. * , prefix_course_categories. *
FROM prefix_course, prefix_course_categories
WHERE prefix_course.category = prefix_course_categories.id
AND (
prefix_course_categories.path LIKE '%/$s/%'
OR prefix_course_categories.path LIKE '%/$s'
)

List all Categories in one level below a certain category

Use this PHP code to retrieve a list of all categories below a certain category.

$s should be the id of the top level category you are interested in.

<?php

require_once('./config.php');

$parent_id = $s;

$categories= array();

$categories = get_categories($parent_id);

echo '<ol>';
foreach ($categories as $category)
        {
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
        }
echo '</ol>';

?>

All teachers and courses

Contributed by François Parlant

  • not taking into account the END DATE
SELECT 
c.id, c.shortname,  
CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', c.id, '">',c.fullname,'</a>') AS 'Course link', 
u.id as 'prof id', 
u.username, u.firstname, u.lastname, r.shortname as 'role'
From prefix_user as u
join prefix_user_enrolments ue on ue.userid=u.id
join prefix_enrol en on ue.enrolid=en.id
join prefix_role_assignments ra on u.id=ra.userid
join prefix_role r on ra.roleid=r.id and (r.shortname ='editingteacher' or r.shortname ='teacher')
join prefix_context cx on cx.id = ra.contextid and cx.contextlevel = 50
JOIN prefix_course c ON c.id = cx.instanceid AND en.courseid = c.id
JOIN prefix_course_categories cc ON c.category = cc.id
WHERE 1=1
%%FILTER_SUBCATEGORIES:cc.path%%
%%FILTER_STARTTIME:c.startdate:>%%

All courses without an END DATE

Contributed by François Parlant

select c.id, c.fullname, c.shortname,
-- c.startdate, c.enddate,
FROM_UNIXTIME(c.startdate,'%d/%m/%Y') as "Date début",
FROM_UNIXTIME(c.enddate,'%d/%m/%Y') as "Date fin",
CONCAT('<a href="https://pedago-msc.campusonline.me/course/view.php?id=', c.id,'">voir cours</a>') AS 'lien cours',
CONCAT('<a href="https://pedago-msc.campusonline.me/user/index.php?id=', c.id,'">voir participants</a>') AS 'lien participants'
FROM prefix_course  AS c
INNER JOIN prefix_course_categories cc ON c.category = cc.id
WHERE c.enddate = 0
%%FILTER_CATEGORIES:c.path%%
%%FILTER_SUBCATEGORIES:cc.path%%
%%FILTER_STARTTIME:c.startdate:>%%

All Courses which uploaded a Syllabus file

+ under specific Category + show first Teacher in that course + link Course's fullname to actual course


SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,c.shortname,r.name
,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user as u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
FROM prefix_resource as r
JOIN prefix_course as c ON r.course = c.id
WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )
AND c.category IN (10,18,26,13,28)

List all courses WITHOUT Syllabus

Contributed by François Parlant

  • courses without ressource with name starting by "syllabus" (using upper case or lower case)
  • display the name as a direct link
  • shows the name of teacher
  • category with sub category filter
  • start date and end date filters
SELECT c.id as 'id cours',
c.shortname, CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', c.id, '">',c.fullname,'</a>') AS 'Course link',
u.id, u.username, u.firstname, u.lastname, r.shortname as 'role'
FROM prefix_user as u
JOIN prefix_user_enrolments ue on ue.userid=u.id
JOIN prefix_enrol en on ue.enrolid=en.id
JOIN prefix_role_assignments ra on u.id=ra.userid
JOIN prefix_role r on ra.roleid=r.id and (r.shortname ='editingteacher' or r.shortname ='teacher')
JOIN prefix_context cx on cx.id = ra.contextid and cx.contextlevel = 50
JOIN prefix_course c ON c.id = cx.instanceid AND en.courseid = c.id
JOIN prefix_course_categories cc ON c.category = cc.id
WHERE c.id Not in (
  SELECT distinct(r.course)
  FROM prefix_resource AS r
  WHERE LOWER( r.name) LIKE 'syllabus%'
  GROUP BY r.course)
%%FILTER_SUBCATEGORIES:cc.path%%
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.enddate:<%%

Count the number of resources whose name starts by "Syllabus"

Contributed by François Parlant

Our school simply asks teachers to drop a file (resource) on their course page and rename this resource (not the file) starting with "syllabus" (case insensitive)

Select
r.name As 'Resource name',
cc.name AS 'Category',
CONCAT('<a href="%%WWWROOT%%/pluginfile.php/', ct.id, '/mod_resource/content/1/', f.filename, '">',f.filename,'</a>') AS 'Clickable filename',

c.fullname AS 'Course name',
c.shortname AS 'Course shortname',

# the date filters are connected to this "last modif" field
# userful to check if the syllabus has been updated this year
DATE_FORMAT(FROM_UNIXTIME(f.timemodified), '%e %b %Y') AS 'last modif',

# tell if the file is visible by the students or hidden
IF(cm.visible=0,"masqué","visible") AS 'Visibility',

# next line tries to give the real path (local path) if you want to create a zip file using an external script)
# notice that the path is in the column "contenthash" and NOT in the column pathhash
# if the contenthash starts with 9af3... then the file is stored in moodledata/filedir/9a/f3/contenthash
# I try to get the path to moodledata from the value of the geoip variable in the mdl_config table... maybe a bad idea
CONCAT('"',(Select left(value, length(value)-25) from prefix_config where name ="geoip2file"),'/filedir/', left(f.contenthash,2), "/",substring(f.contenthash,3,2),'/', f.contenthash, '"') AS 'link'

FROM prefix_resource AS r
INNER JOIN prefix_course_modules AS cm ON cm.instance = r.id
INNER JOIN prefix_course AS c ON c.id = r.course
INNER JOIN prefix_context AS ct ON ct.instanceid = cm.id
JOIN prefix_course_categories cc ON c.category = cc.id
INNER JOIN prefix_files AS f ON f.contextid = ct.id AND f.mimetype IS NOT NULL AND f.component = 'mod_resource'
WHERE LOWER( r.name) LIKE 'syllabus%'
%%FILTER_STARTTIME:f.timemodified:>%% %%FILTER_ENDTIME:f.timemodified:<%%
%%FILTER_SUBCATEGORIES:cc.path%%

List files which have been tagged "Syllabus"

Contributed by François Parlant

Select
t.rawname AS 'rawtag',
c.shortname AS 'Cours shortname',
c.fullname AS 'Course name',
r.name As 'Resource name',
CONCAT('<a href="%%WWWROOT%%/pluginfile.php/', ti.contextid, '/mod_resource/content/1/', f.filename, '">cliquez ici</a>') AS 'link',
ti.contextid AS 'Instance for link',
f.id AS 'file id'
FROM prefix_tag_instance AS ti
INNER JOIN prefix_tag AS t ON ti.tagid = t.id
INNER JOIN prefix_course_modules AS cm ON ti.itemid = cm.id
INNER JOIN prefix_course AS c ON cm.course = c.id
INNER JOIN prefix_resource AS r ON r.id = cm.instance
INNER JOIN prefix_files AS f ON f.contextid = ti.contextid AND f.mimetype IS NOT NULL
WHERE t.rawname = 'Syllabus'

List of courses WITHOUT a resource with a name starting by "syllabus"

Contributed by François Parlant

  • without teachers
select c.id, c.shortname,
CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', c.id, '">',c.fullname,'</a>') AS 'Course link'
FROM prefix_course AS c
INNER JOIN prefix_course_categories cc ON c.category = cc.id
WHERE r.course NOT IN (
  Select r.course
  from prefix_resource AS r
  WHERE LOWER( r.name) LIKE 'syllabus%'
  GROUP BY r.course) 
%%FILTER_SUBCATEGORIES:cc.path%%
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.enddate:<%%

List of courses have MULTIPLE resource with a name like "Syllabus%"

Contributed by François Parlant

select
r.course,
c.shortname,
CONCAT('<a href="%%WWWROOT%%/course/view.php?id=', r.id, '">',c.fullname,'</a>') AS 'Course link'
FROM prefix_resource AS r
INNER JOIN prefix_course AS c ON c.id = r.course
JOIN prefix_course_categories cc ON c.category = cc.id
WHERE LOWER( r.name) LIKE 'syllabus%'
GROUP BY r.course HAVING count(r.course)>1
%%FILTER_SUBCATEGORIES:cc.path%%

All users enrolled in a course without a role

Identifies All users that are enrolled in a course but are not assigned a role.

SELECT
user.firstname AS Firstname,
user.lastname AS Lastname,
user.idnumber Employee_ID,
course.fullname AS Course

FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user as user ON user.id = ue.userid

WHERE user.id NOT IN (
SELECT u.id
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE c.id=course.id
)
ORDER BY Course, Lastname, Firstname

List course resources accumulative file size and count

This is the main (first) report, which has a link (alias) to a second report (the following on this page) which list each file in the course.

SELECT c.id "CourseID", context.id "ContextID"
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=', c.id, '">', c.fullname ,'</a>') AS "Course Name"
, COUNT(*) "Course Files" , ROUND( SUM( f.filesize ) /1048576 ) AS file_size_MB
,CONCAT('<a target="_new" href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?alias=coursefiles&courseid=1&filter_courses=', c.id, '">List files</a>') AS "List Files"

FROM mdl_files AS f
JOIN mdl_context AS context ON context.id = f.contextid
JOIN mdl_course AS c ON c.id = (
  SELECT instanceid
  FROM mdl_context
  WHERE id = SUBSTRING_INDEX( SUBSTRING_INDEX( context.path, '/' , -2 ) , '/', 1 ) )
WHERE filesize >0
GROUP BY c.id

With this report, you will have to define "alias" report property to "coursefiles" for it to be able to be called from the above report. And also setup (add) a FILTER_COURSES filter.

SELECT
id ,CONCAT('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', contextid, '/', component, '/', filearea, '/', itemid, '/', filename, '">', filename,'</a>') AS "File"
,filesize, mimetype ,author, license, timecreated, component, filearea, filepath

FROM mdl_files AS f
WHERE filesize >0
            AND f.contextid
            IN (   SELECT id
                     FROM mdl_context
                    WHERE path
                     LIKE (   SELECT CONCAT('%/',id,'/%')
                                  AS contextquery
                                FROM mdl_context
                               WHERE 1=1
			        %%FILTER_COURSES:instanceid%%
                                 AND contextlevel = 50
                           )
                )

Which courses has redundant topics

This report list several "active topics" calculations, per course. which should give an administrator some indications for which topics/sections/weeks are filled with resources and activities and which ones are empty and not used (usually, at the end of the course).

The following, second SQL query, could be used to "trim" down those redundant course topics/sections/weeks by updating the course format's numsection (Number of sections) setting. (It's a per course format setting!)

SELECT id, format,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">', c.fullname,'</a>') AS Course

,(SELECT value  FROM  `mdl_course_format_options` WHERE  `courseid` = c.id AND `format` = c.format AND `name` = 'numsections' ) AS "numsections"
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND `sequence` !=  '' ) AS "Non empty sections count"
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id ) AS "Total section count"
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND sequence IS NOT NULL) AS "Non NULL sections count"
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND name != '') AS "Non empty section Name count"
 ,(SELECT COUNT(*) FROM mdl_course_modules cm WHERE cm.course = c.id) "Modules count"

FROM mdl_course AS c

The following SQL REPLACE query is used for "fixing" (updating) the "numsections" of a specific course format "onetopics" (you can always change it, or discard it to use this SQL REPLACE on all course formats)

REPLACE INTO `mdl_course_format_options` (`id`, `courseid`, `format`, `sectionid`, `name`, `value`)
SELECT NULL, c.id, 'onetopic', '0', 'numsections', (SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND name != '')
FROM `mdl_course` c where format = 'onetopic'

Hidden Courses with Students Enrolled

Contributed by Eric Strom

This query identifies courses with student enrollment that are currently hidden from students. Includes the defined course start date, count of students and instructors, and a clickable email link of instructor (first found record if more than one).

SELECT c.visible AS Visible,
DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID,

(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students,

(SELECT COUNT( ra.userid ) 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 Instructors,

(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
  FROM prefix_role_assignments AS ra
  JOIN prefix_user AS u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email',

now() AS Report_Timestamp

FROM prefix_course AS c
WHERE c.visible = 0 AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
ORDER BY StartDate, Instructor_Email, Course_ID

Course formats used on my system

SELECT COUNT(*) 'Count', c.format 'Format'
FROM prefix_course AS c
GROUP BY c.format

Course catalogue with future courses

SELECT CONCAT('<a href="%%WWWROOT%%/course/info.php?id=',course.id,'">',course.fullname,'</a>') AS Kurs, FROM_UNIXTIME(startdate, '%Y/%m/%d') AS Beginn
FROM prefix_course AS course
WHERE DATEDIFF(NOW(),FROM_UNIXTIME(startdate)) < 0
ORDER BY startdate


Enrolment methods used in all courses

List of all the enrolment methods attached to all courses with their type, enabled status, sort order, and custom name if any. Includes a link directly the each course's enrolment methods settings page. Known to work in 3.11 (should work in most earlier version.) This report could serve as the basis and be easily expanded to show the various settings details for the methods if you want.

Contributed by: Randy Thornton

SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">',c.shortname,'</a>') AS "Course",
e.enrol AS "Method",
CASE e.status 
   WHEN 0 THEN 'Enabled' 
   WHEN 1 THEN '-' 
   ELSE e.status 
END AS "Status",
IF(e.name IS NOT NULL,e.name,'-') AS "Custom name"

FROM prefix_enrol e 
JOIN prefix_course c ON c.id = e.courseid
ORDER BY c.shortname,e.sortorder

GROUP REPORTS

List course group mode settings

Contributed by: Randy Thornton

Every course has a group mode set in the course settings. The options are stored in prefix_course.groupmode, where

  • 0 = No groups
  • 1 = Separate groups
  • 2 = Visible groups

The Group mode forced setting is in prefix_course.groupmodeforce and is 0 for off and 1 for on. The following query will show you these settings for all courses.

SELECT
c.shortname AS "Course",
CASE c.groupmode
WHEN 0 THEN "No groups"
WHEN 1 THEN "Separate groups"
WHEN 2 THEN "Visible groups" 
ELSE "This should not happen!"
END AS "Group mode",
IF(c.groupmodeforce=0, "Not forced","Forced") AS "Group mode forced"
FROM prefix_course c
ORDER BY c.shortname

Courses that are defined as using Groups

SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
 FROM `prefix_course` AS c
WHERE groupmode > 0

Courses with Groups

Contributed by: Randy Thornton

List of all courses with Groups in them (groupmode > 0). You can also use groupmode=1 to list just Separate type groups or groupmode=2 to list Visible type groups.

SELECT c.shortname, g.name, c.groupmode
FROM prefix_course AS c
JOIN prefix_groups AS g ON c.id = g.courseid
WHERE c.groupmode > 0

Users enrolled in a course with groups but not assigned a group

Displays by course all enrolled users that have not been assigned a group in courses that have groups. NOTE: This needs to be optimized.

SELECT DISTINCT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName

FROM prefix_course AS course
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id
JOIN prefix_groups AS g ON g.courseid = course.id

WHERE ue.enrolid NOT IN (select userid from prefix_groups_members WHERE g.id=groupid)

ORDER BY Course, Lastname

Groups in course with member list

Contributed by: Randy Thornton

List the groups in a course (replace the # by the course id number) with the members of each group.

SELECT c.shortname, g.name AS Groupname, u.username
FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
JOIN prefix_user AS u ON m.userid = u.id
WHERE c.id = #

Note: if you are using Configurable Reports block and want to perform this query on the current course you are in, then you can use a WHERE clause like this:

WHERE c.id = %%COURSEID%%

All the Groups I am in in all courses

Contributed by: Randy Thornton

This will show all the groups in all courses that the user running the report is a member of. It uses the variable %%USERID%% used by the two Moodle plugins to do this.


SELECT
c.shortname AS Course,
g.name AS Groupname
FROM (SELECT userid, groupid FROM prefix_groups_members WHERE userid = %%USERID%%) AS GRU
JOIN prefix_user u ON u.id = GRU.userid
JOIN prefix_groups g ON g.id = GRU.groupid
JOIN prefix_course c ON c.id = g.courseid
# uncomment to limit it to only the current course 
WHERE g.courseid = %%COURSEID%%
ORDER BY c.shortname, g.name

Group Export

Contributed by: Randy Thornton

There's a group import function, but no export. Use this to give you a report with the proper column order and headings to export to a csv file you can then import into another course to replicate the groups. This is a simple version with just the main fields: groupname, description, enrolment key.

SELECT g.name AS groupname, g.description, g.enrolmentkey
FROM prefix_groups AS g
JOIN prefix_course as c ON g.courseid = c.id
WHERE c.id = #

COURSE DESIGN REPORTS

These are reports which summarize course design aspects, such as activity and resource modules per section, types of activities used, etc.

Course Content/Week

Contributed by Elizabeth Dalton, Granite State College

This report assumes that the first 14 sections in a course, not including the "0" or "Welcome" section, correspond to weeks (with "Subsections" given numbers much higher in the sequence). Of those sections, each is checked to count the number of:

   Forums
   Graded Activities (may include Forums)
   Resources (not including a Label)

Totals of each of these types of content elements per section are provided.

Note: Only visible resources and activities are counted. Note: this is a "Global" report. Run it within a course to see a summary of the contents of that course.

SELECT

cs.section AS 'Week'
, cs.name AS 'Section Name'

, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT LIKE 'label'),cm.id,NULL)) AS 'Ungraded Resources'

, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'

, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'

FROM prefix_course AS c
JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section <= 14 AND cs.section > 0
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
JOIN prefix_modules AS m ON m.id = cm.module
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance

WHERE
cs.visible = 1
AND cm.visible = 1
AND c.id = %%COURSEID%%

GROUP BY cs.section
ORDER BY cs.section

Assignments and Weights

Contributed by Elizabeth Dalton, Granite State College

Returns a list of grade book categories for the current course, grade book weightings, the first type of assignment included in the category, a count of different assignment types for each category, and a count of assignments for each category.

Categories with weights of 0 are not included in this report.

Only visible activities are included in this report.

Note: This is designed to be a "Global" report in Configurable Reports.

SELECT

IF(gc.parent IS NOT NULL, gc.fullname, 'None') AS 'Grade Book Category'
, IF(gc.parent IS NOT NULL, ROUND(gic.aggregationcoef, 2), ROUND(SUM(DISTINCT gi.aggregationcoef), 2)+ROUND(SUM(DISTINCT mgi.aggregationcoef), 2)) AS 'Category weight'

, CONCAT_WS(', ',GROUP_CONCAT(DISTINCT gi.itemmodule SEPARATOR ', '), IF(mgi.id, 'manual',NULL)) AS 'Activity Types'
, COUNT(DISTINCT gi.itemmodule) + IF(mgi.id,1,0) AS 'Different Activity Types'
, CONCAT_WS('<br>', GROUP_CONCAT(DISTINCT gi.itemname ORDER BY gi.itemname SEPARATOR '<br>'), GROUP_CONCAT(DISTINCT mgi.itemname ORDER BY mgi.itemname SEPARATOR '<br>')) AS 'Activity Names'
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) + COUNT(DISTINCT mgi.id) AS 'Activity Count'

FROM prefix_course AS c

#get grade categories
LEFT JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
# back from categories to grade items to get aggregations and weights
JOIN prefix_grade_items AS gic ON gic.courseid = c.id AND gic.itemtype = 'category' AND gic.aggregationcoef != 0 AND (LOCATE(gic.iteminstance, gc.path) OR (gc.parent IS NULL))

# attach activities to course
JOIN prefix_course_modules AS cm ON cm.course = c.id
# attach grade items to activities
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.iteminstance = cm.instance AND gi.itemtype = 'mod' AND gi.categoryid = gc.id AND gi.hidden != 1

# attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual' AND mgi.categoryid = gc.id

WHERE
cm.visible = 1
AND c.id = %%COURSEID%%

GROUP BY gc.id
ORDER BY gc.id

Pre-Term Course Review

Contributed by Elizabeth Dalton, Granite State College

Provides an overview of the readiness of ONLINE, HYBRID, and BLENDED courses in the Staging category and all subcategories. Links to each course are provided. Other details:

  1. "Required blocks" include Instructor Block (mooprofile), Activities, and the Research block.
  2. "Instructor Details" block is not the "Instructor" block (mooprofile) automatically provided by the system. It is an optional block that can be edited by the instructor. If not edited to remove boilerplate text, it should be hidden.
  3. All courses should be in the "Collapsed Topics" format with the "Weeks" structure.
  4. "Weeks defined in course settings" is taken from our SIS when the course shells are created, but can be edited by faculty. "# of weeks named and visible" should usually match or exceed this value.
  5. We recommend that each week contain at least one forum, at least one graded activity, and at least one ungraded resource.
  6. "Syllabus updated" date is for the first attached file found with the text "syllabus" in the name. The "Days ago" calculation is included for convenience.

Note: At our institution, we construct categories each term, and insert a text string "staging" in the Category ID for pre-term courses during the preparation or "staging" phase of course development. We remove this text string (and change it to "production") when courses go live at the start of the new term.

SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS Course

#,RIGHT(c.idnumber,2) AS Type # Specific to GSC "Instructional Method" storage

#, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section # Specific to GSC

,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'

,(SELECT IF((u2.description IS NULL) OR (u2.description LIKE ''),'NO', 'YES')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u2 ON u2.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Bio'

,(SELECT IF(u3.picture > 0,'YES','NO')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u3 ON u3.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Picture'

, IF(((bpi.visible IS NULL) OR (bpi.visible !=0)) AND ((bpm.visible IS NULL) OR (bpm.visible !=0)) AND ((bpa.visible IS NULL) OR (bpa.visible !=0)) AND ((bpr.visible IS NULL) OR (bpr.visible !=0)),'YES','NO') AS 'Required blocks visible'
#, IF((bpm.visible IS NULL) OR (bpm.visible !=0),'YES','NO') AS 'Messages block visible'
#, IF((bpa.visible IS NULL) OR (bpa.visible !=0),'YES','NO') AS 'activities block visible'
#, IF((bpr.visible IS NULL) OR (bpr.visible !=0),'YES','NO') AS 'research block visible'

#, IF(SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)) AND (bip.visible !=0),'YES','') AS 'Instructor Details Block visible' # This is a hack based on UUencoded string data from the title of HTML "Instructor Details" block

#, IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO','') AS 'Instructor Details Block Updated' # HTML block has string 'dit this block'

#, IF(COUNT(bi.id) -  SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)),'YES','') AS 'possible extra instructor blocks' #looking for any HTML block with "instructor" in the title

, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format' # change this if you want to test for a different format
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'

, cfw.value AS 'weeks defined in course settings'

, COUNT(DISTINCT IF(((cs.name IS NOT NULL) AND (cs.visible = 1) AND (cs.section != '0') AND (cs.sequence IS NOT NULL)),cs.id,NULL)) AS '# of weeks named & visible (includes orphans)'

, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
, COUNT(DISTINCT IF(m.name LIKE 'forum' ,cs.id , NULL)) AS 'Weeks with Forum'

, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Activities'
, COUNT(DISTINCT IF(gi.id, cs.id, NULL)) AS 'Weeks with Activities'
, COUNT(DISTINCT mgi.id) AS 'Manual Grade Items'

, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')),cm.id,NULL)) AS 'Resources'
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')), cs.id, NULL)) AS 'Weeks with Resources'

# Here are some other things you could check for per course
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums

#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes

#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assign%') AS Assignments

#,(SELECT COUNT(prefix_resource.id) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files

#,(SELECT COUNT(prefix_url.id) FROM prefix_url JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links

,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS SyllabusDate

,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo

, IF(COUNT(DISTINCT IF(f.type LIKE 'news', f.id,NULL)),'YES','NO' ) AS 'Announcement Forum Visible'

, IF(COUNT(DISTINCT IF(f.type LIKE 'news', fd.id,NULL)),'YES','NO' ) AS 'Announcement posted'

FROM prefix_course AS c
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
LEFT JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid

LEFT JOIN prefix_block_positions AS bpi ON bpi.contextid = ctxx.id AND bpi.blockinstanceid = '43692' # mooprofile
LEFT JOIN prefix_block_positions AS bpm ON bpm.contextid = ctxx.id AND bpm.blockinstanceid = '43962' # messages
LEFT JOIN prefix_block_positions AS bpa ON bpa.contextid = ctxx.id AND bpa.blockinstanceid = '43963' # activities
LEFT JOIN prefix_block_positions AS bpr ON bpr.contextid = ctxx.id AND bpr.blockinstanceid = '38368' # html research help

LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.visible = 1 AND cs.sequence IS NOT NULL
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
LEFT JOIN prefix_modules AS m ON m.id = cm.module
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance

LEFT JOIN prefix_forum AS f ON f.course = c.id AND cm.instance = f.id AND cm.visible = 1
LEFT JOIN prefix_forum_discussions AS fd ON fd.forum = f.id

# attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual'

LEFT JOIN prefix_course_format_options AS cfo ON cfo.courseid = c.id AND cfo.name = 'layoutstructure'
LEFT JOIN prefix_course_format_options AS cfw ON cfw.courseid = c.id AND cfw.name = 'numsections'

LEFT JOIN prefix_block_instances AS bi ON bi.parentcontextid = ctxx.id AND bi.blockname = 'html' AND (bi.configdata LIKE '%SW5zdHJ1Y3Rvc%' or bi.configdata LIKE '%bnN0cnVjdG9y%')
LEFT JOIN prefix_block_positions AS bip ON bip.blockinstanceid = bi.id

WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY')
# AND substring(cc.path,2,2) IN ('26') # Staging
#AND substring(cc.path,2,3) IN ('158') # UG
AND cc.idnumber LIKE '%staging%'
AND ctxx.contextlevel = 50

GROUP BY c.shortname

Course Syllabus

Contributed by Elizabeth Dalton, Granite State College / Moodle HQ

This report requires ELIS. It runs from within a course and constructs a course syllabus based on content in the course and in the ELIS entries related to the course (Class Instance, Course Description, and Program). It is a proof-of-concept of an automated syllabus production tool. Fields such as "Course Policies" and "Teaching Philosophy" are added to the Class Instance records, and instructors enter them there. The Instructor Bio is pulled from the User Profile of all users with the Teacher role in the course.

SELECT

c.fullname AS 'fullname'
, ec.idnumber AS 'elis-id'
, DATE_FORMAT(FROM_UNIXTIME(ec.startdate), '%b %e, %Y') AS 'start'
, DATE_FORMAT(FROM_UNIXTIME(ec.enddate), '%b %e, %Y') AS 'end'
, ecd.name AS 'longname'
, ecd.code AS 'coursecode'
, ecd.credits AS 'coursecredits'
, ecd.syllabus AS 'description'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'learning-outcomes'
WHERE ctxecd.id = eft.contextid) AS 'outcomes'

,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.firstname,' ', u.lastname,'</a> ', u.email)
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'

, (SELECT  efc.data
FROM prefix_local_eliscore_fld_data_char AS efc
JOIN prefix_local_eliscore_field AS ef ON ef.id = efc.fieldid AND ef.shortname = 'term-code'
WHERE ctxci.id = efc.contextid) AS 'termcode'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'prerequisites'
WHERE ctxecd.id = eft.contextid) AS 'prerequisites'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'textbooks'
WHERE ctxci.id = eft.contextid) AS 'textbooks'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'other-class-materials'
WHERE ctxci.id = eft.contextid) AS 'other-class-materials'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'course-policies'
WHERE ctxci.id = eft.contextid) AS 'course-policies'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'teaching-philosophy'
WHERE ctxci.id = eft.contextid) AS 'teaching-philosophy'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'course-methods'
WHERE ctxci.id = eft.contextid) AS 'course-methods'

,(SELECT u2.description
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u2 ON u2.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Bio'

,(SELECT

GROUP_CONCAT(DISTINCT CONCAT(

'<tr><td style="border: solid #000 .5px">',IF(gc.parent IS NOT NULL, gc.fullname, 'None')
, ' </td><td style="border: solid #000 .5px"> '
,IF(gc.parent IS NOT NULL, ROUND(gic.aggregationcoef, 2), ROUND( gi.aggregationcoef, 2)+ROUND(mgi.aggregationcoef, 2))

) SEPARATOR '</td></tr>')
#get grade categories
FROM prefix_grade_categories AS gc
# back from categories to grade items to get aggregations and weights
LEFT JOIN prefix_grade_items AS gic ON gic.courseid = gc.courseid AND gic.itemtype = 'category' AND gic.aggregationcoef != 0 AND (LOCATE(gic.iteminstance, gc.path) OR (gc.parent IS NULL))
# attach grade items to activities
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = gc.courseid  AND gi.itemtype = 'mod' AND gi.categoryid = gc.id AND gi.hidden != 1
# attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = gc.courseid and mgi.itemtype = 'manual' AND mgi.categoryid = gc.id
WHERE gc.courseid = c.id  ) AS 'grade categories'

, '<table width = "50%" >' AS 'table start'
, '<table width = "100%" >' AS 'table start 2'
, '</table>' AS 'table end'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'activities-schedule'
WHERE ctxci.id = eft.contextid) AS 'activities'


, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'schedule'
WHERE ctxci.id = eft.contextid) AS 'schedule'

, (SELECT  eft.data
FROM prefix_local_eliscore_fld_data_text AS eft
JOIN prefix_local_eliscore_field AS ef ON ef.id = eft.fieldid AND ef.shortname = 'grading-scale'
WHERE ctxepm.id = eft.contextid) AS 'gradescale'

FROM
prefix_course AS c

# connect moodle course to ELIS class instance
LEFT JOIN prefix_local_elisprogram_cls_mdl AS ecm ON ecm.moodlecourseid = c.id
LEFT JOIN prefix_local_elisprogram_cls AS ec ON ec.id = ecm.classid
# class instance context
LEFT JOIN prefix_context AS ctxci ON ctxci.instanceid = ec.id AND ctxci.contextlevel = '14'

# connect ELIS class instance to ELIS course description
LEFT JOIN prefix_local_elisprogram_crs AS ecd ON ecd.id = ec.courseid
# course description context
LEFT JOIN prefix_context AS ctxecd ON ctxecd.instanceid = ecd.id AND ctxecd.contextlevel = '13'

#connect ELIS program to ELIS Course Description
LEFT JOIN prefix_local_elisprogram_pgm_crs AS epc ON epc.courseid = ecd.id
LEFT JOIN prefix_local_elisprogram_pgm AS epm ON epm.id = epc.curriculumid
# course program context
LEFT JOIN prefix_context AS ctxepm ON ctxepm.instanceid = epm.id AND ctxepm.contextlevel = '11'

WHERE

c.id = %%COURSEID%%

Course Activities Helper

Contributed by Elizabeth Dalton, Granite State College

This report provides a list of the graded activities in a course.

  • Note: Only graded activities are displayed.
  • Note: This is a "Global" report. Run it within a course to see a summary of the contents of that course.
  • Note: This report assumes that course sections each last one week.
# 303 Course Activities Helper

SELECT

gi.itemmodule AS 'activity type'
# cs.section AS 'section number'

# Calculation assumes each section lasts one week
, CONCAT(DATE_FORMAT(FROM_UNIXTIME(c.startdate + (7*24*60*60* (cs.section-1))), '%b %e, %Y'),' - <br>',DATE_FORMAT(FROM_UNIXTIME(c.startdate + (7*24*60*60* (cs.section))), '%b %e, %Y')) AS 'Date'

, gi.itemname AS 'activity name'

#, (SELECT asg.intro FROM prefix_assign AS asg WHERE asg.id = cm.instance) AS 'intro'

#, (SELECT f.intro FROM prefix_forum AS f WHERE f.id = cm.instance) AS 'f intro'

, CASE gi.itemmodule
WHEN 'assign' THEN (SELECT asg.intro FROM prefix_assign AS asg WHERE asg.id = gi.iteminstance)
WHEN 'forum' THEN (SELECT f.intro FROM prefix_forum AS f WHERE f.id = gi.iteminstance)
WHEN 'quiz' THEN (SELECT q.intro FROM prefix_quiz AS q WHERE q.id = gi.iteminstance)
END AS 'test case'

#, (SELECT GROUP_CONCAT(CONCAT(' - ',gi.itemname) SEPARATOR '<BR>') FROM  prefix_grade_items AS gi  JOIN prefix_course_modules AS cm ON  gi.iteminstance = cm.instance WHERE gi.gradetype = 1 AND gi.hidden != 1 AND gi.courseid = c.id AND cm.course = c.id AND cm.section = cs.id ) AS 'activities'


FROM
prefix_course AS c

#get grade sections
LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id  AND cs.section > 0 AND cs.section <=14
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id

#LEFT JOIN prefix_assign AS asg ON asg.id = cm.instance

JOIN prefix_grade_items AS gi  ON  gi.iteminstance = cm.instance AND gi.gradetype = 1 AND gi.hidden != 1 AND gi.courseid = c.id AND cm.course = c.id AND cm.section = cs.id

WHERE
c.id = %%COURSEID%%
AND cs.visible = 1

ORDER BY gi.itemmodule, cs.section

COURSE COMPLETION and GRADE REPORTS

Site-Wide Grade Report with All Items

Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports.

Editor Note: This version is for MSSQL server. For MySQL, see below.

SELECT u.firstname AS 'First' , u.lastname AS 'Last',
u.firstname + ' ' + u.lastname AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
  WHEN gi.itemtype = 'course'
   THEN c.fullname + ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  gi.courseid = c.id
ORDER BY lastname

For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line:

DATEADD(ss,gg.timemodified,'1970-01-01') AS Time

with:

FROM_UNIXTIME(gg.timemodified) AS Time

And:

u.firstname + ' ' + u.lastname AS 'Display Name',

with:

CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',

And:

THEN c.fullname + ' Course Total'

with:

THEN CONCAT(c.fullname,' Course Total')

Editor Note by Randy Thornton: or in full the MySQL version is:

SELECT u.firstname AS 'First' , 
u.lastname AS 'Last',
CONCAT(u.firstname, ' ', u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',

CASE
  WHEN gi.itemtype = 'course'
   THEN CONCAT(c.fullname, ' Course Total')
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified),'%Y-%m-%d') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  gi.courseid = c.id
ORDER BY u.lastname

Site-Wide Grade Report with Just Course Totals

A second site-wide grade report for all students that just shows course totals. Works with ad-hoc reports or Configurable Reports

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
cc.name AS 'Category',
CASE
  WHEN gi.itemtype = 'course'
   THEN c.fullname + ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE  gi.courseid = c.id AND gi.itemtype = 'course'

ORDER BY lastname

For MySQL users:

SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
c.fullname AS 'Course',
cc.name AS 'Category',
CASE
  WHEN gi.itemtype = 'course'
   THEN CONCAT(c.fullname, ' - Total')
  ELSE gi.itemname
END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS TIME

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category

WHERE  gi.courseid = c.id AND gi.itemtype = 'course'
ORDER BY lastname

Learner report by Learner with grades

Which Learners in which course and what are the grades

SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as Percentage,

if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id and gi.itemname != 'Attendance'
ORDER BY `Name` ASC


Grades for all students in all courses

Contributed by: Randy Thornton

A basic report showing grades for Students in all courses. It has only four columns: username, course shortname, grade, and date, restricted to the standard role of Student. You can use this as the basis for more complex reports.

SELECT 
u.username, 
c.shortname AS "Course",
ROUND(gg.finalgrade,2) AS "Grade",
DATE_FORMAT(FROM_UNIXTIME(gg.timemodified), '%Y-%m-%d') AS "Date"

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
WHERE gi.courseid = c.id 
AND gi.itemtype = 'course'
# students only role id is 5
AND ra.roleid = 5
ORDER BY u.username, c.shortname

Users who have not completed a course

By DKeeler

Reference: https://stackoverflow.com/questions/69544738/sql-ad-hoc-report-of-all-users-not-completed-a-course

SELECT
    u.id AS userid,
    u.firstname,
    u.lastname,
    u.idnumber,
    u.email
FROM mdl_user u
WHERE u.deleted = 0
AND u.suspended = 0
AND firstname NOT LIKE "Guest user"
AND NOT EXISTS (
    SELECT ue.userid
    FROM mdl_user_enrolments ue
    JOIN mdl_enrol e ON e.id = ue.enrolid AND e.courseid = 123
    WHERE ue.userid = u.id
)
ORDER BY
    lastname,
    firstname

Course Completion sitewide for all Users

Contributed by: Randy Thornton

A very simple report with a list of course completion status by username. Completions are noted by date, blank otherwise.

SELECT
  u.username,
  c.shortname,
 DATE_FORMAT(FROM_UNIXTIME(cp.timecompleted),'%Y-%m-%d') AS completed
FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY u.username

Another version which includes the start and completed times.

SELECT 
u.firstname,
u.lastname,
c.shortname AS 'Course',
CASE 
  WHEN cp.timestarted = 0 THEN DATE_FORMAT(FROM_UNIXTIME(cp.timeenrolled),'%Y-%m-%d')
  ELSE DATE_FORMAT(FROM_UNIXTIME(cp.timestarted),'%Y-%m-%d')
END AS 'Started',
FROM_UNIXTIME(cp.timecompleted) AS 'Complete'
 
FROM prefix_user AS u
JOIN prefix_course_completions AS cp ON cp.userid = u.id
JOIN prefix_course c ON c.id = cp.course

User Course Completion with Criteria

Contributed by: Randy Thornton

A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.

SELECT u.username AS user,
c.shortname AS course,
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
CASE
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any"
ELSE "All"
END AS aggregation,
CASE
WHEN p.criteriatype = 1 THEN "Self"
WHEN p.criteriatype = 2 THEN "By Date"
WHEN p.criteriatype = 3 THEN "Unenrol Status"
WHEN p.criteriatype = 4 THEN "Activity"
WHEN p.criteriatype = 5 THEN "Duration"
WHEN p.criteriatype = 6 THEN "Course Grade"
WHEN p.criteriatype = 7 THEN "Approve by Role"
WHEN p.criteriatype = 8 THEN "Previous Course"
END AS criteriatype,
CASE
WHEN p.criteriatype = 1 THEN "*"
WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
WHEN p.criteriatype = 3 THEN t.unenroled
WHEN p.criteriatype = 4 THEN
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
WHEN p.criteriatype = 5 THEN p.enrolperiod
WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2))
WHEN p.criteriatype = 7 THEN p.role
WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance)
END AS criteriadetail
FROM prefix_course_completion_crit_compl AS t
JOIN prefix_user AS u ON t.userid = u.id
JOIN prefix_course AS c ON t.course = c.id
JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id

Courses with Completion Enabled and their settings

Contributed by: Randy Thornton

List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.

SELECT c.shortname AS Course,
CASE
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = t.course AND a.criteriatype IS NULL)) = 2 THEN "All"
ELSE "Any"
END AS Course_Aggregation,
CASE
WHEN t.criteriatype = 1 THEN "Self completion"
WHEN t.criteriatype = 2 THEN "Date done by"
WHEN t.criteriatype = 3 THEN "Unenrolement"
WHEN t.criteriatype = 4 THEN "Activity completion"
WHEN t.criteriatype = 5 THEN "Duration in days"
WHEN t.criteriatype = 6 THEN "Final grade"
WHEN t.criteriatype = 7 THEN "Approve by role"
WHEN t.criteriatype = 8 THEN "Previous course"
END AS Criteria_type,
CASE
WHEN t.criteriatype = 1 THEN "On"
WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
WHEN t.criteriatype = 3 THEN "On"
WHEN t.criteriatype = 4 THEN
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role)
WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance)
END AS Criteria_detail
FROM prefix_course_completion_criteria as t
JOIN prefix_course AS c ON t.course = c.id
WHERE c.enablecompletion = 1
ORDER BY course

Course Completion Report with custom dates

List of users who completed multiple or single course/s from a start date to end date chosen by the user. The output gives username, name, course name, completion date and score

SELECT u.username AS 'User Name',
CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
c.shortname AS 'Course Name',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date',
ROUND(c4.gradefinal,2) AS 'Score'
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid
WHERE c.enablecompletion = 1  AND (p.timecompleted IS NOT NULL OR p.timecompleted !='')
AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date)
GROUP BY u.username
ORDER BY c.shortname

Scales used in activities

SELECT scale.name
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',gi.itemmodule,'/view.php?id=',cm.id,'">',gi.itemname,'</a>') AS "Module View"
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/modedit.php?up','date=',cm.id,'">',gi.itemname,'</a>') AS "Module Settings"

FROM prefix_grade_items AS gi
JOIN prefix_course AS c ON c.id = gi.courseid
JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance
JOIN prefix_scale AS scale ON scale.id = gi.scaleid
WHERE gi.scaleid IS NOT NULL


Extra Credit Items by Name Only

Contributed by Eric Strom

This query identifies grade items in visible courses with student enrollment that have "extra credit" in the name of the item but set as extra credit in the grade settings. Includes the defined course start date, count of students and instructors, and a clickable email link of instructor (first found record if more than one).

SELECT DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID, gi.itemname AS Item_Name

,(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students

,(SELECT COUNT( ra.userid ) 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 Instructors

,(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
  FROM prefix_role_assignments AS ra
  JOIN prefix_user AS u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email'

,now() AS Report_Timestamp

FROM prefix_grade_items AS gi
JOIN prefix_course AS c ON gi.courseid = c.id

WHERE gi.itemname LIKE '%extra credit%'
	AND gi.gradetype = '1'
	AND gi.hidden = '0'
	AND gi.aggregationcoef = '0'
	AND c.visible = 1
	AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0

GROUP BY Course_ID, gi.id
ORDER BY StartDate, Course_ID

%%FILTER_SEARCHTEXT:Course_ID:~%%

Site Wide Number of Courses Completed by User

Contributed by Ken St. John

Simple report that shows the number of completed courses for all users site wide

SELECT u.lastname, u.firstname,
COUNT(p.timecompleted) AS TotalCompletions
FROM prefix_course_completions AS p
JOIN prefix_user AS u ON p.userid = u.id
GROUP BY p.userid
ORDER BY u.lastname

ACTIVITY MODULE REPORTS

Note that specific activity modules each have their own section below, with the Resource modules - book, file, folder, page and url all in one section together. At the end is a section for all third party additional activity modules.

General or Multiple Activities

Reports covering multiple or all course modules.

User activity completions with dates

Contributed by: Randy Thornton

This report shows the users completion status of activities across all courses. It is intended to be uses with Configurable Reports filters for user, start and end times, and also to be able to search the Module names. It includes the new core H5P module in 3.10. Add any third party activity modules you may have in your site as you need. Also, thanks to Tim Hunt for improvements to this query.


SELECT
u.username As 'User',
c.shortname AS 'Course',
m.name AS Activitytype,
CASE
    WHEN m.name = 'assign'  THEN (SELECT name FROM prefix_assign WHERE id = cm.instance)
    WHEN m.name = 'assignment'  THEN (SELECT name FROM prefix_assignment WHERE id = cm.instance)
    WHEN m.name = 'book'  THEN (SELECT name FROM prefix_book WHERE id = cm.instance)
    WHEN m.name = 'chat'  THEN (SELECT name FROM prefix_chat WHERE id = cm.instance)
    WHEN m.name = 'choice'  THEN (SELECT name FROM prefix_choice WHERE id = cm.instance)
    WHEN m.name = 'data'  THEN (SELECT name FROM prefix_data WHERE id = cm.instance)
    WHEN m.name = 'feedback'  THEN (SELECT name FROM prefix_feedback WHERE id = cm.instance)
    WHEN m.name = 'folder'  THEN (SELECT name FROM prefix_folder WHERE id = cm.instance)
    WHEN m.name = 'forum' THEN (SELECT name FROM prefix_forum WHERE id = cm.instance)
    WHEN m.name = 'glossary' THEN (SELECT name FROM prefix_glossary WHERE id = cm.instance)
    WHEN m.name = 'h5pactivity' THEN (SELECT name FROM prefix_h5pactivity WHERE id = cm.instance)
    WHEN m.name = 'imscp' THEN (SELECT name FROM prefix_imscp WHERE id = cm.instance)
    WHEN m.name = 'label'  THEN (SELECT name FROM prefix_label WHERE id = cm.instance)
    WHEN m.name = 'lesson'  THEN (SELECT name FROM prefix_lesson WHERE id = cm.instance)
    WHEN m.name = 'lti'  THEN (SELECT name FROM prefix_lti  WHERE id = cm.instance)
    WHEN m.name = 'page'  THEN (SELECT name FROM prefix_page WHERE id = cm.instance)
    WHEN m.name = 'quiz'  THEN (SELECT name FROM prefix_quiz WHERE id = cm.instance)
    WHEN m.name = 'resource'  THEN (SELECT name FROM prefix_resource WHERE id = cm.instance)
    WHEN m.name = 'scorm'  THEN (SELECT name FROM prefix_scorm WHERE id = cm.instance)
    WHEN m.name = 'survey'  THEN (SELECT name FROM prefix_survey WHERE id = cm.instance)
    WHEN m.name = 'url'  THEN (SELECT name FROM prefix_url  WHERE id = cm.instance)
    WHEN m.name = 'wiki' THEN (SELECT name FROM prefix_wiki  WHERE id = cm.instance)
    WHEN m.name = 'workshop' THEN (SELECT name FROM prefix_workshop  WHERE id = cm.instance)
   ELSE "Other activity"
END AS Activityname,
# cm.section AS Coursesection,
CASE
    WHEN cm.completion = 0 THEN '0 None'
    WHEN cm.completion = 1 THEN '1 Self'
    WHEN cm.completion = 2 THEN '2 Auto'
END AS Activtycompletiontype,
CASE
   WHEN cmc.completionstate = 0 THEN 'In Progress'
   WHEN cmc.completionstate = 1 THEN 'Completed'
   WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
   WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
   ELSE 'Unknown'
END AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
FROM prefix_course_modules_completion cmc
JOIN prefix_user u ON cmc.userid = u.id
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
JOIN prefix_course c ON cm.course = c.id
JOIN prefix_modules m ON cm.module = m.id
# skip the predefined admin and guest user
WHERE u.id > 2
# config reports filters
%%FILTER_USERS:u.username%%
%%FILTER_SEARCHTEXT:m.name:~%%
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%

ORDER BY u.username

System wide use of activities and resources

SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC

System Wide usage count of various course Activities

(Tested and works fine in Moodle 2.x) Like: Forum, Wiki, Blog, Assignment, Database,

  1. Within specific category
  2. Teacher name in course
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs

,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments

,(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 = 5 AND ctx.instanceid = c.id) AS Students

FROM prefix_course AS c
WHERE c.category IN ( 18)
ORDER BY Wikis DESC,Blogs DESC, Forums DESC

Assignment Activity

Note: The current Assignment module uses the tables with names starting with prefix_assign_. This is the module introduced in Moodle 2.3 and has been in use ever since then (2012).

The old, previous Assignment module used tables beginning with prefix_assignment_. This old module was removed and has not been supported since Moodle 2.7. If you have any modern version of Moodle, only use the queries below that are for the new module. If you are the author of one of the older assignment modules, please remove it to prevent confusion. Thanks.


Assignment type usage in courses

SELECT

CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments"

,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments

,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
#GROUP BY apc.plugin
) AS "File Assignments"

,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'onlinetext' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Online Assignments"

,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'pdf' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "PDF Assignments"

,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'offline' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Offline Assignments"

,(SELECT COUNT(*)
FROM prefix_assign_plugin_config AS apc
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
WHERE iassign.course = c.id AND apc.plugin = 'comments' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
) AS "Assignments Comments"

FROM prefix_assign AS assign
JOIN prefix_course AS c ON c.id = assign.course
GROUP BY c.id


All Ungraded Assignments

NOTE: This query is for the deprecated old Assignment module from Moodle 2.2, not the new Assignments module. Please update this query if you are the author or it will be removed as the 2.2 Assignment module is no longer supported since release 2.7. See: [1]

Returns all the submitted assignments that still need grading

select
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment"

from prefix_assignment_submissions as asb
join prefix_assignment as a ON a.id = asb.assignment
join prefix_user as u ON u.id = asb.userid
join prefix_course as c ON c.id = a.course
join prefix_course_modules as cm ON c.id = cm.course

where asb.grade < 0 and cm.instance = a.id
and cm.module = 1

order by c.fullname, a.name, u.lastname

All Ungraded Assignments w/ Link

Returns all assignments submitted by those with the student role that have the status of 'submitted', along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle.

This query is updated for use with Moodle 2.2 or later. Contributed by Carly J. Born, Carleton College

SELECT
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment",

concat('<a target="_new" href="%%WWWROOT%%/mod/assign/view.php?id=',
cm.id,
'&rownum=0&action=grader&userid=',
u.id,
'">Grade</a>')
AS "Assignment link"

FROM prefix_assign_submission sub
JOIN prefix_assign a ON a.id = sub.assignment
JOIN prefix_user u ON u.id = sub.userid
JOIN prefix_course c ON c.id = a.course AND c.id = %%COURSEID%%
JOIN prefix_course_modules cm ON c.id = cm.course
JOIN prefix_context cxt ON c.id=cxt.instanceid AND cxt.contextlevel=50
JOIN prefix_role_assignments ra ON cxt.id = ra.contextid AND ra.roleid=5 AND ra.userid=u.id

WHERE cm.instance = a.id AND cm.module = 22 AND sub.status='submitted'

ORDER BY c.fullname, a.name, u.lastname

NOTE: you will need to change the cm.module = 22 number to be the id number of the Assignment module in your site. By default, this is 1.

Assignments (and Quizzes) waiting to be graded

NOTE: This query is for the deprecated old Assignment module from Moodle 2.2, not the new Assignments module. Please update this query if you are the author or it will be removed as the 2.2 Assignment module is no longer supported since release 2.7. See: [2]


This report requires a YEAR filter to be added (Available when using the latest block/configurable_reports)

Which you can always remove, to make this query work on earlier versions.

The report includes:

  • number of quizzes
  • unFinished Quiz attempts
  • Finished Quiz attempts
  • number of students
  • number of Assignments
  • number of submitted answers by students
  • number of unchecked assignments (waiting for the Teacher) in a Course.
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments

,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'

,(SELECT COUNT(*)
FROM prefix_course_modules cm
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz' AND cm.course = c.id
GROUP BY cm.course
) AS 'nQuizzes'

,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish = 0
GROUP BY q.course) AS 'unFinished Quiz attempts'

,(SELECT COUNT(*)
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON q.id = qa.quiz
WHERE q.course = c.id
AND qa.timefinish > 0
GROUP BY q.course) AS 'finished quiz attempts'

,(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 = 5
AND ctx.instanceid = c.id
) AS nStudents


,(
SELECT count(a.id)
FROM prefix_assignment AS a
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) nAssignments

,(
SELECT count(*)
FROM prefix_assignment AS a
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) 'Open <br/>Assignments'

, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)'

,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'unChecked  <br/>Submissions'

,(
SELECT count(asb.id)
FROM prefix_assignment_submissions AS asb
JOIN prefix_assignment AS a ON a.id = asb.assignment
JOIN prefix_course_modules AS cm ON a.course = cm.course
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
) 'Submitted  <br/>Assignments'

FROM prefix_course AS c
LEFT JOIN (
SELECT course, count(*) AS iAssignments
FROM prefix_assignment AS a
GROUP BY a.course
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id

LEFT JOIN (
SELECT course, count(*) AS iOpenAssignments
FROM prefix_assignment AS a
WHERE FROM_UNIXTIME(a.timedue) > NOW()
GROUP BY a.course
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id

WHERE 1=1
#AND c.fullname LIKE '%תשעג%'
%%FILTER_YEARS:c.fullname%%
## You can enable the SEMESTER filter as well,
## by uncommenting the following line:
## %%FILTER_SEMESTERS:c.fullname%%
ORDER BY 'Open <br/>Assignments' DESC


Users who have overdue assignments

Contributed by: Randy Thornton

Shows a list of users who have not yet done an Assignment whose due date is past.

SELECT DISTINCT
u.username AS "User",
c.shortname AS "Course",
a.name AS "Assignment",
DATE_FORMAT(FROM_UNIXTIME(a.gradingduedate),'%Y-%m-%d %H:%i') AS "Due_date_UTC"

FROM prefix_user_enrolments ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS u ON u.id = ue.userid
JOIN prefix_assign a ON a.course = c.id
WHERE 
# pick your course but make sure it agrees with the c.id in the subselect
c.id = 2
#skip future dates
AND DATEDIFF(NOW(),FROM_UNIXTIME(a.gradingduedate)) > 0 
# only users who have not submitted 
AND ue.userid NOT IN 
 (SELECT asub.userid
  FROM prefix_assign_submission AS asub
  JOIN prefix_assign AS a ON a.id = asub.assignment 
  JOIN prefix_course c on a.course = c.id
  WHERE c.id = 2)

ORDER BY u.username, c.shortname


Rubrics without zero values in criteria

Contributed by Eric Strom

Rubric calculations in Moodle can fail to align with instructors expectations if they lack a zero value for each criterion used in the assessment. From documentation at https://docs.moodle.org/32/en/Rubrics#Grade_calculation:

"For example, when the teacher in the previous example chose both levels with 1 point, the plain sum would be 2 points. But that is actually the lowest possible score so it maps to the grade 0 in Moodle. TIP: To avoid confusion from this sort of thing, we recommend including a level with 0 points in every rubric criterion."

This report identifies rubrics having criteria without a zero value level and the courses they live in. This also refines to only assignments with active rubrics that are visible to students in the course. Links to the each rubric id is the direct link to edit the rubric. Fix by adding a zero level for each criteria that is missing it. In general, the grading changes that result will be in the students' favor.

Includes search filter of course idnumber.

SELECT cat.name AS Department, concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
c.id,'">',c.idnumber,'</a>') AS Course_ID,
c.fullname AS Course_Name,
concat('<a target="_new" href="%%WWWROOT%%/grade/grading/form/rubric/edit.php',CHAR(63),'areaid=',gd.areaid,'">',gd.areaid,'</a>') AS Rubric
FROM prefix_course AS c
JOIN prefix_course_categories AS cat
ON cat.id = c.category
JOIN prefix_course_modules AS cm
ON c.id=cm.course
JOIN prefix_context AS ctx
ON cm.id = ctx.instanceid
JOIN prefix_grading_areas AS garea
ON ctx.id = garea.contextid
JOIN prefix_grading_definitions AS gd
ON garea.id = gd.areaid
JOIN prefix_gradingform_rubric_criteria AS crit
ON gd.id = crit.definitionid
JOIN prefix_gradingform_rubric_levels AS levels
ON levels.criterionid = crit.id
WHERE cm.visible='1' AND garea.activemethod = 'rubric' AND (crit.id NOT IN
(SELECT crit.id
FROM prefix_gradingform_rubric_criteria AS crit
JOIN prefix_gradingform_rubric_levels AS levels
ON levels.criterionid = crit.id WHERE levels.score = '0'))

GROUP BY Rubric
ORDER BY Course_ID, Rubric

%%FILTER_SEARCHTEXT:c.idnumber:~%%

Who is using "Single File Upload" assignment

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,ass.name as "Assignment Name"

FROM
prefix_assignment as ass

JOIN
prefix_course as c ON c.id = ass.course

WHERE `assignmenttype` LIKE 'uploadsingle'


Assignment Dates with their Calendar events

Contributed by: Randy Thornton

This query can help you diagnose issues related to setting dues dates and other dates in the Assignment along with their corresponding dates that are put into the Calendar. Assignment puts only the Due date and the Grade by reminder date into the calendar.

SELECT
a.id AS "A_id",
a.name AS "Assignment_name",
IF(a.duedate = 0, 'None',DATE_FORMAT(FROM_UNIXTIME(a.duedate),'%Y-%m-%d %H:%i')) AS "Assign Due",
IF(a.gradingduedate = 0, 'None',DATE_FORMAT(FROM_UNIXTIME(a.gradingduedate),'%Y-%m-%d %H:%i')) AS "Assign Grade_by",
IF(a.cutoffdate = 0, 'None',DATE_FORMAT(FROM_UNIXTIME(a.cutoffdate),'%Y-%m-%d %H:%i')) AS "Assign Cutoff",
IF(a.allowsubmissionsfromdate = 0, 'None',DATE_FORMAT(FROM_UNIXTIME(a.allowsubmissionsfromdate),'%Y-%m-%d %H:%i')) AS "Assign Open",
e.modulename AS "Module_type",
CASE 
 WHEN e.eventtype = 'due' THEN CONCAT('Due: ',DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%Y-%m-%d %H:%i')) 
 WHEN e.eventtype = 'gradingdue' THEN CONCAT('GradingDue: ',DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%Y-%m-%d %H:%i'))
 WHEN e.eventtype IS NULL THEN '-'
 ELSE 'None'
END AS "Event Timestart"

FROM prefix_assign a 
LEFT JOIN prefix_event e ON a.id = e.instance 
WHERE e.modulename IS NULL OR e.modulename = 'assign'

ORDER BY a.id

Chat Activity

List the chats

Contributed by François Parlant This report gives the list of all chats with the name of the course and various ids needed for further queries.

The column "participants" is intended to work with an (optional) secondary report. If you don't need it, you can erase it. It produces a direct link to another (optional) report which will give you the current participants list to this chat.

select
c.shortname,
c.fullname,
ch.course,
ch.id,
# if you intend to use a secondary report to see the participants of a specific chat
# create the secondary report, check the id of the report in the url, and change the 21 in next line to your participant report's id
CONCAT('<a href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?id=21&filter_courses=', ch.id,'">Chat participants</a>') AS 'Course link',
ch.chattime

FROM
prefix_chat ch
INNER JOIN prefix_course c ON c.id = ch.course

ORDER BY ch.chattime, c.fullname


Participants to a chat (optional secondary report)

This version of the participant list is intended to work with a link given in the previous report.

  • User opens the report listing all the chats on the platform
  • user clicks on the link from the column "chat participant"
  • which open this report with a filter on the chatid

(careful, we are tweaking the coursefilter to carry instead the chatid: the displayed "course filter" will not work! but we need it)

SELECT
c.id AS courseid,
chu.chatid,
chu.userid AS 'chat user userid',
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email

FROM
prefix_user u
LEFT JOIN prefix_chat_users chu ON chu.userid = u.id
INNER JOIN prefix_course c ON c.id = chu.course

WHERE 1=1
%%FILTER_COURSES:chu.chatid%%
# you can also filter by course
# but don't put comment line between where and filter
# %%FILTER_COURSES:chu.course%%


ORDER BY c.fullname

List current participants to chat

Contributed by François Parlant

SELECT
c.id AS courseid,
chu.chatid,
chu.userid AS 'chat user userid',
c.fullname,
u.username,
u.firstname,
u.lastname,
u.email

FROM
prefix_user u
LEFT JOIN prefix_chat_users chu ON chu.userid = u.id
INNER JOIN prefix_course c ON c.id = chu.course

WHERE 1=1
%%FILTER_COURSES:chu.course%%

ORDER BY c.fullname

Choice Activity

Choice Results

Contributed by: Randy Thornton

Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.

SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
FROM prefix_choice AS h
JOIN prefix_course AS c ON h.course = c.id
JOIN prefix_choice_answers AS a ON h.id = a.choiceid
JOIN prefix_user AS u ON a.userid = u.id
JOIN prefix_choice_options AS o ON a.optionid = o.id

Database Activity

There are no reports yet for the Database activity.

Face to Face Activity

Get Face to Face sessions info, course info, user info. Note the xpath function is specific to Postgresql.

SELECT
    /* Session info */
    ftf_sd.timestart session_start_date,
    ftf_sd.timefinish session_end_date,
    ftf_s.id session_id,
    /* Course info */
    c.id course_id,
    c.shortname course_shortname,
    c.visible course_visible,
    (
        SELECT count(*)
        FROM prefix_facetoface ftf2
        WHERE ftf2.course = c.id
    ) facetoface_activities_in_course_count,
    /* FaceToFace activity-level info */
    ftf_s.facetoface facetoface_activity_id,
    ftf.name activity_name,
    CASE
        WHEN ftf_s.details LIKE '<%' THEN xpath('//text()', cast(ftf_s.details AS xml))::text
        ELSE ftf_s.details
    END session_description,
    /* User info */
    ftf_su.userid user_id,
    ftf_ss.signupid signup_id,
    ftf_ss.timecreated signup_date,
    u.username user_username,
    u.suspended user_suspended,
    u.username user_firstname,
    u.username user_lastname,
    u.email user_email,
    u.city
FROM prefix_facetoface_sessions ftf_s
    LEFT JOIN prefix_facetoface_sessions_dates ftf_sd ON ftf_sd.sessionid = ftf_s.id
    JOIN prefix_facetoface ftf ON ftf.id = ftf_s.facetoface
    JOIN prefix_course c ON c.id = ftf.course
    JOIN prefix_facetoface_signups ftf_su ON ftf_su.sessionid = ftf_s.id
    JOIN prefix_facetoface_signups_status ftf_ss ON (
        ftf_su.id = ftf_ss.signupid
        AND ftf_ss.superceded = 0
    )
    JOIN prefix_user u ON (
        u.id = ftf_su.userid
        AND u.deleted = 0
    )

Feedback Activity

List the answers to all the Feedback activities within the current course, submitted by the current user

SELECT /* crs.fullname as "Course name", f.name AS "Journal name", CONCAT(u.firstname,' ',UPPER(u.lastname)) as "Participant", */ /* include these fields if you want to check the composition of the recordset */
DATE_FORMAT(FROM_UNIXTIME(c.timemodified),'%W %e %M, %Y') as "Answer Date",
CASE i.typ WHEN 'label' THEN i.presentation ELSE i.name END as "Topic",  /* usually labels are used as section titles, so you'd want them present in the recordset */
v.value as "My Answer"

FROM prefix_feedback AS f
INNER JOIN prefix_course as crs on crs.id=f.course %%FILTER_COURSES:f.course%%
INNER JOIN prefix_feedback_item AS i ON f.id=i.feedback
INNER JOIN prefix_feedback_completed AS c on f.id=c.feedback %%FILTER_COURSEUSER:c.userid%%
LEFT JOIN prefix_feedback_value AS v on v.completed=c.id AND v.item=i.id
INNER JOIN prefix_user AS u on c.userid=u.id

WHERE c.id = %%COURSEID%% AND u.id = %%USERID%%  AND c.anonymous_response = 1  /* This clause limits the recordset to the current course and the current user and includes/ excludes the anonymous responses as needed */

ORDER BY f.id, c.timemodified, i.id


Show all Feedbacks from all courses for all users including showing names of anonymous users

Contributed by: Randy Thornton

Shows all Feedbacks in all Courses with all multi-choice questions and answers of all users including showing the username of anonymous users. Also shows truly anonymous users on the front page as 'Not-logged-in' users. This is a rough report, not a pretty report, and is limited to multiple-choice type questions, but is shows the answer number and the list of possible answers in raw form. I post it here as a basis for further reports, and also as away to get the identities of anonymous users if needed.

SELECT
c.shortname AS Course,
f.name AS Feedback,
# i.id AS Itemid,
i.name AS Itemname,
i.label AS Itemlabel,
CASE
 WHEN f.anonymous = 1 AND u.id != 0 THEN CONCAT(u.username, ' :ANON')
 WHEN fc.userid = 0 THEN 'Not-logged-in'
 ELSE u.username
END AS 'User',
DATE_FORMAT(FROM_UNIXTIME(fc.timemodified),'%Y-%m-%d %H:%i') AS "Completed",
v.value AS "Choice",
CASE
 WHEN i.typ = 'multichoice' THEN
     IF (  SUBSTRING(i.presentation,1,6)='d>>>>>',
	       SUBSTRING(i.presentation,7),
		   i.presentation)
 ELSE i.presentation
END AS "Answers",
i.typ,
i.dependitem,
i.dependvalue

FROM prefix_feedback f
JOIN prefix_course c ON c.id=f.course
JOIN prefix_feedback_item AS i ON f.id=i.feedback
JOIN prefix_feedback_completed fc ON f.id=fc.feedback
LEFT JOIN prefix_feedback_value v ON v.completed=fc.id AND v.item=i.id
LEFT JOIN prefix_user AS u ON fc.userid=u.id
WHERE i.typ != 'pagebreak'


Show all Feedbacks from all courses for all users with their answers

Contributed by: Randy Thornton

Shows all Feedbacks in all Courses with all multi-choice questions and answers of all users for multi-choice questions. It shows the possible answers, the number of the chosen answer and the text of the chosen answer by the user. As always, I disavow any prettiness here and you should update the fields as you need.

Known to work in Moodle 3.5 to 3.10.

SELECT
c.fullname as "Course",
f.name AS "Feedback",
CONCAT(u.firstname,'  ',u.lastname) as "User",
DATE_FORMAT(FROM_UNIXTIME(fc.timemodified), '%Y-%m-%d %H:%i') AS "When",
IF(i.typ = 'label', i.presentation, i.name) AS "Question",
# answers presentation string starts with these 6 characters:  r>>>>>
CASE WHEN i.typ = 'multichoice' THEN SUBSTRING(i.presentation,7) END AS "Possible Answers",

CASE i.typ WHEN 'multichoice' THEN v.value ELSE '-' END AS "Chosen Answer Num",
CASE v.value
  WHEN 1 THEN SUBSTRING(i.presentation, 7, POSITION('|' IN i.presentation) - 7)
  WHEN 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',2), '|',-1)
  WHEN 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',3), '|',-1)
  WHEN 4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',4), '|',-1)
  WHEN 5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',5), '|',-1)
  WHEN 6 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',6), '|',-1)
  WHEN 7 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',7), '|',-1)
  WHEN 8 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',8), '|',-1)
  WHEN 9 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(i.presentation, '|',9), '|',-1)
  ELSE CONCAT("More:", v.value)
END AS "Chosen Answer Text"

FROM prefix_feedback AS f
JOIN prefix_course AS c ON c.id=f.course
JOIN prefix_feedback_item AS i ON f.id=i.feedback
JOIN prefix_feedback_completed AS fc ON f.id=fc.feedback
LEFT JOIN prefix_feedback_value AS v ON v.completed=fc.id AND v.item=i.id
JOIN prefix_user AS u ON fc.userid=u.id

WHERE i.typ IN ('label', 'multichoice')

Forum Activity

Print all User's post in course Forums

%%COURSEID%% is a variable the is replace by the current CourseID you are running the sql report from. if you are using the latest block/configurable_reports ! (You can always change it to a fixed course or remove it to display all courses.)

SELECT
concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') As Fullname
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
,count(*) as Posts
,(SELECT count(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum as iforum ON iforum.id = ifd.forum  WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion

FROM prefix_forum_posts AS fp
JOIN prefix_user as u ON u.id = fp.userid
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course as c ON c.id = fd.course
WHERE fd.course = %%COURSEID%%
GROUP BY f.id,u.id
ORDER BY u.id


Forum use Count per Course -- not including News Forum!

SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course
ORDER BY total desc

Forum use Count per course by type -- not including News Forum!

SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
INNER JOIN prefix_course
ON prefix_course.id = prefix_forum.course
WHERE NOT(prefix_forum.type = 'news')
GROUP BY prefix_forum.course,prefix_forum.type
ORDER BY total desc

Forum activity - system wide

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
  FROM prefix_role_assignments AS ra
  JOIN prefix_user AS u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
,c.fullname as Course
,f.type
,(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 = 5 AND ctx.instanceid = c.id) AS Students
, fd.forum, f.name,count(*) AS cPostAndDisc
,(SELECT count(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion
FROM prefix_forum_posts AS fp
JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course AS c ON c.id = f.course
WHERE f.type != 'news' AND c.fullname LIKE '%2013%'
## WHERE 1=1
## %%FILTER_YEARS:c.fullname%%
## You can enable the SEMESTER filter as well,
## by uncommenting the following line:
## %%FILTER_SEMESTERS:c.fullname%%

GROUP BY fd.forum
ORDER BY count( * ) DESC


Activity In Forums

Trying to figure out how much real activity we have in Forums by aggregating: Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers, Number of Students, ratio between unique Student posts and the number of students in the Course...

SELECT c.fullname,f.name,f.type
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
,(SELECT Count( ra.userid ) AS Students
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid =5
AND ctx.instanceid = c.id
) AS StudentsCount
,(SELECT Count( ra.userid ) AS Teachers
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 'Teacher<br/>Count'
,(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 IN (3,5)
AND ctx.instanceid = c.id
) AS UserCount
, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
FROM prefix_forum as f
JOIN prefix_course as c ON f.course = c.id
WHERE `type` != 'news'
ORDER BY StudentPostUsage DESC

All Forum type:NEWS

SELECT f.id, f.name
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_forum AS f ON cm.instance = f.id
WHERE m.name = 'forum'
AND f.type = 'news'

All new forum NEWS items (discussions) from all my Courses

change "userid = 26" and "id = 26" to a new user id

SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
FROM prefix_forum_discussions as fd
JOIN prefix_forum as f ON f.id = fd.forum
JOIN prefix_course as c ON c.id = f.course
JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
WHERE fd.timemodified > ul.timeaccess
 AND fd.forum IN (SELECT f.id
 FROM prefix_course_modules AS cm
 JOIN prefix_modules AS m ON cm.module = m.id
 JOIN prefix_forum AS f ON cm.instance = f.id
 WHERE m.name = 'forum'
 AND f.type = 'news')
  AND c.id IN (SELECT c.id
   FROM prefix_course AS c
   JOIN prefix_context AS ctx ON c.id = ctx.instanceid
   JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
   JOIN prefix_user AS u ON u.id = ra.userid
   WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC


News Forum - Discussions Count

Which is actually... How much instructions students get from their teachers

SELECT c.shortname ,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
  FROM prefix_role_assignments AS ra
  JOIN prefix_user AS u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
FROM prefix_forum_discussions AS fd
INNER JOIN prefix_forum AS f ON f.id = fd.forum
INNER JOIN prefix_course AS c ON c.id = f.course
WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
GROUP BY fd.forum
ORDER BY count(fd.id) DESC

Number of Forum Posts by a Teacher

Cantidad de foros que han sido posteados por profesor. Queriamos saber cuales son las acciones del profesor dentro de los foros de cada curso, por ello se hizo este informe.

Number of forums that have been posted to by a teacher. We wanted to know what the teacher's actions are in the forums of each course, so this report was made.

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS curso,
CONCAT(u.firstname ,' ',u.lastname) AS Facilitador,

(SELECT COUNT( m.name ) AS COUNT FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS foros,

COUNT(*) AS Posts

FROM prefix_forum_posts AS fp
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
JOIN prefix_forum AS f ON f.id = fd.forum
JOIN prefix_course AS c ON c.id = fd.course
JOIN prefix_user AS u ON u.id = fp.userid

WHERE fp.userid =
(
select distinct prefix_user.id
from prefix_user
join prefix_role_assignments as ra on ra.userid = prefix_user.id
where ra.roleid = 3
and userid = fp.userid
limit 1
)

and c.shortname like '%2014-2-1%'
GROUP BY c.id, u.id

List all the Posts in all the Forums that got high rating

We setup a scale that let teachers and students Rate forum post with "Important, interesting, valuable, not rated" scale And then add a link to the following report at the beginning of the course "Link to all interesting posts"

SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',f.id,'">',f.name,'</a>') AS 'Forum name,
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fd.id,'#p',fp.id,'">',fp.subject,'</a>') AS 'Post link',
SUM(r.rating) AS 'Rating'
FROM mdl_rating AS r
  JOIN mdl_forum_posts AS fp ON fp.id = r.itemid
  JOIN mdl_forum_discussions AS fd ON fd.id = fp.discussion
  JOIN mdl_forum AS f ON f.id = fd.forum
WHERE r.component = 'mod_forum' AND r.ratingarea = 'post' AND f.course = %%COURSEID%%
GROUP BY r.itemid
ORDER BY SUM(r.rating) DESC

List all the Posts in all Discussions of a single Forum

This report is used to help export all the student's posts and discussions of a single forum, by passing the course module id as a parameter to the report using "&filter_var=cmid"

SELECT
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=', f.id, '">', f.name, '</a>') AS 'Forum name',
fd.name AS 'Discussion',
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/discuss.php?d=', fd.id, '#p', fp.id, '">', fp.subject, '</a>') AS 'Post (link)',
fp.message

FROM mdl_forum_posts AS fp
  JOIN mdl_forum_discussions AS fd ON fd.id = fp.discussion
  JOIN mdl_forum AS f ON f.id = fd.forum
  JOIN mdl_course_modules AS cm ON cm.module = 9 AND cm.instance = f.id
WHERE cm.id = %%FILTER_VAR%%
ORDER BY f.id, fd.id

Glossary Activity

All glossary entries

Shows the entries from all glossaries in the site with student and times.

Contributed by: Randy Thornton

SELECT
c.shortname AS "Course",
g.name AS "Glossary",
# g.intro AS "Introduction",
u.username,
ge.concept AS "Concept",
ge.definition AS "Definition",
IF(ge.approved=1,'Yes','No') AS "Approved",
DATE_FORMAT(FROM_UNIXTIME(ge.timecreated), '%Y-%m-%d %H:%i' ) AS "Created",
DATE_FORMAT(FROM_UNIXTIME(ge.timemodified), '%Y-%m-%d %H:%i' ) AS "Modified"
FROM prefix_glossary_entries ge
JOIN prefix_glossary g ON g.id = ge.glossaryid
JOIN prefix_user u ON u.id = ge.userid
JOIN prefix_course c ON c.id = g.course

H5P Activities

There are no reports yet for the H5P activities (core and non-core).

Lesson Activity

Lesson Questions

Show all the Questions with their Answers for all the Lessons in a site. This shows the questions as set up, not student responses. This is known to work in 3.11 but note that this may change in Moodle 4.0 or 4.1 due to plans to have the Lesson module use the standard question bank questions.

Contributed by: Randy Thornton

SELECT
c.shortname AS "Course",
l.name AS "Lesson_Name",
# NOTE: the line below is formatted for the Ad-hoc database queries plugin
# if using Config Reports you can edit this for the proper HTML format or remove it
CONCAT("%%WWWROOT%%/mod/lesson/view.php%%Q%%id=",cm.id) AS "Lesson_Name_link_url",
p.title AS "Page_Title",
p.contents AS "Question",
a.grade,
a.score,
a.answer,
a.response
FROM prefix_lesson_answers a
JOIN prefix_lesson l ON l.id = a.lessonid
JOIN prefix_lesson_pages p ON p.id = a.pageid AND p.lessonid = l.id
JOIN prefix_course c ON c.id = l.course
JOIN prefix_course_modules cm ON cm.instance = l.id
JOIN prefix_modules m ON m.id = cm.module
WHERE m.name = 'lesson'
# to limit this to a single question type add this eg true-false is 2
# AND p.qtype = 2 
# for just one lesson then put its course module id from the url here
# AND cm.id = #


If you want to have a column for the Question type, you can add this to the SELECT statement:

CASE p.qtype 
 WHEN 1 THEN 'Short answer'
 WHEN 2 THEN 'True/False'
 WHEN 3 THEN 'Multi-choice'
 WHEN 5 THEN 'Matching'
 WHEN 8 THEN 'Numerical'
 WHEN 10 THEN 'Essay'
 ELSE p.qtype
END AS "Question type",

LTI External Tool Activity

LTI (External Tool) Usage by Course Start Date

Contributed by Elizabeth Dalton, Granite State College

Report of number of inclusions of LTI (External Tool) Usage activities in courses, filtered by course start date.

SELECT

CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'

, cc.name AS 'Category'
, lti.name AS 'Sample Activity Name'
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
, COUNT(DISTINCT cm.id) AS 'Resources Used'
#, FROM_UNIXTIME(cm.added) AS 'resource added'


FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'lti'

JOIN prefix_course AS c ON c.id = cm.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_lti AS lti ON lti.id = cm.instance
WHERE
1

%%FILTER_STARTTIME:c.startdate:>%%
%%FILTER_ENDTIME:c.startdate:<%%

GROUP BY c.shortname, m.name
ORDER BY c.startdate, c.shortname

Quiz Activity

Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes

SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
                co.fullname AS course_fullname, co.shortname AS course_shortname,
                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
                u.firstname, u.lastname, u.email,
FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
                qu.course = co.id AND
                co.id = ct.instanceid AND
                ra.roleid = re.id AND
                re.name = 'Teacher' AND
                ra.contextid = ct.id AND
                ra.userid = u.id

SELECT Count('x') As NumOfStudents
                                FROM prefix_role_assignments a
                                JOIN prefix_user u ON userid = u.id
                                WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)

Number of Quizes per Course

SELECT count(*)
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes

FROM prefix_course_modules cm
JOIN prefix_course c ON c.id = cm.course
JOIN prefix_modules as m ON m.id = cm.module
WHERE m.name LIKE 'quiz'
GROUP BY c.id

List all MultiAnswer (Cloze) Questions

SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
,question.id question_id, question.questiontext
FROM  prefix_question question
JOIN prefix_quiz_question_instances qqi ON question.id = qqi.question
JOIN prefix_quiz quiz ON qqi.quiz = quiz.id
WHERE  `qtype` LIKE  'multianswer'

Parse MultiAnswer (Cloze) Question Responses and Display in Columns

(Contributed by Laura DiFiore, July 2021, Moodle 3.97+) In this example, I am using a 4-part Cloze question for stenography students to keep track of their writing practice sessions. In mdl_question_attempts, their answers are stored like this:

part 1: 84; part 2: 92; part 3: 2; part 4: 98
part 1: 85; part 2: 107; part 3: 0; part 4: 100
SELECT  
    SUBSTRING_INDEX(SUBSTRING_INDEX(responsesummary, ';', 1), ' ', -1) as Drill,
    SUBSTRING_INDEX(SUBSTRING_INDEX(responsesummary, ';', 2), ' ', -1) as WPM,
    SUBSTRING_INDEX(SUBSTRING_INDEX(responsesummary, ';', 3), ' ', -1) as Mistakes,
    SUBSTRING_INDEX(SUBSTRING_INDEX(responsesummary, ';', 4), ' ', -1) as Accuracy
FROM `mdl_question_attempts` WHERE questionid=21
Drill	WPM	mistakes	accuracy
84	92	2	98
85	107	0	100

List courses with MANUAL grades

Which is basically and indication to teachers using Moodle to hold offline grades inside Moodle's Gradebook, So grades could be uploaded into an administrative SIS. Use with Configurable Reports.

SELECT COUNT( * )
,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course
FROM  prefix_grade_items AS gi
JOIN prefix_course as c ON c.id = gi.courseid
WHERE  `itemtype` =  'manual'
GROUP BY courseid

List the users that did not take the Quiz

Do not forget to change "c.id = 14" and q.name LIKE '%quiz name goes here%'

SELECT
user2.id AS ID,
ul.timeaccess,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.username AS IDNumber,
user2.institution AS Institution,

IF (user2.lastaccess = 0,'never',
DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess

,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess

,(SELECT r.name
FROM  prefix_user_enrolments AS uenrol
JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
JOIN prefix_role AS r ON e.id = r.id
WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName

FROM prefix_user_enrolments AS ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS user2 ON user2 .id = ue.userid
LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id
WHERE c.id=14 and ue.userid NOT IN (SELECT qa.userid FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON qa.quiz = q.id
JOIN prefix_course AS c ON q.course = c.id
WHERE c.id = 14 AND q.name LIKE '%quiz name goes here%')


Users who have not yet taken a quiz

Contributed by: Randy Thornton

This is a stripped-down version of the query above, showing users in a course and the quizzes they have not yet taken.

SELECT DISTINCT
u.username AS "User",
c.shortname AS "Course",
q.name AS "Quiz"

FROM
prefix_user_enrolments ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS u ON u.id = ue.userid
JOIN prefix_quiz q ON q.course = c.id
WHERE 
# specify course but be sure it matches the c.id in the subselect
c.id = #
# exclude users who have an attempt
AND ue.userid NOT IN 
 (SELECT qa.userid
  FROM prefix_quiz_attempts AS qa
  JOIN prefix_quiz AS q ON qa.quiz = q.id
  JOIN prefix_course c on q.course = c.id
  WHERE c.id = #
 )
  
ORDER BY u.username, c.shortname, q.name

List Questions in each Quiz

SELECT quiz.id,quiz.name, q.id, q.name
FROM mdl_quiz AS quiz
JOIN mdl_question AS q ON FIND_IN_SET(q.id, quiz.questions)
WHERE quiz.course = %%COURSEID%%
ORDER BY quiz.id ASC

Note: this query does not work in Moodle 2.8+. There is no mdl_quiz.questions field. It will need to be rewritten to use the usage/contextid organization.

Here is a version for Moodle 3.x

SELECT cm.id 'cmid', quiz.id 'quiz id'
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/quiz/edit.php?cmid=',
	   cm.id, '">', quiz.name, '</a>') AS 'edit quiz'
,q.id 'qid', q.name 'question name'
FROM mdl_quiz AS quiz
JOIN mdl_course_modules cm ON cm.instance = quiz.id AND cm.module = 33 # 33=quiz mdl_modules
JOIN mdl_quiz_slots qs ON qs.quizid = quiz.id
JOIN mdl_question AS q ON q.id = qs.questionid
WHERE quiz.course = %%COURSEID%%
ORDER BY quiz.id ASC

Quiz activity research

This report was made to extract student full activity in quizzes for an academic research about adapting instructional design teaching methods in online learning. The students do not use the Quiz module as a standard quiz but more as Study booklets or mini courses with embedded questions and hints to assist students evaluate their progress (Similar to what you expect to find in a SCORM activity)

SELECT
cm.course "course_id", cm.id "moduel_id", q.id "quiz_id", q.name "quiz_name",

CASE q.grademethod
      WHEN 1 THEN "GRADEHIGHEST"
      WHEN 2 THEN "GRADEAVERAGE"
      WHEN 3 THEN "ATTEMPTFIRST"
      WHEN 4 THEN "ATTEMPTLAST"
END "grade method"

, q.attempts "quiz_attempts_allowed", cm.groupmode "group_mode"
, qa.id "attempt_id", qa.state "attempt_state", qa.sumgrades "attempt_grade", qg.grade "user_final_grade", q.grade "quiz_max_grade"
,(SELECT GROUP_CONCAT(g.name) FROM mdl_groups AS g
JOIN mdl_groups_members AS m ON g.id = m.groupid WHERE g.courseid = q.course AND m.userid = u.id) "user_groups",
DATE_FORMAT(FROM_UNIXTIME(qa.timestart), '%d-%m-%Y %h:%k') "attempt_start",
DATE_FORMAT(FROM_UNIXTIME(qa.timefinish), '%d-%m-%Y %h:%k') "attempt_finish",
u.id "user_id", u.firstname, u.lastname,
question.id "question_id", question.name "question_name",
qas.state "question_step_state",qas.fraction "question_grade", qh.hint, question.qtype "question_type"

FROM mdl_quiz as q
JOIN mdl_course_modules as cm ON cm.instance = q.id and cm.module = 14
JOIN mdl_quiz_attempts qa ON q.id = qa.quiz
LEFT JOIN mdl_quiz_grades as qg ON qg.quiz = q.id and qg.userid = qa.userid
JOIN mdl_user as u ON u.id = qa.userid
JOIN mdl_question_usages as qu ON qu.id = qa.uniqueid
JOIN mdl_question_attempts as qatt ON qatt.questionusageid = qu.id
JOIN mdl_question as question ON question.id = qatt.questionid
JOIN mdl_question_attempt_steps as qas ON qas.questionattemptid = qatt.id
LEFT JOIN mdl_question_hints as qh ON qh.questionid = q.id
#WHERE q.id = "SOME QUIZ ID"
WHERE cm.course = "SOME COURSE ID"

Quiz Usage in Courses by Date

Contributed by Elizabeth Dalton, Granite State College

This report lists the courses containing quizzes with the course start date between the two values, and provides a summary of the types of questions in the quizzes in each course and whether question randomization and answer randomization functions were used.

"Multiple Choice" questions include true/false and matching question types.

"Short Answer" are questions that accept a single phrase.

"Other" questions include fixed numerical, calculated, essay, and various drag and drop types.

"Min Quiz Age" and "Max Quiz Age" provide data about the last modified date for the quizzes in the course, compared to the course start date. The values are expressed in units of days. A negative value indicates that a quiz was edited after the start of the course. A value greater than 90 days indicates that the quiz may have been used in an earlier term (cohort) without modification.

Note: In Configurable Reports, the Date Filter is not applied until the "Apply" button is clicked.

SELECT

c.shortname AS 'Course'
#, u.lastname AS 'Instructor'
, COUNT(DISTINCT q.id) AS 'Quizzes'
, COUNT(DISTINCT qu.id) AS 'Questions'
, SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 ))  AS 'multichoice'

, SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'

, COUNT( qu.id) - SUM(IF (qu.qtype = 'multichoice', 1, 0 )) - SUM(IF (qu.qtype = 'truefalse', 1, 0 )) - SUM(IF (qu.qtype = 'match', 1, 0 )) - SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'Other'

, (SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 )))/COUNT( qu.id) AS 'Percent MC'

#, SUM(IF (qu.qtype = 'numerical', 1, 0 )) AS 'numerical'
#, SUM(IF (qu.qtype LIKE 'calc%', 1, 0 )) AS 'calculated'
#, SUM(IF (qu.qtype = 'random', 1, 0 )) AS 'random'
#, SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'
#, SUM(IF (qu.qtype = 'essay', 1, 0 )) AS 'essay'


, IF(q.shufflequestions > 0,'Yes','No') AS 'Randomized Questions'
, IF(q.shuffleanswers > 0,'Yes','No') AS 'Randomized Answers'

#, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
#, FROM_UNIXTIME(MIN(q.timemodified)) AS 'Last Modified'

#, DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(MIN(q.timemodified))) AS 'Quiz age'

, MIN(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Min Quiz Age'
, MAX(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Max Quiz Age'

#, SUM(IF (DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified)) < 90, 1,0)) AS 'new quizzes'

FROM prefix_quiz AS q
JOIN prefix_course AS c on c.id = q.course
JOIN prefix_quiz_question_instances AS qqi ON qqi.quiz = q.id
LEFT JOIN prefix_question AS qu ON qu.id = qqi.question

WHERE
1
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.startdate:<%%

GROUP BY c.id

ORDER BY c.shortname

Student responses (answers) to quiz questions

(Contributed by Juan F with help from Tim hunt and fellow Moodlers on the forums) A report that targets a specific quiz for all of our Biology courses, a summary of all questions and how many students get them right/wrong.

SELECT
    concat( u.firstname, " ", u.lastname ) AS "Student Name",
    u.id,
    quiza.userid,
    q.course,
    q.name,
    quiza.attempt,
    qa.slot,
    que.questiontext AS 'Question',
    qa.rightanswer AS 'Correct Answer',
    qa.responsesummary AS 'Student Answer'

FROM mdl_quiz_attempts quiza
JOIN mdl_quiz q ON q.id=quiza.quiz
JOIN mdl_question_usages qu ON qu.id = quiza.uniqueid
JOIN mdl_question_attempts qa ON qa.questionusageid = qu.id
JOIN mdl_question que ON que.id = qa.questionid
JOIN mdl_user u ON u.id = quiza.userid

WHERE q.name = "BIO 208 Post Test Assessment"
AND q.course = "17926"

ORDER BY quiza.userid, quiza.attempt, qa.slot

Questions which are tagged within a course/quiz

Calculates subgrades for tags in the each of the quizzes in a course. Contributed by Daniel Thies in https://moodle.org/mod/forum/discuss.php?d=324314#p1346542

SELECT
    quiz.name AS quiz,
    t.rawname AS tag,
    CONCAT('<a target="_new" href="%%WWWROOT%%/mod/quiz/review.php?attempt=',
            MAX(quiza.id),'">',u.firstname,' ',u.lastname,'</a>') AS student,
    CAST(SUM(qas.fraction) as decimal(12,1)) AS correct,
    CAST(SUM(qa.maxmark) as decimal(12,1)) AS maximum,
    CAST(SUM(qas.fraction)/SUM(qa.maxmark)*100 as decimal(4,2)) AS score
FROM prefix_quiz_attempts quiza
JOIN prefix_user u ON quiza.userid = u.id
JOIN prefix_question_usages qu ON qu.id = quiza.uniqueid
JOIN prefix_question_attempts qa ON qa.questionusageid = qu.id
JOIN prefix_quiz quiz ON quiz.id = quiza.quiz
JOIN prefix_tag_instance ti ON qa.questionid = ti.itemid
JOIN prefix_tag t ON t.id = ti.tagid
JOIN (SELECT MAX(fraction) AS fraction, questionattemptid
        FROM prefix_question_attempt_steps
        GROUP BY questionattemptid) qas ON qas.questionattemptid = qa.id
WHERE quiz.course = %%COURSEID%%
GROUP BY quiza.userid,
    quiza.quiz,
    quiz.name,
    u.firstname,
    u.lastname,
    ti.tagid,
    t.rawname
ORDER BY quiza.quiz, t.rawname, u.lastname, u.firstname, score

SCORM Activity

SCORM Usage by Course Start Date

Contributed by Elizabeth Dalton, Granite State College

Report of number of inclusions of SCORM activities in courses, filtered by course start date.

SELECT

CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'

, cc.name AS 'Category'
, scm.name AS 'Sample Activity Name'
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
, COUNT(DISTINCT cm.id) AS 'Resources Used'
#, FROM_UNIXTIME(cm.added) AS 'resource added'


FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'SCO%'

JOIN prefix_course AS c ON c.id = cm.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
JOIN prefix_scorm AS scm ON scm.id = cm.instance

WHERE
1

%%FILTER_STARTTIME:c.startdate:>%%
%%FILTER_ENDTIME:c.startdate:<%%

GROUP BY c