Discover last access of old user on ownCloud

Good morning,
i have to migrate an old ownCloud environment to a new server but i would like to reduce the amount of data assigned to users and migrate only the active users. I have a lot of old user that i think no more use the cloud to store the data. Do you have any suggestion to check the last access of the users?

Thanks in advance for your support.
Best regards.
Luigi

It conceivably could be returned by a SQL query, but without reviewing the database structure, I don’t know the exact query required. You should be able to come up with something following some database analysis.

Have you considered sending an email explaining that you will only be migrating the users that reply?

1 Like

Hi, are you talking about this query?

MariaDB [owncloud]> select m.user_id, fc.size from oc_mounts m, oc_filecache fc, oc_storages s where m.mount_point=concat(‘/’, m.user_id, ‘/’) and s.numeric_id=m.storage_id and fc.storage=m.storage_id and fc.name=‘files’;
±--------±--------+
| user_id | size |
±--------±--------+
| admin | 163 |
| user1 | 4774179 |
| user2 | 1571107 |
±--------±--------+
3 rows in set (0.00 sec)

Have you considered sending an email explaining that you will only be migrating the users that reply? This can be anther good solution.

Thank you

Something like this?

SELECT user_id, email FROM oc_accounts WHERE last_login < $THRESHOLD
2 Likes

I have just discovered that Owncloud has been configured on sqlite3 db so i am looking for the command (query) to find the same results

I assume, the query is the same.

1 Like

The following query should work with sqlite:

select user_id, strftime('%d-%m-%Y', datetime(last_login, 'unixepoch')) from oc_accounts as last_login order by last_login desc;

Though with recent ownCloud versions, you can get a list of users that have not logged in for a certain number of days with the occ user:inactive command, without messing with the database.
I don’t seem to be allowed to post a link, you can find more information in the admin manual under Configuration - Server - OCC Command.

In any case be aware that even if a user has not logged in for a long time, they could have shared data with other users or via a link that is still actively used.

2 Likes

Hi,
With the occ command i managed to find these users:

[root@server~]# sudo -u apache /var/www/html/owncloud/occ user:inactive 3000

  • 0:
    • uid: …
    • displayName: …
    • inactiveSinceDays: 3255
  • 1:
    • uid: …
    • displayName: …
    • inactiveSinceDays: 3249

user:inactive - Reports users who are known to ownCloud, but have not logged in for a certain number of days.

Could i consider these results valid? 3000 days are 8 years. Do you think that there are also other ways to investigate the shared data?

Do you know a way to disable all x inactive users with a command?

thank you very much for your help.
Luigi

1 Like

While 3000 days is a long time, the report normally should be correct.

If you run the query I provided in my last post, you should be able to see the last login dates as well to cross check, ideally with some users where you know when they were last active.

I don’t know of a single command to disable inactive users, but you should be able to write the list of inactive users to a file and then use this to call occ user:disable.

For example, assuming that jq is installed on your machine:

# occ user:inactive 30 --output=json | jq -r ".[].uid" | tee users.txt
admin
test

# for USER in $(cat users.txt); do occ user:lastseen $USER; done
admin`s last login: 01.10.2022 07:41
test`s last login: 01.06.2020 16:02

Here I used user:lastseen as a safe test, but user:disable should work just the same.

All of that assumes that you have local users only that are not synced from e.g. an LDAP / Active Directory.

Disabling the long inactive users may not do that much though, data they have shared will still be accessible.

If you really wanted to test what happens if the data is no longer accessible, you could set the permissions in the filesystem in such a way that ownCloud is no longer able to access them e.g. using chmod 000.
I have done that for testing, but note that this is not supported officially by ownCloud and may lead to strange errors in the log.

The easiest option may be to just not migrate the users that you are reasonably sure are not needed anymore initially, but make sure that you keep the old server for some time and/or have some way to restore users from backup in case some users do complain about not being able to access some data anymore.
Whether that is feasible of course depends on your use case, but that’s probably how I would do it.

Good luck!

2 Likes