Notify the share owner when another user downloads a shared file [$1,000]

[gig13]

Use Case:

1. User A shares a file with User B.
2. User B downloads the file via Web/desktop client/mobile.
3. User A is notified that User B has downloaded the file and via which method - Web, desktop client or mobile.

It would also be desired to have this tracked in an audit trail like the Activity or via a reporting mechanism.

@MTRichards

[drkmccy]

The otherthing is downloading via webdav (syncing with client or federated share) does currently not trigger any hook, so they are not detectable.

This is the deal breaker. Unless that hook is there, this is dead.

[oparoz]

Thanks @nickvergessen. 365 days sound good enough for a feature which wants to track which files were downloaded.

The otherthing is downloading via webdav (syncing with client or federated share) does currently not trigger any hook, so they are not detectable.

I saw the issue, but wasn't sure if that was because it wasn't implemented or if apps needed to register a different way. This should be relatively easy to fix since a short patch can give any app access to the information.

[oparoz]

This is the deal breaker. Unless that hook is there, this is dead.

@derekblankmccoy - It's a requirement for this work which is targeting 9.1, so the fix should be implemented in the coming weeks, but it's always possible to patch.

[oparoz]

@derekblankmccoy, @gig13 - I'm going to add an entry for each download of a shared file to the activity stream.

[oparoz]

@nickvergessen - Activity can listen to hooks triggered by DAV just fine. People with 3rd party apps were having issues registering for these events, but it turned out to be a documentation issue and does not require any core modifications.

[drkmccy]

That's great! So it is possible. We still need to know how it was downloaded (web, client or mobile) and a way of exporting the info so we can have timestamps etc. @gig13 is still active here, hopefully he will give some input

[drkmccy]

You are correct, I forgot about the client, as I don't use it. I only use web and mobile. I've just looked at the client and it shows a timestamp, the filename, the folder, the action and the size, but not who downloaded it. That would require changes to be made to the client (?)

An idea would be to add a filter in the web portal's activity feed that let's you select from last hour, day, week, month or year. And an equivalent of the desktop client's "copy" function. Maybe an "Export" button that dumps it in a csv? What do you think?

[oparoz]

, but not who downloaded it. That would require changes to be made to the client (?)

The log extract I posted above is from the standard client and shows "who" and "using what", so no change required afaik.

And an equivalent of the desktop client's "copy" function. Maybe an "Export" button that dumps it in a csv? What do you think?

You can retrieve all of it via any REST client and slice and dice it any way you want to, but maybe an ownCloud app could be written with the sole purpose of generating a daily CSV file.

Here is what it looks like for one item

{
    "activity_id": 1910,
    "type": "remote_download",
    "user": "NSA",
    "affecteduser": "admin",
    "app": "files_sharing",
    "subject": "shared_file_downloaded",
    "subjectparams": [{
        "value": {
            "56142": "\/Contracts\/Governments\/Most_wanted_cryptographer_3.jpg"
        },
        "type": "file"
    },
    {
        "value": "NSA",
        "type": "username"
    },
    {
        "value": "desktop",
        "type": ""
    }],
    "message": "",
    "messageparams": [],
    "link": "https:\/\/localhost:4443\/owncloud\/files\/index.php?dir=%2FContracts%2FGovernments",
    "object_type": "files",
    "object_id": 56142,
    "object_name": "\/Contracts\/Governments\/Most_wanted_cryptographer_3.jpg",
    "subject_prepared": "Shared file \u003Cfile link=\"https:\/\/localhost:4443\/owncloud\/index.php\/apps\/files\/?dir=\/Contracts\/Governments&scrollto=Most_wanted_cryptographer_3.jpg\" id=\"56142\"\u003EContracts\/Governments\/Most_wanted_cryptographer_3.jpg\u003C\/file\u003E was downloaded by \u003Cuser display-name=\"NSA\"\u003ENSA\u003C\/user\u003E via the \u003Cparameter\u003Edesktop\u003C\/parameter\u003E client",
    "message_prepared": "",
    "typeicon": "icon-download",
    "datetime": "2016-03-30T11:08:05+00:00",
    "previews": [{
        "link": "\/owncloud\/index.php\/apps\/files\/?dir=\/Contracts\/Governments&scrollto=Most_wanted_cryptographer_3.jpg",
        "source": "\/owncloud\/index.php\/core\/preview.png?file=\/Contracts\/Governments\/Most_wanted_cryptographer_3.jpg&c=3ed6f28f4d0e2e9c8910e44dd14404bc&y=150&x=150",
        "isMimeTypeIcon": false
    }]
},

An idea would be to add a filter in the web portal's activity feed that let's you select from last hour, day, week, month or year.

Since that touches the Activity app's UI, I'll open an issue there to see if it's something which can be implemented. I think it should be possible to get at least a new tab showing only the files which have been downloaded.

