Your Moodle version

Reports: Ad-hoc database queries

report_customsql
Maintained by Tim at Lone Pine Koala Sanctuary Tim 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.
51k
1k
12

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
  • Picture of Mykl Belfatto
    Fri, Jun 12, 2015, 1:19 AM
    Hi Tim, How's it going? I trying to run this query ti get the outstanding activities for each student. it runs in phpmyadmin, but it won't run in Moodle. In Moodle, the first query (nested select) runs fine, it's when I add the NOT IN select that causes the problem. Any idea? Or another way to do this? Cheers, Mykl


    SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item), SortOrder
    FROM (

    SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gi.sortorder AS SortOrder
    FROM {user} AS u
    JOIN {groups_members} AS gm ON gm.userid = u.id
    JOIN {groups} AS g ON g.id = gm.groupid
    JOIN {course} AS c ON c.id = g.courseid
    JOIN {grade_items} AS gi ON gi.courseid = g.courseid

    WHERE u.Institution != '' AND NOT u.deleted
    AND itemmodule = 'assign' AND CONCAT(u.id, ', ', gi.id) NOT IN (
    SELECT CONCAT(u.id, ', ', gg.itemid) 'Item'
    FROM {user} AS u
    JOIN {grade_grades} AS gg ON gg.userid = u.id )

    ORDER BY CONCAT(Company, Student, Course), SortOrder ) AS tmp

    GROUP BY CONCAT(Company, Student, Course)
  • Tim at Lone Pine Koala Sanctuary
    Fri, Jun 12, 2015, 1:23 AM
    Try turning on https://docs.moodle.org/29/en/Debugging (set it to DEVELOPER level temporarily) then try again. You should get a useful error message.

    AS tmp in the ORDER BY looks wrong.
  • Picture of Mykl Belfatto
    Fri, Jun 12, 2015, 8:37 AM
    I didn't know the debug would work smile The AS tmp is not part of the ORDER BY, it's the naming for the nested SELECT
  • Picture of Mykl Belfatto
    Fri, Jun 12, 2015, 11:53 AM
    The error I get is Error when executing the query: Error reading from database Incorrect key file for table '/var/mysqltmp/#sql_1f3_0.MYI'; try to repair it...I think I'm blowing the tmp space sad I think I'll need to increase the size.
  • Picture of Mykl Belfatto
    Fri, Jun 12, 2015, 12:05 PM
    I got it!!! It's not perfect (yet), but this gives the outstanding activities (not graded) for each student.

    SELECT Company, StudentID, Student, Course, GROUP_CONCAT(Item)
    FROM (
    SELECT Institution AS 'Company', u.id AS StudentID, CONCAT(u.lastname, ', ', u.firstname) AS 'Student', c.fullname AS Course, gi.itemname AS 'Item', gg.itemid AS ItemsCompleted
    FROM prefix_user AS u
    JOIN prefix_groups_members AS gm ON gm.userid = u.id
    JOIN prefix_groups AS g ON g.id = gm.groupid
    JOIN prefix_course AS c ON c.id = g.courseid
    JOIN prefix_grade_items AS gi ON gi.courseid = g.courseid
    LEFT JOIN prefix_grade_grades As gg ON gi.id = gg.itemid
    WHERE u.institution != '' AND NOT u.deleted AND gi.itemmodule = 'assign'
    AND gg.itemid IS NULL

    ORDER BY CONCAT(Company, Student, Course), gi.sortorder) AS tmp
    GROUP BY CONCAT(Company, Student, Course)
  • Picture of Daemon Hunt
    Fri, Jun 19, 2015, 6:10 AM
    I am panicking. My Moodle for 2015 has been using the new standard log store with legacy logging turned off, and I have no idea how to tweak this SQL code to make it work!

    SELECT COUNT(l.id) hits, concat('',c.fullname,'') AS Course

    ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
    FROM prefix_role_assignments AS ra
    JOIN prefix_context AS ctx ON ra.contextid = ctx.id
    JOIN prefix_user AS u ON u.id = ra.userid
    WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

    ,CASE
    WHEN c.fullname LIKE '%-13%' THEN '2013'
    WHEN c.fullname LIKE '%-14%' THEN '2014'
    WHEN c.fullname LIKE '%-15%' THEN '2015'
    END AS YEAR

    ,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules

    ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
    JOIN prefix_context AS ctx ON ra.contextid = ctx.id
    WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students

    FROM prefix_log l
    INNER JOIN prefix_course c ON l.course = c.id
    GROUP BY c.id
    HAVING Modules > 2
    ORDER BY YEAR DESC, hits DESC
  • Picture of Craig Simmons
    Fri, Jun 19, 2015, 6:54 PM
    This is a great plugin. Thanks.

    However, I am having problems entering a date as parameter.

    This works:

    SELECT
    subject, firstname, lastname, email, FROM_UNIXTIME(created)
    FROM
    {forum_posts}
    INNER JOIN
    {user} ON {forum_posts}.userid = {user}.id
    WHERE
    parent > 0
    AND FROM_UNIXTIME(created) BETWEEN '2015/04/27' AND '2015/05/03'

    Whereas this doesn't (although it correctly prompts for start and finish dates):

    SELECT
    subject, firstname, lastname, email, FROM_UNIXTIME(created)
    FROM
    {forum_posts}
    INNER JOIN
    {user} ON {forum_posts}.userid = {user}.id
    WHERE
    parent > 0
    AND FROM_UNIXTIME(created) BETWEEN :start_date AND :end_date

    Any thoughts why?

    Thank you for your time.

    Craig
  • Tim at Lone Pine Koala Sanctuary
    Fri, Jun 19, 2015, 8:16 PM
    Try changing FROM_UNIXTIME(created) -> created in the WHERE.
  • Picture of Kathleen Bodine
    Thu, Aug 6, 2015, 12:49 AM
    Really liking this tool! We have a hosted site and are using this tool to automate attendance and other reports to help with retention. Currently the email report requires that a user log in to Moodle and have viewing permissions. Suggestion to allow .csv file to be sent directly to ftp site or email address.
  • Tim at Lone Pine Koala Sanctuary
    Thu, Aug 6, 2015, 5:00 PM
    A scheduled query can be sent to an email address. Look at the bottom of the form when creating a query.
  • Picture of Marcus Green
    Mon, Aug 10, 2015, 10:33 PM
    Tim or Mahmoud, it would be useful if you were to update the links to the documents to a later version
    as it is pointing to 2.2 at the moment with lots of references to Moodle 1.9 which makes it look rather
    out of date. If you just take out the version bit (the 22) it should automatically redirect to the latest version
    i.e. https://docs.moodle.org/en/Custom_SQL_queries_report
  • Tim at Lone Pine Koala Sanctuary
    Mon, Aug 10, 2015, 10:36 PM
    Thanks Marcus. Link updated.
  • wen photo
    Wed, Aug 19, 2015, 3:21 PM
    As Kathleen pointed out, it would be super awesome if we could add direct SFTP (with scheduling) capability to this plugin. Thanks for the great work!
  • Picture of charles okaformbah
    Wed, Aug 19, 2015, 4:11 PM
    Hi Tim. Need help on schedule, the query works fine but I don't seem to understand why it does not do query scheduled to my email. Don't know if I am doing something wrong or is there a cronjob I need to setup. Here is an imageprint of my setting http://prntscr.com/86cj20
  • 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
1 2 3 4 5 6
Please login to post comments