Find private files of each user: alias/shortcuts to courses

Find private files of each user: alias/shortcuts to courses

by Anton Tremetzberger -
Number of replies: 5
Picture of Particularly helpful Moodlers

Dear Community,

we have some users who use "private files" in Moodle. Some of the files are placed in courses via alias/shortcut, some others as a copy. If users open their private files repository and click on a file they can see, in which courses the files are linked as alias/shortcut.

As Moodle Admin I wan't to know which user has private files and in which courses the linked files are placed:

  • list of all users (ID, Firstname, Lastname) who uses private files
  • list of private files (filename, filepath in private file folder, filesize)
  • list of courses (ID, shortname, lastname) where these files are linked as alias/shortcut

PS: we have a database query which finds all private files of a user

Select u.firstname, u.lastname, u.username,
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',u.id,'">',u.id,'</a>') as 'ID and link to user profile', 
DATE_FORMAT(FROM_UNIXTIME(u.lastlogin), '%e %b %Y') AS 'lastlogin', u.suspended AS 'active (0) or suspended (1) User', f.filename, f.filesize
from prefix_files AS f 
JOIN prefix_user AS u ON u.id = f.userid 
where filearea = "private"


Mayb it es easy to customise this query, but I don't know how to find out the courses and files which are placed via alias/shortcut. Any ideas?

thanks, br Anton

Average of ratings: -
In reply to Anton Tremetzberger

Re: Find private files of each user: alias/shortcuts to courses

by Randy Thornton -
Picture of Documentation writers
Anton,

The last column in the mdl_files table is referencefileid and will have a number value for links and NULL for actual files. This number is the id of the row in the table mdl_files_reference The source column will give you the path from which the file comes with category and course, etc.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Find private files of each user: alias/shortcuts to courses

by Anton Tremetzberger -
Picture of Particularly helpful Moodlers

Dear Randy,

thanks for your help. It concerns several tables: context, course_modules, user, course, files - so It was not easy to get the whole information, but following query will show me the correct information I want - maybe it's interesting for others:


select f.contextid, f.component, f.filearea, f.filename,
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',f.userid,'">',f.userid,'</a>') as 'ID and Link to Userprofile',
u.firstname, u.lastname,
f.filesize, f.mimetype, f.source, f.author, f.referencefileid,
c.instanceid as 'ID from prefix_context',
cm.id as 'ID from prefix_course_modules',
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',cm.course,'</a>') as kursid_and_link,
co.fullname,
co.shortname
from prefix_files as f
JOIN prefix_context AS c ON c.id = f.contextid
JOIN prefix_course_modules as cm ON cm.id = c.instanceid
JOIN prefix_user as u ON u.id = f.userid
JOIN prefix_course as co ON co.id = cm.course
where referencefileid != ""


thanks, br Anton

Average of ratings: -
In reply to Anton Tremetzberger

Re: Find private files of each user: alias/shortcuts to courses

by Randy Thornton -
Picture of Documentation writers
Anton,

Oh, this is very nice! The only small change I would make is to use WHERE referencfileid IS NOT NULL instead of checking for an empty value. I notice in the column that all those are null for normal files.

Would you consider sharing this over on the Ad-hoc contributed reports page? https://docs.moodle.org/310/en/ad-hoc_contributed_reports

Randy
Average of ratings: -
In reply to Randy Thornton

Re: Find private files of each user: alias/shortcuts to courses

by Anton Tremetzberger -
Picture of Particularly helpful Moodlers
Dear Randy,
thanks for the advice. I just have published it on the Ad-hoc contributed reports page. However, the website moderators still have to approve the entry. I hope it will be published soon.

br, Anton
Average of ratings: -
In reply to Anton Tremetzberger

Re: Find private files of each user: alias/shortcuts to courses

by Randy Thornton -
Picture of Documentation writers
Anton,

Thanks for sharing that. I think Mary is the modertor for the documentation. If it doesn't show up soon, I will remind her.

Randy
Average of ratings: -