report_customsql
Maintained by Tim Hunt, 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.
2080 sites
70 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.

Contributors

Mahmoud Kassaei: Developer

Awards

• Fri, 19 Jan 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?
• Fri, 19 Jan 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
• Fri, 19 Jan 2018, 6:12 AM
Some more information - after doing some more debugging, I noticed the following in the SQL debug output:

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()

What caught my eye is the report_customsql_send_email_notification call.

Richard
• Sat, 20 Jan 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, 31 Jan 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
• Sat, 17 Mar 2018, 5:14 AM
I have finally done a 3.2+ compatible version.
• Wed, 4 Apr 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?
• Wed, 4 Apr 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
• Thu, 5 Apr 2018, 5:22 AM
Unfortunately, I don't have the skills to help you out with that, Tim.

I've submitted a bug report. I hope I did it right. I think I've only ever done one before.
https://tracker.moodle.org/browse/CONTRIB-7238
• Thu, 19 Apr 2018, 1:49 AM

I've been looking for information about the "Export csv report to path / directory" functionnality but couldn't find any.
What kind of path is expected? A local path from the server root? Where can I export my file?
The contextual help (in Portuguese) didn't seem to be filled. Maybe I can help with that once I understand the feature.
Thanks
• Thu, 19 Apr 2018, 11:48 PM
It is a path on the local server. Here is how the setting is used in the code https://github.com/moodleou/moodle-report_customsql/blob/master/locallib.php#L670. (That could be a file path that is a mount of a network disc, or similar.)
• Sat, 21 Apr 2018, 3:00 AM
Thank you very much for the help
• Wed, 20 Jun 2018, 2:57 AM
I have started to work with this plugin, which I found to be incredibly useful (thank you to the developers).

However, I have had to make some small modifications on my version to get it working the way I need and I want to share my feedback.

In the locallib.php file, I found that the subject line for the emails that get sent is not currently pulling the query name (display name) which it is coded to do. I modified these lines in mine by changing "report_customsql_plain_text_report_name($report->displayname)" in the code to "report_customsql_plain_text_report_name($report)" since the "report_customsql_plain_text_report_name()" function looks at the \$report object as a whole and then returns the query name.