Data and E-Learning Part 12: Ad-hoc database queries (Students)

The previous post in this series showed how to set up and run a query using the Ad-hoc database queries plugin for Moodle. Over the next few articles we will examine different queries for extracting data regarding specific areas of your Moodle site.

Areas to report on

There are lots of options for generating reports via the Ad-hoc database queries plugin. Your first decision when designing a report is to understand what you are looking to report on. Some of the items you can base your report on include:

• Students
• Courses
• Enrolments
• Assignments
• Quizzes.

In this article we will look at what you could report on for students and provide you with some examples. We will show some sample SQL queries and discuss the results they produce and how you could use this data.

The queries shown in this article may not work with your own Moodle site. Use them instead as ideas for how you could use the plugin to improve the reporting you are generating. If you like a query, try to figure out how to make it work for your own Moodle site.

Count of Students

It can be useful to know how many students are in your Moodle site. This can help you to manage enrolments, track enrolments over time, or look deeper at the demographics of your users. You can apply some filtering to the results using a WHERE clause to be more specific on which group of students you are counting:

• All users that are not suspended (as shown above)
• Suspended students
• Students from a specific city or country
• Students in a specific course.

Running this report and recording the results would allow you to track the number of users over time. Eventually you could analyse this data to see what parts of the year have the most active students or to identify what causes a spike (or drop) in the number of students.

User contact details

A report with the contact details of users can be useful when you find yourself needing to contact a specific student. Adding some basic filtering to the query can allow you to target specific groups within your Moodle site that you need to contact:

• By city or country
• By enrolment date (e.g. to contact all users that enrolled that month)
• A specific user based on their idnumber or email address.

Having a list of contact details for students on a course or in a geographic area can be useful if you want to promote a course to them or inform them of a change to course delivery. Make sure to adhere to any privacy requirements and laws around unsolicited messages.

User last access

Tracking when a user last accessed your site can be a good way to check engagement. It also allows you to identify users that have not logged in for a while and may be at risk of falling behind or needing some further support. You could modify this query to:

• Return users that have not logged in since a specific date
• Filter by a specific course, city, country etc.
• Order the last access column to see the most recent users logging in.

You could use this query to audit students if there is a requirement to log in regularly or if you want to follow up any students that have not logged in for a specific number of days.

Usernames

There are lots of administrative tasks that can be made easier or sped up with the right database query. This query could be used if a user needed to know their username. You would be able to find it based on their name or email address. You could find a specific username by using a WHERE clause on any of:

• First name
• Last name
• Email.

Searching for a username may not be overly useful. What this query demonstrates is that you can return any piece of data that is held by the system and that by filtering on other columns find the information you are after. Instead of username it could be an email address you look for, or phone number, date of birth etc.

Using the SQL queries

The queries provided in this article will not necessarily work in your Moodle site. They are provided to prompt your thinking of what you could extract regarding your users. If you are adventurous, use the SQL statements as a starting point and see if you can get them to work in your site. This is the best way to learn! Use the provided examples to stimulate your thinking about what else you might want to learn about your users.

In this article we have shown you some examples of queries you could use based on the users table.

You can view the previous articles 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
• Part 10: Ad-hoc database queries plugin
• Part 11: Ad-hoc database queries

• About
• Latest Posts

Jeff Mitchell

P&C Reporting and Data Analyst at Fisher & Paykel Appliances

Jeff is passionate about the role of learning and development, and has a specific interest in how people and organisations can be developed in order to achieve their potential. Jeff has a keen interest in information technology and specifically data analysis and the e-learning space.

Latest posts by Jeff Mitchell (see all)

• Version Control – Git GUI – 7th March 2023
• Version Control – Git Command Line Tool – 7th February 2023
• Version Control – Creating Repositories – 7th January 2023