Admin Dashboard SQL Queries to find links embedded in various activities in Moodle

Version 1

    Though there isn't a single query that I have come across that can pull all of these queries in one swoop, the following queries will check commonly used activities in Moodle and help you find embedded links to particular resources or activities that you might need to change. Though Moodlerooms offers a find and replace tool for replacing strings in the database, it is incredibly dangerous and has been known to crash many databases and require a database restore which could risk student data. Our team used the following queries and combined them in a Google spreadsheet to track the edits and provide quick access to the activity.

     

    Link directly to the admin dashboard by modifying the following link: http://  YOUR URL /blocks/reports/view.php?courseid=1&action=dashboard

     

    Every Moodlerooms instance has a set of modules integrated into the system. Each Moodle setup has a different module list. Before you adapt these queries for your needs run the following query to determine which module id your activities.

    SELECT * from mdl_module
    

     

    This will give you the module numbers to input related to your specific instance of moodle.

    Query for searching in Moodle Assignment Activities

    Select Concat("http://course.uc.apu.edu/course/modedit.php?update=", cmod.id) AS Link, assign.name AS ItemName, "Assignment" as Itemtype, c.shortname AS Coursename, assign.intro AS DATA
    From mdl_assign assign
    Join mdl_course c on c.id = assign.course
    Join mdl_course_categories cat on cat.id = c.category
    Join mdl_course_modules cmod on cmod.instance = assign.id
    WHERE assign.intro LIKE '%course.uc.apu.edu/mod/turnitintool/%' -- insert text/link you are for between percent signs. 
    AND cat.path Like '/502%' -- Insert a category ID
    AND cmod.module = 97 -- You need to insert your module ID for Moodle Assignments
    AND cmod.course = c.id
    ORDER BY c.id
    

     

    Query for searching in Moodle Advanced Forum Activity Descriptions/Introductions

    Select Concat("http://course.uc.apu.edu/course/modedit.php?update=", cmod.id) AS Link, hsu.name AS ItemName, "Advanced Discussion" AS Itemtype, c.shortname, hsu.intro AS DATA
    from mdl_hsuforum hsu
    Join mdl_course c on c.id = hsu.course
    Join mdl_course_categories cat on cat.id = c.category
    Join mdl_course_modules cmod on cmod.instance = hsu.id
    WHERE hsu.intro LIKE'%some link or text%'
    AND cat.path Like '%/477%'
    AND cmod.module = 58 -- You need to insert your Module ID for Moodle Advanced Forums
    AND cmod.course = c.id
    
    

    Query for searching in Moodle Resource Pages

    SELECT Concat("http://course.uc.apu.edu/course/modedit.php?update=", cmod.id) AS Link, p.name AS ItemName, "Page Resource" as Itemtype, c.shortname, p.content AS DATA
    FROM mdl_page p
    Join mdl_course c on c.id = p.course
    Join mdl_course_categories cat on cat.id = c.category
    Join mdl_course_modules cmod on cmod.instance = p.id
    WHERE p.content LIKE '%some link/%' -- insert text/link you are looking for between percent signs
    AND cat.path Like '/502/%' -- Insert the new term's category ID
    AND cmod.module = 89 -- You need to enter your module id number for a page resource
    AND cmod.course = c.id
    ORDER BY c.id
    

     

    Query for searching in Moodle Section Headers

    SELECT concat("http://course.uc.apu.edu/course/view.php?id=", c.id, "&ampsection=", cs.section) as Link, cs.name, "Section" as ItemName, c.shortname, cs.summary
    FROM mdl_course_sections cs
    INNER JOIN mdl_course c on c.id = cs.course
    INNER JOIN mdl_course_categories cat on cat.id = c.category
    WHERE cs.Summary Like '%course.uc.apu.edu/mod/turnitintool/%' -- insert text/link you are for between percent signs
    AND cat.path Like '/502/%'
    ORDER BY c.id
    

    Query for searching in Moodle Labels

    SELECT Concat("http://course.uc.apu.edu/course/modedit.php?update=", cmod.id) AS Link, l.name AS ItemName, "Label" as Itemtype, c.shortname, l.intro AS DATA
    FROM mdl_label l
    Join mdl_course c on c.id = l.course
    Join mdl_course_categories cat on cat.id = c.category
    Join mdl_course_modules cmod on cmod.instance = l.id
    WHERE l.intro LIKE '%course.uc.apu.edu/mod/turnitintool/%' -- insert text or link you are for between percent signs
    AND cat.path Like '/502/%'
    AND cmod.module = 10 -- You need to enter your module id number for a label
    AND cmod.course = c.id
    ORDER BY c.id
    

    Query for searching in Moodle TurnitinTwo activities

    Select Concat("http://course.uc.apu.edu/course/modedit.php?update=", cmod.id) AS Link, tii.name AS ItemName, "Turnitin2" as Itemtype, c.shortname, tii.intro AS DATA
    From mdl_turnitintooltwo tii
    Join mdl_course c on c.id = tii.course
    Join mdl_course_categories cat on cat.id = c.category
    Join mdl_course_modules cmod on cmod.instance = tii.id
    WHERE tii.intro LIKE '%course.uc.apu.edu/mod/turnitintool/%' -- insert text/link you are for between % signs.
    AND cat.path Like '/502/%' -- Insert the new term's category ID
    AND cmod.module = 107 -- Idetnfies the module id number for a Turnitin 2 resource
    AND cmod.course = c.id
    ORDER BY c.id
    

     

    This document was generated from the following discussion: Admin Dashboard SQL Queries to find links embedded in various activities in Moodle