Our district has just recently placed all Google youtube user accounts into restricted mode. This in turn has caused many of our youtube videos to be inaccessible to students. We have hundreds of embedded videos in our courses. They are in activities, resources, pages, forums, etc. Now we are scrambling to find each video that has been blocked.
I am trying to create a report that would identify instances of embedded or linked youtube videos. As of yet I’ve had no luck. I can get a simple list of external links,but these are only on the front page of each course. As I stated earlier, most of ours are embedded in activities, resources etc.
I would like the report to contain the course and module where the video is embedded, and display the url as a link.
In this attempt, I’ve tried modifying a query found in contributed reports,but I get an error saying
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
FROM prefix_resource AS r
JOIN prefix_course AS c ON r.course = c.id
WHERE r LIKE 'iframe src="https://www.youtube.com/embed%'
“Unknown column 'r' in 'where clause'”
Any help creating the correct query would be most appreciated