Reports: Ad-hoc database queries

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.
3134 sites
1k downloads
124 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.

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

Show comments
  • MDinnovar
    Wed, Jun 3, 2020, 2:59 AM
    Excelent!!!
  • Picture of Paweł Suwiński
    Fri, Jul 31, 2020, 11:54 PM
    Hi, I'm new to moodle 3.9 internals after moving from 1.9 version. I made there a quick hack by copying and twicking download.php to have a feature of public download of report csv file restricted by IP address (done .httaccess) using a simple GET query. Now I need this feature to the new instance. I can just copy and addapt old hacked file or other simply way without ugly hacking is just using customdir export feture and there some short index.php script, like below one and .htaccess.

    ------------------------------- <?php
    // report id
    $id = (int)($_GET['id'] ?? 0);
    // lastone or date, default - currend day
    $lastone = isset($_GET['lastone']) ? true : false;
    $date = (int)($_GET['date'] ?? date('Ymd'));
    $pattern = sprintf('%s-%s*.csv', $id, $lastone ? '' : $date.'-');
    if(!$files = glob($pattern)) {
    header("HTTP/1.0 404 Not Found");
    exit($pattern.': files not found!'.PHP_EOL);
    }
    $file = end($files);
    header('Content-Disposition: attachment; filename="'.$file.'"');
    header('Content-Type: text/csv; charset=UTF-8');
    readfile($file);
    --------------------------------------------------

    But I am wondering is it a way to do it better using some new/ native stuff of 3.9 version, maybe webservices or just simple extention of plugin by adding new options in edit_form.php and adjusting download.php (?). After all if this feature is considered usefull I can do some contrib to the plugin. Any advice what would be the finest way to achive this?

    --
    regards
    Pawel Suwinski
  • Picture of Paul Bennett
    Fri, Sep 4, 2020, 3:10 AM
    I have tried to download the results from some of the contributed reports as a comma separated value (.csv) file, but get a general errror : unknown download file.
    The same error occurs when I select any other download format.

    Any guidance would be much appreciated.

    Cheers!

    Paul
  • Tim at Lone Pine Koala Sanctuary
    Mon, Sep 7, 2020, 7:03 PM
    You need to find what the error really is. Where are you seeing that error message? In the web browser, or somewhere else?

    Try turning https://docs.moodle.org/en/Debugging up to full.

    If you are getting some sort of file downloaded, try opening it in a plain-text editor, to see what is really in there.
  • Picture of Paul Bennett
    Mon, Sep 7, 2020, 11:09 PM
    Hi Tim,

    I activated Developer Mode and received the following details:

    Debug info:
    Error code: unknowndownloadfile
    ×Stack trace:
    line 482 of /lib/setuplib.php: moodle_exception thrown
    line 50 of /report/customsql/download.php: call to print_error()

    Does this provide sufficient clues, or do I need to find details elsewhere?

    Paul
  • Picture of Paul Bennett
    Mon, Sep 7, 2020, 11:16 PM
    Hi Tim,

    From /var/log/apache2/error.log:

    [Mon Sep 07 06:25:02.736876 2020] [mpm_prefork:notice] [pid 16451] AH00163: Apache/2.4.18 (Ubuntu) OpenSSL/1.0.2g configured -- resuming normal operations
    [Mon Sep 07 06:25:02.736915 2020] [core:notice] [pid 16451] AH00094: Command line: '/usr/sbin/apache2'
    [Mon Sep 07 11:04:50.717559 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
    [Mon Sep 07 11:04:50.717617 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
    [Mon Sep 07 11:04:50.717646 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
    [Mon Sep 07 11:04:50.717668 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47036] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
    [Mon Sep 07 11:05:21.130167 2020] [php7:warn] [pid 4673] [client 192.168.1.70:47102] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110508.csv): failed to open stream: No such file or directory in /var/www$
    [Mon Sep 07 11:05:21.130575 2020] [php7:notice] [pid 4673] [client 192.168.1.70:47102] Default exception handler: Unknown download file. Debug: \nError code: unknowndownloadfile\n* line 482 of /lib/setuplib.php: moodle_exception thrown$
    [Mon Sep 07 11:09:24.954536 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
    [Mon Sep 07 11:09:24.954615 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
    [Mon Sep 07 11:09:24.954645 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
    [Mon Sep 07 11:09:24.954666 2020] [php7:notice] [pid 4672] [client 192.168.1.70:47418] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
    [Mon Sep 07 11:09:31.032453 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontsans' or component 'mod_certificate'. Perhaps you are missing $string['fontsans'] = ''; in mod/ce$
    [Mon Sep 07 11:09:31.032507 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontsans_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontsans_desc'] = '';$
    [Mon Sep 07 11:09:31.032536 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontserif' or component 'mod_certificate'. Perhaps you are missing $string['fontserif'] = ''; in mod/$
    [Mon Sep 07 11:09:31.032557 2020] [php7:notice] [pid 4677] [client 192.168.1.70:47426] PHP Notice: Invalid get_string() identifier: 'fontserif_desc' or component 'mod_certificate'. Perhaps you are missing $string['fontserif_desc'] = '$
    [Mon Sep 07 11:09:56.152745 2020] [php7:warn] [pid 4674] [client 192.168.1.70:47480] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110951.csv): failed to open stream: No such file or directory in /var/www$
    [Mon Sep 07 11:09:56.153032 2020] [php7:notice] [pid 4674] [client 192.168.1.70:47480] Default exception handler: Unknown download file. Debug: \nError code: unknowndownloadfile\n* line 482 of /lib/setuplib.php: moodle_exception thrown$

    Paul
  • Tim at Lone Pine Koala Sanctuary
    Tue, Sep 8, 2020, 12:02 AM
    So, the error is coming from here: https://github.com/moodleou/moodle-report_customsql/blob/v3.9/download.php#L49. So, it is failing to find the CSV data for the report on disc. How is your Moodle site set up? What sort of storage is used for the moodledata folder?
  • Picture of Paul Bennett
    Tue, Sep 8, 2020, 12:17 AM
    Hi Tim,
    The moodle site is setup on a Ubuntu 16.04 LTS server. There is ample free space on the server with over 100GB provided of which we are using only about 19GB at the moment.
    The Moodledata folder is in the path /var/moodledata. The permissions are set at 777 for the parent and subfolders.
    When I examined the path referenced in the line [Mon Sep 07 11:09:56.152745 2020] [php7:warn] [pid 4674] [client 192.168.1.70:47480] PHP Warning: fopen(/var/moodledata/admin_report_customsql/temp/114/20200907-110951.csv): failed to open stream: No such file or directory in /var/www$, I was able to confirm that the path /var/moodledata/admin_report_customsql/temp and the file 20200807-110951.csv do exist. However, I am not sure where /var/www$ is coming from....

    Thank you for your assistance on this issue!

    Paul
  • Tim at Lone Pine Koala Sanctuary
    Tue, Sep 8, 2020, 12:19 AM
    The $ may just be Apache (unhelpfully) truncating the lines in the error log to a fixed lenght.
  • Picture of Paul Bennett
    Tue, Sep 8, 2020, 1:23 AM
    Hi Tim,
    Do you require any additional logs or server details?

    Paul
  • Tim at Lone Pine Koala Sanctuary
    Tue, Sep 8, 2020, 7:53 PM
    I don't need that. You need to work out why files are disappearing from your server's moodledata folder.
  • Picture of Michael Milette
    Wed, Sep 9, 2020, 5:05 AM
    Hi Paul, the download links for the reports work fine for me, even on Ubuntu 16.04. I am pretty sure it is a server configuration issue (most likely PHP), not a plugin issue.

    Best regards,

    Michael
  • Picture of Michael Milette
    Wed, Sep 9, 2020, 5:08 AM
    Could also be Apache mime types.
  • 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.

    Paul
  • 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Please login to post comments