Your Moodle version

Reports: Ad-hoc database queries

Maintained by Tim at Lone Pine Koala Sanctuary Tim Hunt, at the OU (Perry building) Mahmoud Kassaei
This 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.

This report, created by The Open University, lets Administrators set up arbitrary SQL select queries that anyone with the appropriate permissions can then run. Reports can be set to be runnable on-demand, or automatically run weekly or monthly.

The results are displayed as a fairly plain HTML table, and can also be downloaded as CSV.

The idea is that this lets you quicly set up ad-hoc reports, without having to create a whole new admin report plugin.


Screenshot #0


Tim at Lone Pine Koala Sanctuary
Tim Hunt (Lead maintainer)
at the OU (Perry building)
Mahmoud Kassaei: Developer
Please login to view contributors details and/or to contact them

Comments RSS

Show comments
  • Picture of Ronald Balhan
    Wed, Jan 20, 2016, 9:24 PM
    Ok, I understand, thank you.
    As I don't want the Non-Editing Teacher role on system context, I created a new role (based on no archetype) on system context with only the report/customsql:view permission and assigned that role to the user(s) who are permitted to run the ad-hoc database queries in any course where I added the HTML block and all seems to works fine now.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jan 20, 2016, 9:25 PM
    Yes. That is the right way to do it. smile
  • Picture of Andrew C
    Tue, Mar 1, 2016, 6:28 PM
    Hi Tim, I've been running Ad-hoc database queries for 3 years now and all fine until today. I'm currently on version 2014061800. Last night, the cron log stated my regular queries ran per usual but when I look at the report in the plugin, I obtain the message "This query has not yet been run.". There have been no changes to the system/code and archives date back to 2013. No errors in any logs. Any ideas why they disappeared and do you have any info on how the archives are retrieved via the plugin? I'm aiming to restore from a backup. I can see they still exist in the data folder. Thanks, Andrew (Moodle 2.6.11)
  • Tim at Lone Pine Koala Sanctuary
    Tue, Mar 1, 2016, 6:35 PM
    The data from reports that have been run are stored in the moodledata folder, in moodledata/admin_report_customsql. There should be a subfolder full of CSV files with the id of each report that runs on a schedule. Has someone deleted those folders?
  • Tim at Lone Pine Koala Sanctuary
    Tue, Mar 1, 2016, 6:46 PM
    Since of the archive will not be a problem (unless the disc gets full!) No idea why it is not working. If you have PHP skills, it would be good if you could try to debug this.
  • Picture of Andrew C
    Tue, Mar 1, 2016, 7:11 PM
    Hi Tim, the reports are now running fine and the problem was not connected to the plugin. I think something temporarily happened to the fileserver containing the reports which caused the error. Cheers for your help and for the excellent plugin.
  • Picture of Mike Finch
    Wed, Mar 9, 2016, 8:12 AM
    Moodle version: 3.0.2
    Ad-hoc database queries plugin version: 3.0

    I used this plugin to create the following query, which lists courses that are completed.

    select u.username, c.shortname, rec.timecompleted, rec.timecompleted as cdate
    from {course_completions} rec
    inner join {user} u
    on rec.userid =
    inner join {course} c
    on rec.course =
    where rec.timecompleted is not NULL

    A comment in the query editing page says:
    | If a column name in the results ends with the characters date, and the column contains integer values,
    | then they will be treated as Unix time-stamps, and automatically converted to human-readable dates.

    The timecompleted field of the course_completions table in the database definitely contains integer values. And in my query, I tell it to have a column in the result whose name ends with 'date'.

    However, when I run the query, my 'cdate' column is not getting human-readable date strings. I just get blanks.

    Do you have any suggestions on what the problem is?
    Thank you.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 3:49 PM
    Check directly in the database. Does this column actually contain integers, or blank (null)?

    Also, turn on Debugging to verify there is no error message.
  • Picture of Mike Finch
    Wed, Mar 9, 2016, 10:41 PM
    Re: Human-readable date strings not displaying

    Thanks for the quick response.
    The values for course_completions.timecompleted in my query result are indeed non-NULL integers. The WHERE clause I use in my query ensures that. I can execute the same query directly on the database (removing curly braces from and adding the 'mdl_' prefix back to the table names, of course), and I get integer values for that column.

    I do have the debug setting set to DEVELOPER. When I run the query from Moodle, I do not observe any error output on the page.

    Looks like the report_customsql_get_element_type() function is the starting point for my investigation. I will see where it leads.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Mar 9, 2016, 10:51 PM
    Yes. Sorry, I think you will need to debug this one yourself.
  • Picture of Mike Finch
    Thu, Mar 10, 2016, 12:29 AM
    Re: Human-readable date strings not displaying

    I have discovered that when report_customsql_generate_csv() determines that a value should be formatted as a date string, it calls userdate(), providing it with a format string of '%F %T'. With that format string, the return is always a empty string.

    In contrast, if userdate() is called with no format string, then the return is a valid string such as “Tuesday, 8 March 2016, 1:17 PM”. Therefore, the format string of ‘%F %T’ is suspect.

    Perhaps a better alternative would be to use one of the format strings in “moodle\lang\[language code]\langconfig.php” ?
  • Picture of Mike Finch
    Fri, Mar 11, 2016, 12:17 AM
    Re: Suggested fix for null context

    In each of the 3 event classes that this plugin has, the get_url() function wants to use $this->context. However, sometimes that context is null, and I get notice messages like the following on my page.

    Notice: Trying to get property of non-object in C:\moodle\report\customsql\classes\event\query_edited.php on line 51

    The fix is to use $this->get_context(), which will create a context if it does not exist yet.
  • Tim at Lone Pine Koala Sanctuary
    Fri, Mar 11, 2016, 1:04 AM
    Moodle's userdate is a wrapper around strftime, and says both %F and %T are fine. I don't know why it does not work for you. Anyway, that is the date format we want for this report.

    Thanks for finding the event problem. I just pushed what I think is the right fix to and eventfix branch in git: However, I don't have time to test this. Can you test?
  • Picture of Mike Finch
    Fri, Mar 11, 2016, 3:23 AM
    Re: Suggested fix for null context

    I tested the changes in commit 5d35b97. I did not experience any error or notice messages on my pages. I created, edited, and deleted a query via the "Ad-hoc database queries" report page, and did not experience any problems.
  • Picture of Laura Robinson
    Tue, Mar 29, 2016, 10:46 PM
    I'm running Moodle 3.0.2 and am experiencing issues with the automatic email element of the plugin. I have tested with two different users, but as yet neither user has received a scheduled report email. I can see from the plugin interface that the daily query is running successfully and I can view the output within Moodle, but what's happened to the email is a mystery. Any tips on how to track down where the problem might lie (i.e. within Moodle/the plugin vs my mailserver?)
1 2 3 4 5 6 7 8 9
Please login to post comments