Reports: Ad-hoc database queries

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.
2345 sites
98 fans
Current versions available: 9

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 Tifano T
    Fri, May 24, 2019, 7:37 AM
    Thanks Tim.
    As the export and import is scheduled and pushed automatically in Ad Hoc the later sounds a viable solution.
    SMS vendor wouldn't change for just us.
    I shall also see if configurable report can be cofigured to svhedule download as it produces CSV format without double quote.

    Thanks for you response and time.
  • Picture of Mahesh Kumar
    Fri, May 24, 2019, 3:19 PM

    Is it possible that an option to the limit of rows to be displayed can be added in the plugin? Or any other recommendation to view more than 5000 rows rather than changing it from the /reports/customsql/locallib.php

  • Picture of pierre REMY
    Fri, Jun 7, 2019, 5:29 PM
    shall you confirm this plugin offer the feature to send reports by night ? By example, every monring, i need to get the list of last created account (new subscribers).
    i read at the documentation and i can't see any information about email sending, setitng a date timer to send....
    ps : why so few screenshots of this plugin ?
    thank you
  • Picture of Ivan Marquez
    Mon, Jul 1, 2019, 4:23 PM
    Hello Creator,

    I was wondering if its possible to modify the schedule settings of the plugin to run every 5 minutes and save the csv somewhere in the server?
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jul 1, 2019, 6:00 PM
    Well, you could do that by writing code to change how the scheduling works.
  • Picture of Rachel Grzymkowski
    Tue, Jul 30, 2019, 8:25 PM
    Hello. LOVE AD-HOC plugin. Is there anyway to clear the old "Archived versions of this query"? We run it daily and we're not sure how to see the most recent because it looks like the archive is full and stopped archiving? Thanks
  • Tim at Lone Pine Koala Sanctuary
    Tue, Jul 30, 2019, 8:44 PM
    Yes, the are currently kept for ever. We ought to do something about that, and was created some time ago to track it.

    Even with a daily report, I don't think the archive could possibly fill up, so I don't konw why it has stopped working for you. Note that it only archives if the report includes some data.
  • Ray at his desk
    Sat, Aug 3, 2019, 12:20 AM
    Hi team,

    Our system is no longer sending out email notifications for our reports we have set to run daily. I think I tracked it down to an error about `message_send() must be an instance of core\message\message, instance of stdClass given`, which reminded me of a commit I saw on the GitHub repo. However, the code available on this page does not include that commit.

    I posted a longer explanation as an issue on your GitHub repo. I would deeply appreciate it if anyone could take a look and provide any insight. Thank you!

    - Ray
  • Picture of Manabu Abe
    Tue, Aug 27, 2019, 10:43 AM

    I have the same problem with Email sending function.
    Even though CSV files were saved, sending email has not worked since upgrading 3.7.

    Moodle 3.7+ (Build: 20190628) / Plugin Ver: 2018112200

    I got the following message for the Failed tasks in the Cron Task logs page.

    !!! Coding error detected, it must be fixed by a programmer: A lock was created but not released at:
    [dirroot]/lib/cronlib.php on line 99
    Code should look like:
    $factory = \core\lock\lock_config::get_lock_factory('type');
    $lock = $factory->get_lock(Resource id #565);
    $lock->release(); // Locks must ALWAYS be released like this.

    Is it related to the issue?
  • Picture of Luis Antonio Galindo Castro
    Wed, Sep 25, 2019, 8:37 AM
    Is there a way to install on 3.7?
    How can I contribute to do it?
  • Tim at Lone Pine Koala Sanctuary
    Thu, Sep 26, 2019, 1:27 AM
    Thanks for the reminder. The report was basically alreayd compatible with 3.7, but I have just uploaded the latest version here.
  • Picture of Zon Tec Solutions
    Fri, Sep 27, 2019, 6:56 PM
    Thanks for this valuable information with us.
  • Picture of Kristin Kilburn
    Mon, Oct 7, 2019, 10:10 PM
    Good morning. We are having an issue with duplicate records in the output of an ad hoc db query. When the ad hoc db query runs, the resulting file has duplicates - not all duplicates but duplicates.
    When I pull the query logic out of the ad hoc db query and run it in mySQL Workbench, I do not get duplicates.
    I even tried to pull the logic directly out of the mdl_report_customsql_queries table and running that in mySQL Workbench and I still can't recreate the duplicates.
    I've tried doing a Notepad++ file compare between the logic in the table and the logic on the front end - nothing.
    I re-read the fine print in the plugin on the front end and I saw mention of what it does when a column ends in the word "date". I did have a column that ended with "date" and I changed it to avoid any chance of issue there. Still duplicates.
    We've upgraded the plugin to the newest available version.

    Any thoughts on why this might be happening?
    Thank you in advance.
  • Tim at Lone Pine Koala Sanctuary
    Mon, Oct 7, 2019, 11:52 PM
    The report really just executes teh given SQL using Moodle's database API:

    Can you try executing the query directly in MySQL. I wonder if mySQL Workbench is hiding duplicate rows for some reason.
  • Picture of Kristin Kilburn
    Tue, Oct 8, 2019, 4:35 AM
    Tim thank you for the prompt response! : )
    I neglected to mention one other detail - we have the exact same report built using the configurable reports plugin and that report does not produce the duplicates either.

    Does that change your recommendation at all?
    Thank you again!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Please login to post comments