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:
3793 sites
1k downloads
146 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.

Screenshots

Screenshot #0

Contributors

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

Comments

  • 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
  • Sat, Jan 6, 2018, 11:40 PM
    Hi. Thanks for this plugin, it is very useful. One question: Can I create my reports with paging and sorting? Thanks in advance
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jan 8, 2018, 8:37 PM
    Paging and sorting are not currently an option. All the data is shown on one page. This is simple and seems to work.

    For sorting (or any other more complex processing) any report can be downloaded as CSV, and then manipulated in a spreadsheet program like Excel or Libre Office.
  • Tim at Lone Pine Koala Sanctuary
    Mon, Jan 8, 2018, 9:14 PM
    @Andreas, there is no automatic way to delete the archive.

    You can do this manually if you have access to the server. The archived reports are stored in [moodledata]/admin_report_customsql, in folders named for the report id (which you can get from the URL) and the filename clearly includes the date and the time. You can just delete those files from the server.

    Of course, it might be nice for someone to code a new feature for this report to automatically delete old reports after a certain amount of time.
  • Wed, Jan 10, 2018, 3:42 PM
    @Tim: Thanks for the explanation, very helpful, problem solved ...
  • Panthers fan
    Fri, Jan 19, 2018, 2:35 AM
    I'm looking into why a scheduled report is not being emailed. It is failing a write to the database. After enabling $CFG->showcronsql and $CFG->showcrondebugging, it looks like it fails saving to the mdl_message table before emailing. Question - is there any size limitation to saving to the table? The reason I ask is if I cut down on the number of columns or the number of rows returned from the query, the report is successfully emailed. If I add back the information that I need in the report (it returns almost 3K rows now), it fails. For what its worth, it does create the CSV file in /moodledata/admin_report_customsql. Any ideas?
    Thanks,
    Richard
  • Tim at Lone Pine Koala Sanctuary
    Fri, Jan 19, 2018, 3:07 AM
    Sorry, I can't think why that would be happening. You will probably need to debug it. Message size is a good guess. What size is that column in the DB?
  • Panthers fan
    Fri, Jan 19, 2018, 3:42 AM
    If I'm reading the information_schema correctly (still learning my way around SQL), the field mdl_message.fullmessagehtml is a longtext and character_maximum_length is 4294967295 (4G). So, its probably (!) not the message size. By my meager calculations, the message is at most 350K.

    For debugging, other than enabling $CFG->showcronsql and $CFG->showcrondebugging, what else would help? My complicating factor is we only have email enabled on our production system. So I can't enable debugging for too long.

    Thanks,

    Richard
  • Panthers fan
    Fri, Jan 19, 2018, 6:12 AM
    Some more information - after doing some more debugging, I noticed the following in the SQL debug output:

    eventdata as \stdClass is deprecated. Please use core\message\message instead.
    line 67 of /lib/messagelib.php: call to debugging()
    line 641 of /report/customsql/locallib.php: call to message_send()
    line 582 of /report/customsql/locallib.php: call to report_customsql_send_email_notification()
    line 131 of /report/customsql/locallib.php: call to report_customsql_email_report()
    line 78 of /report/customsql/classes/task/run_reports.php: call to report_customsql_generate_csv()
    line 104 of /lib/cronlib.php: call to report_customsql\task\run_reports->execute()
    line 292 of /lib/cronlib.php: call to cron_run_inner_scheduled_task()
    line 91 of /admin/tool/task/schedule_task.php: call to cron_run_single_task()

    What caught my eye is the report_customsql_send_email_notification call.

    Richard
  • Panthers fan
    Sat, Jan 20, 2018, 6:18 AM
    Fixed it. After a LOT of debugging (and reading lots of documentation), I updated my.cnf with group_concat_max_len=8M and max_allowed_packet=16M. So far, so good - I'm finally getting emails with the large report in my Inbox.
    Keeping my fingers crossed. Thanks for your help...
    Richard
  • Wed, Jan 31, 2018, 5:31 AM
    Hi Tim and Mahmoud,

    Will there be a version of Ad-hoc Database Queries compatible with Moodle 3.2 and beyond? If so, when do you think we can expect to see an updated 3.4 compatible version?

    Michael
  • Tim at Lone Pine Koala Sanctuary
    Sat, Mar 17, 2018, 5:14 AM
    I have finally done a 3.2+ compatible version.
  • Wed, Apr 4, 2018, 7:05 AM
    Hi, Tim. I noticed that the last update stripped away the report name from the email report. They all show up as "Query" now. Was that intentional, or should I do a bug report?
  • Tim at Lone Pine Koala Sanctuary
    Wed, Apr 4, 2018, 5:36 PM
    I don't think that was an intentional change. A but report would be good. A patch would be even better, if you can do one smile
Please login to post comments