Open LMS Admin Reports

The reports allow students, teachers, and administrators to view data about activity in a course or group of courses that is relevant to them. The Admin reports aggregate data site administrators and department heads on their courses and programs as a whole.

Access the Admin reports

Steps: Administration > Reports > Admin SQL

The Admin reports allows an administrator to create a custom ad-hoc report based on an SQL query for a MySQL database.

1. From the site front page Admin > Reports > Admin SQL.
2. Create a query in the text box to create a new report.

   All queries must be written for a MySQL database. See the MySQL documentation for syntax structure for a query. See the Query Overview for examples.

3. Select Generate Report.
4. Use any additional features to add to the report or save the query.
   • If you have run previous reports in the same session, the Generate Report button may be hidden and replaces with the Filter button. Select Filter to display the report query text area and the Generate Report button.
   • Select Reset to clear the query text area of any SQL report and start over or load a saved query.
   • The Browse Schema button allows the administrator to view the database schema for their site as well as the structure of each table. The initial load of the schema can take a few seconds. To view a table’s structure, select the table in the right-hand column and you view the table structure in the left-hand column.
   • To save a query after you have generated a report from it, type the Name for the query and select Save Query. The report name now shows up in the saved query dialog when you select the Saved Queries button.
   • To load a saved query, select Saved Queries. Select the saved query you want to load into the text area. The saved query overwrites any code that has been entered in the query text area. Select Generate Report to display the report.
   • To delete a saved query, select Saved Queries and then select the Delete (X) icon. Select OK on the confirmation.
   • Tabular data for all reports can be exported to a CSV file using the Export menu on the bottom of the page.
     

Query overview

All field names that you want to display as columns in the report must be specified and/or aliased. You can also use wildcards (*) in your queries. For example,

SELECT * FROM mdl_user returns all users in the database. You can drill down further using tables and additional field information.

For example,

SELECT u.id FROM mdl_user u WHERE u.id = 1

Aggregate function fields must be aliased. For example, the following query is valid:

SELECT COUNT (id) as user_count FROM mdl_user

While the following query is not valid:

SELECT COUNT(id) FROM mdl_user

If a query has a colon in it then this must be concatenated with the rest of the string. This usually occurs when you are searching capabilities and plugins configuration variables. An example query is below:

SELECT
(SELECT COUNT(id) FROM mdl_course) – 1 AS courses,
(SELECT COUNT(id) FROM mdl_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT (DISTINCT ra.userid)
FROM mdl_role_capabilities rc
JOIN mdl_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 mdl_role_assignments) AS enrollments,
(SELECT COUNT(id) FROM mdl_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM mdl_resource) AS resources,
(SELECT COUNT(id) FROM mdl_question) AS questions

The tables that start with in the database schema are block collector tables and contain the collected data points as set in the Collect block’s global settings.