[oparoz]

@derekblankmccoy - https://github.com/owncloud/activity/issues/496

[drkmccy]

Thanks @oparoz I think we're on a good path.

[oparoz]

@derekblankmccoy - Well, that request wasn't approved. It's true that if I add a filter allowing a user to list all downloads, then you can just scroll to find the information you need.

[nickvergessen]

Btw if you want to query the API, query the official OCS api only. anything else might change at anytime.

[oparoz]

@nickvergessen - you mean your Activity API v2 can change at any time? https://github.com/owncloud/activity/blob/master/docs/endpoint-v2.md It's versioned, so shouldn't really change for current clients, no?

[nickvergessen]

Only OCS api is stable, the current v2 may or may not become a ocs api in the future, but we are still discussing the format of the output with the clients and w3c comparisons

[oparoz]

Thanks @nickvergessen . I saw the discussion in the issues list, but didn't think it would affect the v2. And good job on Activity :).

@derekblankmccoy - There is less info in the OCS API, but it's actually an advantage as it's almost human readable...

      {
        "id": 1936,
        "subject": "Shared file Contracts/Governments/4G pineapple.jpg was downloaded by BND via the Web client",
        "message": "",
        "file": "/Contracts/Governments/4G pineapple.jpg",
        "link": "https://localhost:4443/files/index.php?dir=%2FContracts%2FGovernments",
        "date": "2016-03-30T15:06:57+00:00"
      },

[oparoz]

So, afaik, @gig13's requirements are now met by what I've developed so far:

• [x] User A is notified that User B has downloaded the file and via which method - Web, desktop client or mobile
• [x] It would also be desired to have this tracked in an audit trail like the Activity or via a reporting mechanism

@derekblankmccoy - What would you and your partners need in order to be able to close this issue? I've looked at adding an export button in Activity, but since the information is lazy loaded, it will almost never produce what people need. Scripts, etc. are possible, but then it requires a privileged access to the ownCloud installation.

[drkmccy]

@oparoz The copy function in the client is good, but it needs more control for it to be functional. It copies the data in comma separated format like this:

date and time in a non workable format, file, action in a sentence written format Fri Mar 1 07:54:20 2016 GMT,docuements/memo.pdf,Shared file Documents/memo.pdf was downloaded by oparoz via the web client

The info is there, but I can't really do anything with it. If it was like this:

date,time,file,type,user,action,client 01/04/2016,07:54:20,documents/memo.pdf,shared,oparoz,downloaded,web

That would be useful because I could filter the data and do something with it. Remember my scenario of the 500 students? There is no practical way of finding who didn't download the invite without this sort of filtering. I mean unless the activity app had fancy filtering, sorting, and querying, but that would be asking too much in my opinion. A simple extract so I could filter and sort myself is all I would need.

I guess @nickvergessen's API would solve that issue.

Annoyingly, the desktop client has the copy function, but it doesn't go back as far enough as the Activity app shows on the web client. The web client is annoying itself because I have to scroll down, wait and repeat several times, but at least I can go back a whole year. The problem is I can't export that data easily like the copy button on the client. So the web has the data, the desktop client has the functionality. We would need a "best of both": either an export button on the web client or have the data on the desktop client go back as far as the web client can, or both, lol.

That's before considering archiving the data after its copied. There is no way to select how long to go back before exporting the data, so there will be data redundancy. If I could say "show me from 01/03/2016 - 31/03/2016" I could export that and save it as "March 2016.xls". However, this issue could be solved if the data was copied with a usable date format, as I mentioned above. It would copy the whole thing, but I would filter 90% of it out to only show me the last month and I would archive that separately.

[drkmccy]

I would also respectfully argue that:

It would also be desired to have this tracked in an audit trail like the Activity or via a reporting mechanism.

Has not been met as no "audit" can be done if the data cannot be worked with. "A reporting mechanism" also implies that a "report" (filtered query) can be run.

However, the functionality you added is a massive improvement.

[oparoz]

Has not been met as no "audit" can be done if the data cannot be worked with. "A reporting mechanism" also implies that a "report" (filtered query) can be run.

