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

Document created by mwilday on Aug 23, 2017
Version 1Show Document
  • View in full screen mode

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

Attachments

    Outcomes