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:
3498 sites
1k downloads
135 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

  • Picture of Mark Aine
    Mon, Feb 22, 2021, 10:10 PM
    Hello. What sql query can i use to select teachers with their corresponding courses and number of enrolled users in that course
  • Picture of Stephanie Worden
    Sat, Mar 6, 2021, 2:43 AM
    Do you have a list of the queries available with this plugin? (If it's available on here somewhere and I've missed it, my apologies.) I am looking for a plugin that will allow me to see all grades for a particular student for all courses that student is enrolled in. Is this plugin able to do that?
  • Picture of Jon Bolton
    Sat, Mar 6, 2021, 4:01 AM
    @Stephanie - you’ll find a list if you click on the link in the Useful Links section above that says “More documentation on this plugin“.
  • Picture of hamza khatana
    Thu, Mar 11, 2021, 4:31 PM
    Hello @Tim Hunt, can you please tell me, is that possible to grant view/edit permissions of the ad-hoc reports to other admins because all I am seeing that the root user can use and view reports generated by this plugin. So if there is any possible way then please let me know. Thanks in advance
  • Tim at Lone Pine Koala Sanctuary
    Thu, Mar 11, 2021, 10:39 PM
    Access to reports is controlled by the capabilies that this plugin defines. (By default, only admins get permission to access thing, but you can change that by editing the roles. Filter the list using "report/customsql:"
  • Picture of hamza khatana
    Tue, Mar 16, 2021, 9:08 PM
    @Tim Hunt thank you for your reply you have been a great help to me. one more thing that I want to add a header image to pdf downloaded the report and I can't find the pdf library in the plugin files, so please guide me through how should I add it. thanks in advance
  • Peter
    Thu, Apr 22, 2021, 8:33 PM
    @Patrick Gay, @Tim Hunt,

    How was this problem resolved in the end? I am using Moodle 3.10.3+ with report_customsql 4.0 for Moodle 3.9+. Here the situation is the same as the one Patrick describes. I can make reports on demand, the plugin has an entry in the table mdl_message_providers and the notification settings and preferences are fine.
    Still, the reports are not created according to the loggings.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Apr 22, 2021, 11:23 PM
    Can you check in the mdl_report_customsql_queries table. Look at what is stored in the emailto column.

    Also, look under Admin -> Server -> Tasks -> Scheduled tasks, and look at the logs for this report's task. Are there any errors being reported there.
  • Peter
    Fri, Apr 23, 2021, 1:24 AM
    Hi Tim,

    Thanks for your reply.

    email to column: ',2'. 2 is the userid of this user

    in the logs there is 1 error:

    Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
    ... started 11:10:01. Current memory use 4.5MB.
    ... Looking for old temp CSV files to delete.
    ... Running report tessie
    ... REPORT FAILED Invalid user
    ... used 8 dbqueries
    ... used 0.063519954681396 seconds
    Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)

    Kind regards,
    Peter
  • Tim at Lone Pine Koala Sanctuary
    Fri, Apr 23, 2021, 1:27 AM
    Right, so there is a bug. There should not be a , before the 2. If you can fix that directly in the DB, then it will start working.

    Ultimately, I need to push a bug fix for this to the plugins DB.
  • Peter
    Fri, Apr 23, 2021, 1:36 AM
    Thanx Tim. When you push the bug fix, i'll fix the DB.
    Regards,
    Peter
  • Peter
    Fri, Apr 23, 2021, 3:00 AM
    Tim,

    I scheduled a report for 8 pm after I made the change in the database ('2 to 2). It looks like the report has not been picked up after all.

    Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)
    ... started 20:10:01. Current memory use 4.5MB.
    ... Looking for old temp CSV files to delete.
    ... used 3 dbqueries
    ... used 0.0085630416870117 seconds
    Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql \ task \ run_reports)

    Does the plugin by any chance check whether there has already been a run on this day?
  • Tim at Lone Pine Koala Sanctuary
    Fri, Apr 23, 2021, 3:12 AM
    Yes, I think we found this when we were trying to hurry-up some testing. When it computes when next to run the report, it computes the next day from when it last ran, and then puts in the time.
  • Picture of Shubham Sharma
    Thu, Apr 29, 2021, 2:11 AM
    Hello Tim,
    Thankyou very much for creating such a helpful plugin. I was using this first time in my moodle instance and wanted the query results to go to my email. I created a scheduled export and set up my email addresses but I did not get any email in my mail box ( I setup 2 emails ).. and also I setup the path for export in. "Export csv report to path / directory" but there is no file coming there for scheduled export. here is my cron job details:
    Execute scheduled task: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)
    ... started 12:10:02. Current memory use 19.9MB.
    ... Looking for old temp CSV files to delete.
    ... used 3 dbqueries
    ... used 0.0043940544128418 seconds
    Scheduled task complete: Ad-hoc database queries: run scheduled reports task (report_customsql\task\run_reports)


    I am not sure what I am doing wrong.. Can you please guide?
    Thanks
  • Tim at Lone Pine Koala Sanctuary
    Thu, Apr 29, 2021, 2:36 AM
    See comments above. There is a bug. Hopefully won't be too long before it is fixed.
Please login to post comments