Custom SQL queries report
Created by The Open University
This admin report plugin allows Administrators to set up arbitrary database queries to act as ad-hoc reports. Reports can be of two types: either run on demand, or scheduled to run automatically. Other users with the right capability can go in and see a list of queries that they have access to. Results can be viewed on-screen or downloaded as CSV.
- 1 Installing this report
- 2 Screen shots
- 3 Interface for normal users
- 4 Interface for administrators
- 5 Share your interesting queries here
- 6 See also
Installing this report
Follow the generic Installing plugins documentation.
Here are two example screen shots, showing the two main screens.
(Note, these screen shots are in the OU theme, not the standard Moodle theme, and iCMA is OU-jargon for quiz.)
Interface for normal users
List of available queries
Users with the report/customsql:view capability can access the list of reports in the admin block. Each query is accessible only to a certain people. There are three levels of access:
- Available to any one who can access the report at all (those with report/customsql:view).
- Available to people who are able to see other system reports (those with moodle/site:viewreports)
- Available to administrators only (those with moodle/site:config)
When you go to the list, it will only show the queries you have access to. There is a note beside each query saying when it was last run, and how long it took to generate.
The list shows on-demand and scheduled queries separately.
Running an on-demand query
To run an on-demand query, click on its name in the list of queries.
The query will be run, and the results will be displayed as a table. Any URLs in the table will automatically be made into hyperlinks.
A description of the query may appear above the table.
The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.
Viewing the results of scheduled queries
Scheduled queries can work in one of two ways. Either each run of the report generates and entire table of reasults, or each run just creates one line of results, and the report builds up a row at a time.
When you click the name in the list of queries, you get taken to a display of the latest results, just like in the on-demand case.
However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.
Interface for administrators
Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.
Additional controls in the staff interface
Administrators get shown who each report in the list is available to.
They also get an edit and a delete icon next to each query.
There is an Add new query button at the end of the list of queries.
When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.
Adding or editing a query
When you click the Add new query button, you get taken to an editing form that lets you define the query.
You must give the query a name.
You can optionally enter a description that is displayed above the results table. You should use this to explain what the results of the query mean.
You must enter the SQL to generate the results you want displayed. This must be an SQL select statement. You must use the prefix prefix_ for table names. It should not be possible to enter any SQL that would alter the contents of the database.
You choose who you want the query to be accessible to.
You choose whether the query should be run on-demand or scheduled weekly or monthly. If the report is scheduled, you can say whether the each run returns one row to be added to a single table, or whether each run generates a separate table.
When you save the new query, the SQL is checked to make sure that it will execute without errors. If you have said that the report will only return a single row, this is also checked. After saving the query, if this was a manual query, you are taken to the query results page, so you can see what the results look like. If it was an automatic query or if you cancel the form, you are taken to the list of available queries.
Editing an existing query uses the same form as for adding a new query, but to change the properties of an existing query.
Note that at the OU, weeks start on Saturday. If you don't like that, there is a fairly obvious constant to hack at the top of locallib.php.
Deleting a query
When you click the delete icon or link for a query, you are taken to a confirmation page that shows you SQL of the query you are about to delete. The query is only deleted if you click Yes on the confirmation page.
After deleting a query, you are taken back to the list of queries.
If you come up with any interesting SQL to custom reports, you can share it here.
Students that are in a course where groups are turned on, but have not been assigned to a group yet
This query was set to run daily to provide a report of students in a course that uses groups but have not yet been assigned to a group and provide a link to the view to assign them to a group. Teacher/Non-Editing teacher roles are filtered out.
SELECT DISTINCT u.username, CONCAT( u.firstname, " ", u.lastname ) AS NAME, ic.shortname AS "Course Code", CONCAT("%%WWWROOT%%/enrol/users.php%%Q%%id=",ic.id, "&action=addmember&user=", u.id) AS "Link to Add to Group" FROM prefix_course ic JOIN prefix_context con ON con.instanceid = ic.id JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50 JOIN prefix_role r ON ra.roleid = r.id JOIN prefix_user u ON u.id = ra.userid LEFT JOIN prefix_groups_members gm ON u.id = gm.userid WHERE ic.visible = 1 AND ic.shortname NOT LIKE '%META%' AND ic.shortname NOT LIKE '%INOL%' AND u.username NOT LIKE 'firstname.lastname@example.org' AND ic.groupmode <> '0' AND gm.userid IS NULL AND r.id NOT IN (3,4) ORDER BY ic.shortname
Quiz attempts in the last week/month
Set this up as a scheduled report:
SELECT COUNT(*) FROM prefix_quiz_attempts WHERE timefinish > %%STARTTIME%% AND timefinish <= %%ENDTIME%% AND preview = 0
This must be set up as a Scheduled, on the first day of each week or Scheduled, on the first day of each month report, or it will not work.
This report shows roughly the same usage statistics that are sent to moodle.org when you register your site. The ones that are aggregated at http://moodle.org/stats/. (The only difference is that the registration form does not do AND confirmed = 1 for some reason.
SELECT (SELECT COUNT(id) FROM prefix_course) - 1 AS courses, (SELECT COUNT(id) FROM prefix_user WHERE deleted = 0 AND confirmed = 1) AS users, (SELECT COUNT(DISTINCT ra.userid) FROM prefix_role_capabilities rc JOIN prefix_role_assignments ra ON ra.roleid = rc.roleid WHERE rc.capability IN ('moodle/course:upd' || 'ate', 'moodle/site:doanything')) AS teachers, (SELECT COUNT(id) FROM prefix_role_assignments) AS enrolments, (SELECT COUNT(id) FROM prefix_forum_posts) AS forum_posts, (SELECT COUNT(id) FROM prefix_resource) AS resources, (SELECT COUNT(id) FROM prefix_question) AS questions
There are two interesting queries in this blog post When do students submit their online tests?.
Monthly Usage by Role
This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.
SELECT MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month, YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year, prefix_role.name AS user_role, COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users FROM prefix_stats_user_monthly INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id WHERE prefix_context.contextlevel = 50 AND `prefix_stats_user_monthly`.`stattype` = 'activity' AND prefix_stats_user_monthly.courseid <>1 GROUP BY MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_stats_user_monthly.stattype, prefix_role.name ORDER BY YEAR(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), MONTH(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), prefix_role.name
You must have Statistics turned on to be able to generate any data with this report.
PostgreSQL version of the Monthly Usage by Role query:
SELECT EXTRACT(MONTH FROM to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_month, EXTRACT(YEAR FROM to_timestamp(prefix_stats_user_monthly.timeend)) AS calendar_year, prefix_role.name AS user_role, COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users FROM prefix_stats_user_monthly INNER JOIN prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid INNER JOIN prefix_context ON prefix_role_assignments.contextid = prefix_context.id INNER JOIN prefix_role ON prefix_role_assignments.roleid = prefix_role.id WHERE prefix_context.contextlevel = 50 AND prefix_stats_user_monthly.stattype = 'activity' AND prefix_stats_user_monthly.courseid <>1 GROUP BY EXTRACT(MONTH FROM to_timestamp(prefix_stats_user_monthly.timeend)), EXTRACT(YEAR FROM to_timestamp(prefix_stats_user_monthly.timeend)), prefix_stats_user_monthly.stattype, prefix_role.name ORDER BY EXTRACT(YEAR FROM to_timestamp(prefix_stats_user_monthly.timeend)), EXTRACT(MONTH FROM to_timestamp(prefix_stats_user_monthly.timeend)), prefix_role.name
Show all Quiz results across a site
I'm sure someone will be able to improve upon this, but this is a query that will show Quiz results across a site. --Stuart Mealor 22:07, 9 April 2010 (UTC)
SELECT prefix_grade_items.itemname, prefix_grade_items.grademax, ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade, prefix_user.firstname, prefix_user.lastname, prefix_user.username FROM prefix_grade_grades INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id WHERE (prefix_grade_items.itemname IS NOT NULL) AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual') AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL) AND (prefix_grade_grades.timemodified IS NOT NULL) AND (prefix_grade_grades.finalgrade > 0) AND (prefix_user.deleted = 0)