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

Comments RSS

Comments

  • Tim at Lone Pine Koala Sanctuary
    Wed, Aug 19, 2015, 4:14 PM
    Best to ask for help in the forum https://moodle.org/mod/forum/view.php?id=7979
  • Mon, Sep 21, 2015, 9:58 PM
    Actually a csv file cannot be sent to an email address directly. A link to the file can be sent to a Moodle user and indirectly forwarded to that users email address, but the user still needs to log into Moodle to retrieve the csv file or create one from the data included in the email.

    We are using this tool to collect daily attendance to be imported to our SIS. Ideally I would like to have the csv file automatically dropped to ftp. The few extra steps we take daily to download the report and place it in a file is mildly annoying. BUT that being said is still 1000 times better than collecting and posting attendance by hand. Not complaining, I am very grateful for this tool. Just a suggestion regarding the ftp drop.
  • Tim at Lone Pine Koala Sanctuary
    Mon, Sep 21, 2015, 10:01 PM
    Well, if someone can code that option without making either the user interface, or the back-end code, too much more complicated, then I would certainly accept the change (and it should certainly be possible to do). However, doubt I will have the time or inclination to code this myself.
  • time!
    Wed, Nov 11, 2015, 2:48 PM
    Automating Attendance from all courses

    Hi,
    I'm wondering if anyone has written AN SQL query for generating an attendance report for all students for all courses?

    Love the power of this!
  • Thu, Nov 19, 2015, 8:44 PM
    I am having a hopefully simple issue. Moodle is installed under centos..
    When I schedule a run of a sql query it never runs. The command crontab -l shows nothing scheduled and I see no cron.php in the report/customsql directory

    Is there something I need to do to have a query run at a specific time?

    Thanks
    Tim
  • Thu, Nov 19, 2015, 10:12 PM
    Regarding the issue with the customsql not automatically running, as a side note I did have to add the whet call for everything else (forums, badges) to the crontab manually.
    So maybe someone knows what to add for the sql database query?

    Tim
  • Tim at Lone Pine Koala Sanctuary
    Thu, Nov 19, 2015, 10:34 PM
  • Fri, Nov 20, 2015, 1:11 AM
    After I changed the cron to use cli it seems to at least look at it, but the job was scheduled for 12:00 and at 12:00 I see this in the logs
    Starting admin reports
    Processing cron function for report_customsql...
    ... started 12:00:02. Current memory use 59.9MB.
    ... Looking for old temp CSV files to delete.
    done. (2 dbqueries, 0 seconds)

    I know the query is good since the exact same one can be run on-demand

    Any ideas?
    Thanks
  • Tim at Lone Pine Koala Sanctuary
    Fri, Nov 20, 2015, 1:16 AM
    Sorry, don't know why it is not working for you. We use this feature all the time at the Open University, and it does work. So, you just have to work out how to get it set up on your server, and I am afraid I don't think I can be much help there. Sorry.
  • Fri, Nov 20, 2015, 1:26 AM
    Is there a command that might fire it off manually? I could cron that
  • Fri, Nov 20, 2015, 1:27 AM
    also thanks for trying
  • Sat, Nov 21, 2015, 2:55 AM
    I sorted out my issues with reports not running.
    1) single entry for cron in crontab, not in cron.hourly
    2) it will only run once a day, so if the custom query ran at 8:00 today and you reschedule it for 10:00 it will not run again today (unless you set lastrun to 0 in mdl_report_customsql_queries)

    Stumbled across this..
  • Thu, Nov 26, 2015, 3:11 AM
    Hi Tim and Mahmoud,

    I just added a few pull requests.

    Any idea when a Moodle 3.0 compatible version might become available?

    Best regards,

    Michael Milette
  • Tue, Dec 1, 2015, 2:47 AM
    Hi Tim and Mahmoud,

    Just added one more pull request. This one puts the list of reports in a table to make it easier to read the list of reports, especially when combined with the expandable/collapsible list feature I added last week.

    Let me know if you have any questions or concerns.

    Best regards,

    Michael
  • Wed, Dec 9, 2015, 10:44 PM
    Hello,

    I am looking at the available report options but don't see one that counts currently enrolled users within a time period e.g. How many users were enrolled between 1 month or some other time period (1 week, 30 days). I am just looking for the number of enrolled users across the whole site that are enrolled between dates and not course specific. Could someone point me to which one would carry that out please? Or perhaps help with the query that I would need to use or change?

    Thanks

    Al
Please login to post comments