This forum post has been removed
Number of replies: 15Re: Report identifying all embedded youtube videos. Nothing is working
You have two FROM clauses... I think that's your problem.
I had to solve this problem for my last institution related to a different web service that became unavailable. I'm attaching two xml files with the reports that I wrote. You should be able to import these into Configurable Reports. If you have trouble, let me know and I'll stand up a server with Configurable Reports and extract the sql-- I don't currently have these reports saved in that form.
Re: Report identifying all embedded youtube videos. Nothing is working
The previous report looks for references within Page resources. This one looks for URL resources that contain references matching a string.
This forum post has been removed
Re: Report identifying all embedded youtube videos. Nothing is working
Hi Adele,
If the YouTube items are embedded using iframes the content will be with in the label not with in the external URL so I think you might be looking in the wrong table?
I'd suggest the MDL_label table to be the one that you are looking for for the embed codes?
Vernon
This forum post has been removed
Re: Report identifying all embedded youtube videos. Nothing is working
Yes, individually is the way to go. This works for me. Simplistic but it works.
SELECT
mdl_course.category,
mdl_course.fullname,
mdl_course.id,
mdl_course.shortname,
mdl_assign.intro
FROM
mdl_assign
INNER JOIN mdl_course ON mdl_assign.course = mdl_course.id
WHERE
mdl_assign.intro LIKE '%youtube%'
returns
| category | fullname | id | shortname | intro | |||||||||||
| 1 | TiiTruthTester | 997 | TiiTruthTester | <iframe width="560" height="315" src="https://www.youtube.com/embed/gS80HadDF6s" frameborder="0" allowfullscreen=""></iframe> | |||||||||||
This forum post has been removed
Re: Report identifying all embedded youtube videos. Nothing is working
Hi Adele,
This isn't going to do what you want, either-- you need to be searching the Assignment, etc. tables, not the URL tables. You're going to need several queries, I'm afraid, because you will probably find it easier to set up a different query for each table.
If you are going to try to change this query, though, remember that you have to trim out the colon : and surround %youtube% with quotes like this
WHERE externalurl like '%youtube%'
Also, if you just search on the string 'youtube' this way, you're going to get all references to youtube, not just the ones on a private channel....
Re: Report identifying all embedded youtube videos. Nothing is working
Something like this should work:
activity.id AS 'activity ID'
, concat('<a href=\"','%%WWWROOT%%', '/course/view.php', CHAR(63), 'id=', CAST(activity.course as CHAR), '\">', CAST(course.fullname AS CHAR), '</a>') AS Course
, activity.name AS name
, activity.intro AS DescriptionFROM prefix_assign AS activityINNER JOIN prefix_course AS course
ON activity.course=course.id
WHERE description like '%youtube%' or description like '%youtu.be%'
ORDER BY course
Just replace "assign" with the name of any table you want to check, e.g. prefix_file, prefix_quiz, etc.
Also, remember there are short versions of youtube links. So you will also need to search on '%youtu.be%' as noted in this example.
Re: Report identifying all embedded youtube videos. Nothing is working
As fun as writing these queries looks, wouldn't this plugin do it for you?
Edit: Just noticed you later mentioned quizzes and assessments which may be an issue I would think but could be worked around given it's permission based.
Re: Report identifying all embedded youtube videos. Nothing is working
There are two problems with using this tool in this situation:
1 - The tool requires external access, and might present a security risk
2 - the tool only looks at "page" entries, not assignments or quizzes.
Re: Report identifying all embedded youtube videos. Nothing is working
- Given there are no 3rd parties involved I don't see why it would be a security risk any more than a normal user is.
- Not sure that's correct, I believe when it mentions scraping "pages" it's talking about any web page e.g. from the plugin docs on github "... it is possible to restrict crawler from accessing other moodle contexts such as courses, activities and blocks."
Re: Report identifying all embedded youtube videos. Nothing is working
Looking more closely at the github entry, this plugin might work to detect broken links across the site (itself a valuable feature), but I don't know if it will detect Youtube private videos as "inaccessible." Do private videos at YouTube generate a 404 error?
I also don't know how fast or slow a link crawler like this would be compared to a report against the database, or how usable the output would be to correct the courses with the bad links.
It could be worth trying, though.
This forum post has been removed
Re: Report identifying all embedded youtube videos. Nothing is working
I'm glad that helped-- it seems like you're on the right track! You'll need to look at each of the activity types you want to test to find out where (or if) the course ID is stored. In the case of book chapters, you may need to link them through books first. Also, if you need to get links from individual quiz questions, not just from a quiz introduction/description, you'll need to link in the question table.
The "resources" table in Moodle actually refers to uploaded files. There is a separate "page" table that you will need to run the query on. You may want to check the Moodle schemae to see what the different tables are that might be holding the links you're looking for: https://docs.moodle.org/dev/Database_Schema