Is there a way to get a table of most popular file downloads?

Hello,

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.

Cheers

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:

SELECT
files.fileid,
files.name,
files.size,
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

LIMIT 50
1 Like

Thanks!

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:

announcementcenter
calendar
circles_as_member
comments
favorite
file_changed
file_created
file_deleted
file_restored
personal_settings
public_links
shared
spreed
calendar_todo
deck

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?

Cheers,
Kevin

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.

1 Like