One of my colleagues wants to see the popularity of file downloads on our Nextcloud installation. I’ve looked at the Monitoring section which shows things in aggregate, but it doesn’t show the most popular file downloads. Is there away to see this? He would like to find out which files are being looked at more than others. I know he can look at each file individually, but it would be great to see it in a table instead of clicking on each file.
At first glance in the database it looks like oc_activity.object_id references oc_filecache.fileid. In order to check file downloads, you’ll also need to filter oc_activity.subject LIKE “%file%downloaded%” or possibly other values there. Based on that information, you could query oc_activity, filter by subject, and then group by object_id (group by file) showing a count of records by object_id (aka by file).
Here’s a rough query to quickly view the stats for public shared files:
COUNT(act.activity_id) AS number_of_downloads
FROM oc_activity act
JOIN oc_filecache files ON act.object_id = files.fileid
WHERE act.subject = "%file%downloaded%"
AND act.object_type = "files"
GROUP BY act.object_id
ORDER BY number_of_downloads DESC
Question; do you know if this is possible in something like Matomo or Google Analytics to write this query into a dashboard? (I’ve never used either but saw the option in the admin section.) If not I’ll ask my system admin about getting a direct connection. I’d want my end user to be able to access this without SQL coding knowledge.
I don’t think so… In order to connect an external monitoring tool, you’d need to expose that data via an API endpoint and you probably should consider any security concerns (only allowing access from an authorized monitoring host). I think you would have to write an add-on app to gain this functionality.
Thanks for your help linucksrox. Fair point about enabling the the API and the security concerns related to that.
We ran the query and unfortunately there’s no files_downloaded type.
We queried a list of all the type values showing in that table and they are:
But nothing that matches the “downloaded” part… is Nextcloud not counting the downloads unless you turn it on? Or do you (or anyone else) know how to enable that value type?
Found it. it’s under oc_activity table; the field is "subject’: it’s called “public_shared_file_downloaded” - there’s also “shared_file_downloaded” but we’re looking for public.
Thanks kindly for your help linucksrox (you’re giving me dyslexia)
I’m glad you found it. I think it might be necessary to have the “Activities for shared file downloads” app enabled for some of those activities to appear.
Instead of the API idea, you could also write an app that displays that info in the web interface for a logged in user (or an administrator). That might be easier than setting up an API, I’m not sure how complicated it would be.
Or schedule cron to run your query and email the results periodically.