Ad-hoc database queries

Reports ::: report_customsql
Maintained by Tim at Lone Pine Koala SanctuaryTim Hunt, at the OU (Perry building)Mahmoud Kassaei, Anupama Sarjoshi
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:
3836 sites
148 fans
Current versions available: 10

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
Anupama Sarjoshi: Tester & Developer
Please login to view contributors details and/or to contact them

Comments RSS


  • Panthers fan
    Wed, Jun 21, 2017, 11:46 PM
    I'm writing a report that has a username column that I would like to make into a hyperlink to loginas the particular user. Question - any way to get the required sesskey using SQL commands?
    Thanks again,
  • Tim at Lone Pine Koala Sanctuary
    Wed, Jun 21, 2017, 11:54 PM
    I think the reason sesskey exists is precisely to stop that sort of dangerous direct linking.

    However, it would be possible to add another of the %%...%% tokens. E.g. we could have %%SESSKEY%% that gets replaced by the current user's sesskey in the output code. E.g. about here: add a line

    $value = str_replace('%%SESSKEY%%', sesskey(), $value);

    However, I am a bit reluctant to add that to the official version of the plugin.
  • Panthers fan
    Thu, Jun 22, 2017, 12:04 AM
    Re: Adding %%SESSKEY%%

    No worries. I can change in my version. But your answer got me to thinking - Is there a better, safer way to accomplish what I want? Basically, we have a situation where an administrator has to loginas hundreds of users to change some data. The link in the report is just an attempt to save them a few mouse clicks for each user. If there is another way via the plugin to do this, I'm all ears.

  • Panthers fan
    Thu, Jun 22, 2017, 12:06 AM
    Re: Adding "Expand all"

    I have written over a hundred reports that I have put into several categories. Question - how would someone add an "Expand all" to the top of the form (like other forms)?

  • Tim at Lone Pine Koala Sanctuary
    Thu, Jun 22, 2017, 12:23 AM
    Expand all would be a JavaScript change. Probably not too hard to do.

    Since all the data behind Moodle is in a database, it is quite easy to change things if you can acess the database directly. It is also possible to screw up the data directly, if you make a mistake. So, be careful.
  • Sat, Jul 1, 2017, 8:16 PM
    I am using Moodle 3.3 (Build: 20170515).

    I am getting this error. Is it because my Moodle version is not supported?

    Validating report_customsql ... Error
    [Error] Write access check [/opt/bitnami/apps/moodle/htdocs/report]
    Installation aborted due to validation failure
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jul 3, 2017, 5:32 PM
    Read the error message: "Write access check [/opt/bitnami/apps/moodle/htdocs/report]"

    It looks like the file permissions are not right on your server.
  • Mon, Jul 3, 2017, 6:50 PM
    Validating report_customsql ... Error
    [Error] Write access check [/opt/bitnami/apps/moodle/htdocs/report]
    Installation aborted due to validation failure

    For the error above, if you execute the commands below, the installation will go through.
    I wondering why need to run the command below though.

    sudo chown -R bitnami:daemon /opt/bitnami/apps/moodle/htdocs/report
    sudo chmod -R g+w /opt/bitnami/apps/moodle/htdocs/report
  • Tue, Jul 25, 2017, 3:36 AM
    Guys, would appreciate some help on this... whilst trying to install...

    Plugin "report_customsql" is installed in incorrect location "$CFG->dirroot/blocks/customsql", expected location is "$CFG->dirroot/report/customsql"

    More information about this error

    ×Debug info:
    Error code: detectedmisplacedplugin
    ×Stack trace:
    line 835 of /lib/upgradelib.php: plugin_misplaced_exception thrown
    line 446 of /lib/upgradelib.php: call to upgrade_plugins_blocks()
    line 1754 of /lib/upgradelib.php: call to upgrade_plugins()
    line 699 of /admin/index.php: call to upgrade_noncore()
  • Tue, Jul 25, 2017, 1:02 PM
    So the clue is in the message... you've installed the plugin in the blocks folder but it should be in the reports folder smile
  • Thu, Jul 27, 2017, 2:41 AM
    @Jon Bolton - thanks, exactly correct. Appreciate the help.
  • Tue, Sep 12, 2017, 10:03 PM
    Hi smile are there any plans to certify this for use with moodle 3.2/3.3? I have a managed site and the provider won't install any plugins that aren't listed as supported. Many thanks.
  • Tue, Oct 10, 2017, 12:28 PM
    I can't get scheduled reports to email at all.

    Email from my server works. I've set up two users: 1 a site admin, and 1 with `report/customsql:view`, and set these usernames as the recipients of a weekly report. I can see that the report is running: It tells me the latest time it ran and shows me the previous outputs that it generated, yet I get no emails. In the standard log for the report_customsql component there are entries for viewed and edited but none for notification.

    How do I test that a scheduled weekly or monthly report runs? - I'd like a "run now" sort of option so I don't have to wait a week to find out that my query was actually buggy.

    What is the first day of the week? Is that a Sunday or a Monday? The accepted answer is different depending on where you live. Here people respond "Monday".

    I'm confused by the `%%STARTTIME%%` and `%%ENDTIME%%` settings. There's no explanation of how this relates to the date it runs in the interface and even the code seems a little confusing to me. Is the start time the beginning of the *previous* week and the end the end of the day before the report runs again? That's what I expect, so I could report on things that happened in the last week up until right now. I can't test these in a normal on-demand report since they don't get replaced.

    Minor niggle: If I set up a report as "on demand" then run it, then change its type to "daily", I see "this report was run on (date)" in the text next to it. If I then open the report, the screen then says "This query has not yet been run.".

  • Tim at Lone Pine Koala Sanctuary
    Tue, Oct 10, 2017, 6:32 PM
    To answer most of those questions with full certainty, I would have to check what the code is doing. And, I know that you are very capable of understanding the code yourself, so would you be able to start trying that? Most of the back-end stuff is in locallib.php, and the method names are quite clear. Of course, if there is anything you can't work out yourself, please ask again. Thanks.
  • Thu, Jan 4, 2018, 11:09 PM
    Dear all,

    thanks for this very useful plugin, we use it to communicate weekly reports of participants on security trainings in our plants.
    As I can see, the plugin creates automatically an archive of the queries. My question: Is there a chance to delete the archived Versions without deleting the whole query?
    Or is the archive a result of the SQL query?
    Any help would be greatly appreciated.

    CR Andreas
Please login to post comments