@derekblankmccoy - Well, the way I read that sentence (and understood from @jospoortvliet's statements) is that Activity is the audit trail. Everything is in the DB and can be queried by any data mining tool, but that's for @gig13 to clarify. Reporting was an alternative proposition in the OP.

But my goal is to close this issue as described in the OP, make the modifications useful and meet the sponsor's requirements, so there has to be a way to easily retrieve the information and neither the client's copy function (Activity API needs to be redesigned, weird timestamps) nor the activity stream (lazy loading, no search) are acceptable solutions.

have the data on the desktop client go back as far as the web client can

I think it will require the new Activity API, but make sure to open an issue in the client repository if it doesn't exist already.

The big problem right now regarding building a useful export functionality is that the OCS API can't search for information. The only thing you can ask for is the row from which to start the query and the number of rows to return. So basically, you could dump the last 1000 rows at a regular interval and merge the new batch with what you have... That's not great.

I see 2 ways forward:

1. Wait for the new OCS API to land and use that either in the client or the web GUI or both
2. Design a custom way to retrieve the information, outside of Activity and not using the OCS API

Any other suggestions? Any preference?

[drkmccy]

Well the info is there, just not usable.

If the new API is coming soon, I don't mind waiting for that. If its pie in the sky, we could make something that takes the data and offers it in a usable manner.

So basically, you could dump the last 1000 rows at a regular interval and merge the new batch with what you have...

I don't mind that..... If i could select a date range and have it spit it out in workable values.

[oparoz]

If the new API is coming soon, I don't mind waiting for that.

It's targeting 9.1, which means that if you wait for 9.1.1 or 9.1.2 before using it in production, you should be able to get what you need in 4-5 months. https://github.com/owncloud/activity/issues/494

BTW, my changes will also land in the same version and probably won't be backported, so you should be getting everything at the same time. I you wanted something for 9.0, then a different strategy should be picked.

I don't mind that..... If i could select a date range and have it spit it out in workable values.

You can't. You can only say: Give me rows 100 to 200.

[drkmccy]

Ok so If I understand correctly the new API offers usable data. Maybe all we need then is an automatic logger that takes that data and dumps it daily into a yymmdd.csv?

I could then merge the days I need by using the command prompt.

[bboule]

Also... If you are someone that uses MS Excel you can actually connect to the database table directly (old versions of excel need Power Query which is a free add-on) new versions of excel allow you to connect directly to the database, and then allow you to pivot the table in an excel sheet...

Just another option!!!

[drkmccy]

@oparoz, whats that?

[oparoz]

@derekblankmccoy - That's what you can export to using phpMyAdmin

[drkmccy]

oh right I see, the action doesn't seem to be there, I assume you queried for shared file downloads?

[oparoz]

oh right I see, the action doesn't seem to be there, I assume you queried for shared file downloads?

Exactly. All the data for the month of March 2016.

[oparoz]

@derekblankmccoy - If you have a test instance and want to give all this a try, I can probably send you a patch and the queries.

[drkmccy]

Ok, that sounds good. If I can pull a report with the info needed, we can close this!

[oparoz]

@derekblankmccoy - OK, let me know when you have an instance updated with the latest commits from master.

[drkmccy]

Im setting up a brand new instance, I'll be back when its done

[drkmccy]

It looks like phpmyadmin is no go, but we are going to run a custom script. What is the query?

[oparoz]

@derekblankmccoy Any specific hurdles in setting up the test instance?

[drkmccy]

Im having issues now. Every time I share a folder with a user, they can see the folder, but if I log out, the shared folder disappears from their ownCloud..... oh dear.

[oparoz]

Just saw that the feature freeze for 9.1 is tomorrow, the 24th. I doubt the solution can be tested by then, so it will only be delivered in November, for 9.2.

[drkmccy]

Oh, it does work, It's just the retreival of the logs I could not do.

[oparoz]

Oh, it does work.

Does that mean you have a test instance on which to test the patches?

[drkmccy]

correct

[oparoz]

@derekblankmccoy - Events triggered when files are shared have been accidentally removed. Resolution is tracked in #24937. Once that's fixed, I'll send you patches created against the latest master.

[oparoz]

@derekblankmccoy - We're back in business and there are still 12 days before the feature freeze, so this could still make it in if reviewed in time. Make sure you pull the latest commits from master and I'll post a patch you can apply to finally test this.

[drkmccy]

Just manged to get it all patched. It seems to work at first glance!

[oparoz]

That's good news :). After a few days, you should have some data to extract via the SQL commands I pasted earlier.

[drkmccy]

Cool, so when is it being implemented into a proper release?

[oparoz]

Probably in 9.2 in November. The first step is for you to validate the fact that it works for you as expected, then I can submit the patches to the repositories which need modifications.

[PVince81]

There was no time left to develop this for 9.1, moving to 9.2.

[oparoz]

@derekblankmccoy - Can I send my changes to the project? Does that meet your requirements? If this gets merged quickly, I can probably send you patches for versions down to 9.0.

[drkmccy]

Tested and working. I couldn't manage to get LDAP working. @oparoz is it safe to assume it will track LDAP users just as well?

[drkmccy]

Just found an issue with this. It doesn't seem to work with shared folders.

[oparoz]

Has been implemented in an app: https://github.com/nextcloud/files_downloadactivity

[PVince81]

From what I see the app is not compatible with ownCloud, so we should keep this ticket open for a solution compatible with ownCloud.