This is a must-have plugin that will make your life easier.

Install it on your staging and production servers if required.

https://moodle.org/plugins/report_customsql

The plugin allows you to run SQL queries on your database to check or report on data.

Site administration > Reports > Ad-hoc database queries

Once you have installed the plugin you can create your own SQL scripts.

These SQL scripts can read data from the Moodle database. 

Thee scripts look like this.

SELECT USER.firstname AS Firstname, USER.lastname AS Lastname, USER.email AS Email, USER.city AS City, course.fullname AS Course ,(SELECT name FROM prefix_role WHERE id=asg.roleid) FROM prefix_user AS USER, prefix_course AS course, prefix_role_assignments AS asg INNER JOIN prefix_context AS context ON asg.contextid=context.id WHERE context.contextlevel = 50 AND USER.id=asg.userid AND context.instanceid=course.id

This particular script reports a site global list of all users enrolled in each course


By learning about the database structure and learning how to crate SQL scripts, you can interrogate the Moodle database and produce your own custom reports. 

See https://docs.moodle.org/37/en/Custom_SQL_queries_report for more details.

You may not need this straight away, but have it ready when the time comes.

Example SQL can be found here: https://docs.moodle.org/22/en/ad-hoc_contributed_reports

 

 

3 Comments

  1. Sekar G

    Very Good. if you have more report queries. please publihed.

    • Chris Richter

      Hi Sekar,
      Thank you.
      There are more at ​https://docs.moodle.org/22/en/ad-hoc_contributed_reports

  2. Sekar G

    I want to learn about Ad-hoc database queries. If you have any course.

    Let me know.

Submit a Comment