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:

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:

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:

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