fl0049921

Admin reports to find anomalies - Part 2 - Assignments

Blog Post created by fl0049921 on May 30, 2018

The following SQL scripts might be helpful to find different anomalies on your Moodle site.

 

Modules and their ID number in your database

Before you adopt one of the following codes, you have to check your module IDs in your database as the codes are based on our modules table and it can be different in your database.

 

Start with the following query:

 

select * from mdl_modules

 

and save it somewhere.

 

Our most popular modules are:

 

IdName
11lesson
12page
13quiz
15scorm
17url
21journal
28book
37hsuforum
44lti
55assign
79webexactivity
84hvp

 

Your database might be different, so you have to use your ID numbers in the following queries. The code looks similar to this one:

 

... AND cm.module = 55 -- 55 is our module ID for assignments, you have to use your own ID

 

Let's find some weird settings in Assignments

Missing 'Grade to pass'

Our assignments use the Attempts reopened: Automatically until pass setting under the Submissions settings. It is a great way to keep the students previous attempts accessible on the VLE, so we can see their progress during their studies. We can also control the number of their attempts (Maximum attempts), normally if it is a formative assessment, the students are allowed to submit unlimited time, but if it is a summative one, they have limited attempts.

Everything works fine apart from one little thing. If you leave the Grade to pass value as 0 under the Grade settings, then everyone will pass the assignment, regardless to the actual grading scale or point, so the assignment never reopens automatically, no one can resubmit anything.

The following report helps you to find mismatching settings:

 

SELECT 
    gi.courseid, gi.itemname, gi.gradepass, gi.scaleid
FROM
    mdl_grade_items AS gi
        JOIN
    mdl_course AS c ON c.id = gi.courseid
        JOIN
    mdl_assign AS ass ON gi.iteminstance = ass.id
        AND gi.courseid = ass.course
        JOIN
    mdl_course_modules AS cm ON cm.instance = ass.id 
        AND cm.module = 55 -- don't forget to use your own assign module ID here
        JOIN
    mdl_course_sections AS cs ON cs.id = cm.section
WHERE
    gi.gradepass = 0
        AND gi.itemmodule = 'assign'
        AND gi.hidden = 0 -- visible grade item
        AND c.visible = 1 -- visible course
        AND ass.attemptreopenmethod = 'untilpass'
        AND cm.visible = 1 -- visible activity
        AND cs.visible = 1 -- visible course section

 

If the grade type is Point (e.g. between 0 and 100), the Grade to pass can be a point (e.g. 40), but when the grade type is Scale you have to be more careful.

If the grade type is Scale: every scale item has a number, e.g.

FAIL=1, PASS=2, MERIT=3, DISTINCTION=4 

therefore the Grade to pass should be 2 (=PASS) in this case.

 

Assignments not sending notifications to graders

It is really important that the grader receives notifications about submitted files need to be graded.

Let's find assignments without notifying the grader.

 

SELECT 
    cm.id AS moduleid, c.id AS courseid, c.shortname, ass.name
FROM
    mdl_assign AS ass
        JOIN
    mdl_assign_plugin_config AS apc ON apc.assignment = ass.id
        JOIN
    mdl_course_modules AS cm ON cm.instance = ass.id 
        AND cm.module = 55 -- don't forget to use your own assign module ID here
        JOIN
    mdl_course AS c ON c.id = cm.course
WHERE
        ass.sendnotifications = 0 -- no notification
        AND cm.visible = 1  -- visible activity
        AND c.visible = 1  -- visible course
        AND apc.plugin = 'file'
        AND apc.subtype = 'assignsubmission'
        AND apc.name = 'enabled'
        AND apc.value = 1 -- file submission enabled
        AND ass.submissiondrafts = 1 -- student has to press Submit button
        AND ass.grade <> 0 -- graded assignment
ORDER BY c.id

 

File submissions need to be graded but grader cannot upload feedback file

 

SELECT 
    ass.course, ass.name
FROM
    mdl_assign_plugin_config AS apc
        JOIN
    mdl_assign AS ass ON ass.id = apc.assignment
WHERE
        ass.grade <> 0  -- needs to be graded
        AND apc.plugin = 'file'
        AND apc.subtype = 'assignfeedback'
        AND apc.name = 'enabled'
        AND apc.value = 0 -- no feedback file
        AND apc.assignment IN (SELECT 
            ass.id
        FROM
            mdl_assign_plugin_config AS apc
                JOIN
            mdl_assign AS ass ON ass.id = apc.assignment
                JOIN
            mdl_course AS c ON c.id = ass.course
                JOIN
            mdl_course_modules AS cm ON cm.course = c.id
                AND cm.instance = ass.id
                AND cm.module = 55 -- don't forget to use your own assign module ID here
        WHERE
            apc.plugin = 'file'
                AND apc.subtype = 'assignsubmission'
                AND apc.name = 'enabled'
                AND apc.value = 1 -- file submission enabled
                AND c.visible = 1 -- visible course
                AND cm.visible = 1) -- visible activity
ORDER BY ass.course DESC

 

Enjoy!

 

Frank

Outcomes