Data and E-Learning Part 10: Ad-hoc database queries plugin
So far in this article series we have seen how to extract data from a Moodle installation using the tools that come with Moodle by default. We will now turn our attention to a powerful plugin that allows you to query the Moodle database; the Ad-hoc database queries plugin. In this article, we will look at how to install the plugin and provide an overview of its use. In future articles we will explore how you could use the plugin to get data from your Moodle installation and how you could use that data.
Ad-hoc database queries
The Ad-hoc database queries plugin is a free plugin that allows you to make read-only queries of the Moodle database using SQL commands. As it is read-only, you can view information in the database without the risk of causing any unwanted changes. The plugin allows you to use queries to get all sorts of information out of your Moodle installation. You will need some understanding of SQL and how the Moodle database is structured to use this plugin. We won’t be explaining SQL or the Moodle database structure in this article series; we will just look at how the plugin can be used, the sort of data you can extract using it, and what you might do with that data.
Installing the Ad-hoc database queries plugin
You can download the Ad-hoc database queries plugin from its plugin page: https://moodle.org/plugins/report_customsql. Make sure to download the correct version for your Moodle installation. Once downloaded, follow the generic instructions for installing plugins.
Using the Ad-hoc database queries plugin
Once installed, you can access the Ad-hoc database queries plugin from the Reports area in your Site administration.
The plugin has a good page in the Moodle Docs that tells you how to use it. Here we will just provide an overview. On the plugin page you will see the categories of queries that are available. You can create these categories yourself to organise your queries – only the ‘Miscellaneous’ category will be available when you first install the plugin.
Clicking the arrow next to a category will expand it so that you can view the available queries.
Query scheduling
Queries can be either scheduled or on-demand. On-demand queries are only run when an administrator runs them. They will provide the latest available data at the time they are run. Scheduled queries will run at the scheduled time. This can be daily, weekly or monthly. The report will contain data accurate at the time the report is run. The report will be linked from the query and it can be emailed to a specified user.
Running a query
Find the query in the correct category and then click on its name. This will take you through to the query results, which are displayed in a table (1). Note that there is a hard limit of 5000 rows returned, although this may be lower depending on your site settings. From the results page you can edit (2) and delete (3) the query. You can also download the results (4). Select your desired download format from the drop-down menu and click Download.
Understanding the results
The results that are returned will depend on how you have written your query and the data that is in your Moodle database. The bold items in the top row are your column headings. Below this is each row of data that the query has returned. In this example you can see details for two courses – their id, full name, short name and their start date.
In this article we have provided an overview of the Ad-hoc database queries plugin which can be used to pull a wide variety of information out of your Moodle installation. In the next article we will look at how you create a query using the plugin.
You can view the previous posts in this series here:
- Part 1: The Gold Mine
- Part 2: Data Types
- Part 3: Understanding Your Data
- Part 4: Developing Questions
- Part 5: Combining Datasets
- Part 6: Limitations of Data
- Part 7: Extracting Data From Moodle
- Part 8: Moodle Grader Report
- Part 9: Moodle Grade History
- Version Control – Git GUI – 7th March 2023
- Version Control – Git Command Line Tool – 7th February 2023
- Version Control – Creating Repositories – 7th January 2023
Great explanation of a VERY powerful tool Jeff !