fl0049921

Admin reports to find anomalies - Part 1 - Lessons

Blog Post created by fl0049921 on May 29, 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 = 11 -- 11 is our module ID for lessons, you have to use your own ID

 

Let start with Lessons

Re-take a lesson

If a lesson contains at least one quiz page, it is important to think about the 'Re-takes allowed' setting under the Grade section. If enabled, students can attempt the lesson more than once, otherwise they get an error message:

You are not allowed to retake this lesson.

The following report helps you to find these lessons with quiz pages but disabled re-take setting:

 

SELECT DISTINCT
    c.id AS cid,
    c.shortname,
    cm.id AS cmid,
    l.id AS lid,
    l.name,
    l.retake
FROM
    mdl_lesson_pages AS lp
        JOIN
    mdl_lesson AS l ON lp.lessonid = l.id
        JOIN
    mdl_course AS c ON l.course = c.id
        JOIN
    mdl_course_modules AS cm ON cm.course = c.id AND cm.instance = l.id
        AND cm.module = 11 -- don't forget to change it, if your modules table is different
WHERE
        c.visible = 1      -- only visible courses
        AND cm.visible = 1 -- only visible activities
        AND l.retake = 0   -- retakes not allowed
        AND lp.qtype <> 20 -- branch / content, not quiz
        AND lp.qtype <> 21 -- end of branch, not quiz
        AND lp.qtype <> 30 -- cluster, not quiz
        AND lp.qtype <> 31 -- end of cluster, not quiz

 

The other qtype codes are:

page / quiz typeqtype code
LESSON_PAGE_SHORTANSWER1
LESSON_PAGE_TRUEFALSE2
LESSON_PAGE_MULTICHOICE3
LESSON_PAGE_MATCHING5
LESSON_PAGE_NUMERICAL8

 

Find graded lessons

The next script helps you to find graded lessons, in our VLE we don't want them to be graded.

 

SELECT 
    cm.id AS cmid,
    c.id AS cid,
    l.name AS lname,
    l.id AS lid
FROM
    mdl_lesson AS l
        JOIN
    mdl_course AS c ON l.course = c.id
        JOIN
    mdl_course_modules AS cm ON cm.course = c.id AND cm.instance = l.id
        AND cm.module = 11 -- don't forget to change this number based on your modules table
WHERE
    l.grade > 0

 

I hope you will find these reports helpful.

 

Enjoy!

 

Frank

Outcomes