Last active
November 27, 2023 03:47
-
-
Save ragingcomputer/8a0c3a043489d7df5f0821b1faf7142a to your computer and use it in GitHub Desktop.
SQL query for Moodle to show space usage in Course Backup Area by course
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT mc.fullname AS CourseName, | |
ROUND((SUM(mf.filesize)/1024)/1024, 2) AS Storage_Used_MB | |
FROM mdl_files AS mf | |
LEFT JOIN mdl_context AS ctx ON mf.contextid = ctx.id | |
LEFT JOIN mdl_course AS mc ON ctx.instanceid = mc.id | |
WHERE mf.component = 'backup' | |
AND mf.filearea = 'course' | |
AND NOT mf.filename = '.' | |
GROUP BY mf.contextid | |
ORDER BY Storage_Used_MB DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
SELECT
c.id AS course_id,
c.fullname AS course_name,
SUM(f.filesize) AS total_backup_space
FROM
mdl_course c
JOIN
mdl_files f ON c.id = f.contextid
JOIN
mdl_backup_courses bc ON c.id = bc.courseid
WHERE
f.component = 'backup'
GROUP BY
c.id, c.fullname
ORDER BY
total_backup_space DESC;
calculates the total backup space used by each course by summing up the file sizes from the mdl_files table where the component is 'backup.' It provides a list of courses along with their corresponding total backup space, ordered in descending order.
Additionally, always back up your database before running any queries.