Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim 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.
Latest release:
3559 sites
133 fans

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 Paul Bennett
    Wed, Sep 9, 2020, 7:38 PM
    Hello Tim and Michael,

    We have been working to debug the issue on our moodle site, during which we have observed the following:
    * The timestamp is being turned into a filename in a function called report_customsql_temp_cvs_name in locallib.php, which is called from both the view.php and download.php files.
    * view.php is the report view, where you have the option to download the various types of reports.
    * download.php is the PHP script that is supposed to return the file or display the error message.

    The timestamp is the exact same when the function is called from view.php and download.php, but the filename that is created based on the timestamp is 5 hours different in the view.php instance than the download.php instance, even though it is the same function.

    We suspect the download function fails because of the difference in the timestamp.

    Any ideas? Your feedback is most welcome.

  • Tim at Lone Pine Koala Sanctuary
    Wed, Sep 9, 2020, 7:41 PM
    Well down for working that out.

    Sounds like a time-zone bug in the code. Please re-open the github issue I closed (or create a new one) with the details.

    I am busy, so I don't know when I will be able to work on fixing this myself. Pull requests welcome.
  • Picture of Patrick Gay
    Wed, Oct 28, 2020, 9:35 AM
    I'm using Moodle 3.9.2+ Build 20201028 and Ad-Hoc Database queries 3.9 for Moodle 3.5+ 2020062800. I have everything setup and can generate reports on demand. I can not, however, get reports to send email. Where can I go debug why it isn't happening? I've done all the regular debugging stuff, php error_log has nothing in it, I've turned PHP logging all the way up and still get nothing. What is the "thing" that sends the email? The cron job? If so, it never complains about anything. Any ideas where to go look? Thanks
  • Sarah Ashley
    Wed, Oct 28, 2020, 11:33 AM
    Hello Patrick,

    What worked for us was to uninstall the plugin and re-install it.

    There's a table in the Moodle database called 'mdl_message_providers', and for a plugin to be able to send out a message it needs to have an entry in that table. The site that was not sending scheduled reports had no entry for the report_customsql plugin. We backed up our custom reports, uninstalled the plugin, reinstalled and restored the backed up reports.
    When we checked the database that message provider got added to the correct table in the database during the installation process and our daily scheduled reports started getting emailed right on time!

    Hope this helps!

    In the spirit of sharing,
  • Tim at Lone Pine Koala Sanctuary
    Wed, Oct 28, 2020, 6:03 PM
    Can other parts of Moodle send email? (E.g. forum subscription emails.) That is nother thing to text.
  • Picture of Patrick Gay
    Thu, Oct 29, 2020, 9:28 AM
    @Sarah Ashley - I checked the DB and that value was indeed in the message_providers table. But just to be sure all was good, I uninstalled the plugin and reinstalled it, restored my sql and set it to run automatically, daily at 20:00. At 20:02, still nothing. @Tim Hunt yes. I tested this out and I received an email. I checked the task log and it is indeed running according to the log, but nothing is being sent. Anyone have any ideas or where I can go to troubleshoot?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Oct 29, 2020, 5:51 PM
    This is getting very puzzling. You could check under Site administration > Messaging > Notification settings, and Your profile -> Preferences -> Notification preferences. However, it would be surprising if that was wrong, particularly if you have just reinstalled.

    And, might be worth setting Debugging to Developer level, to see if that makes a difference.

    But, this is just standard Moodle messaging API. It should work. It does normally.
  • Picture of Rachel Grzymkowski
    Wed, Jan 20, 2021, 12:47 AM
    Hello Is there anyway to increase the Limit of rows returned? We've reached the max and it's truncating the report.
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jan 20, 2021, 12:57 AM
    The limit is an admin setting that you can change under Plugins -> Reports -> Ad-hoc DB queries.
  • Picture of Mark Aine
    Mon, Feb 22, 2021, 10:10 PM
    Hello. What sql query can i use to select teachers with their corresponding courses and number of enrolled users in that course
  • Picture of Stephanie Worden
    Sat, Mar 6, 2021, 2:43 AM
    Do you have a list of the queries available with this plugin? (If it's available on here somewhere and I've missed it, my apologies.) I am looking for a plugin that will allow me to see all grades for a particular student for all courses that student is enrolled in. Is this plugin able to do that?
  • Picture of Jon Bolton
    Sat, Mar 6, 2021, 4:01 AM
    @Stephanie - you’ll find a list if you click on the link in the Useful Links section above that says “More documentation on this plugin“.
  • Picture of hamza khatana
    Thu, Mar 11, 2021, 4:31 PM
    Hello @Tim Hunt, can you please tell me, is that possible to grant view/edit permissions of the ad-hoc reports to other admins because all I am seeing that the root user can use and view reports generated by this plugin. So if there is any possible way then please let me know. Thanks in advance
  • Tim at Lone Pine Koala Sanctuary
    Thu, Mar 11, 2021, 10:39 PM
    Access to reports is controlled by the capabilies that this plugin defines. (By default, only admins get permission to access thing, but you can change that by editing the roles. Filter the list using "report/customsql:"
  • Picture of hamza khatana
    Tue, Mar 16, 2021, 9:08 PM
    @Tim Hunt thank you for your reply you have been a great help to me. one more thing that I want to add a header image to pdf downloaded the report and I can't find the pdf library in the plugin files, so please guide me through how should I add it. thanks in advance
Please login to post comments