Difference between revisions of "CentOS 8.x Owncloud direct DB queries"
m |
m |
||
| Line 50: | Line 50: | ||
===Number of files deleted by a user within given time periods under a specific folder=== | ===Number of files deleted by a user within given time periods under a specific folder=== | ||
To query no. of files deleted by saurabh within timestamps | To query no. of files deleted by saurabh within timestamps inside a given folder use: | ||
Example values: | Example values: | ||
; user: saurabh | ; user: saurabh | ||
| Line 63: | Line 63: | ||
select * from oc_activity where user='saurabh' and affecteduser='saurabh' and timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%'; | select * from oc_activity where user='saurabh' and affecteduser='saurabh' and timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%'; | ||
</pre> | </pre> | ||
'''See [[Convert date to different format using date command]] for date/time conversion to epoch and back.''' | |||
Latest revision as of 07:52, 15 October 2022
Home > CentOS > CentOS 8.x > Desktop tools or applications > Owncloud > Direct DB queries
Connecting to database
For bitnami installations database settings can be checked at '/opt/owncloud-<version>/apps/owncloud/htdocs/config/config.php'. Note at least following values:
- 'dbname' => 'bitnami_owncloud',
- 'dbuser' => 'bn_owncloud',
- 'dbpassword' => '<secret>',
Then open connection to bitnami owncloud mysql database using:
/opt/owncloud-10.5.0-1/mysql/bin/mysql -u bn_owncloud -h 127.0.0.1 -p bitnami_owncloud
oc_filecache
This table has information about current files known to owncloud. Important column in this table is:
- path
- Stores path of file with respect to user folder (/opt/owncloud-<version>/apps/owncloud/data/<username>/)
- path_hash
- Md5 hash of path stored in path. This can be used to quickly search for a given path using indexes. See "show indexes from oc_filecache;" as it shows BTREE index on path_hash but no index on path.
- permissions
- Most likely related to filesystem permissions. I have seen value 31 when folder can be updated (owned by daemon:daemon) and 17 when it was read-only and owned by root.
Number of files in a given folder
We can check how many files/folders are there in any given path using queries such as:
select count(*) from oc_filecache where path like 'files/users/saurabh/%';
where path of file as per owncloud is 'users/saurabh/*'. We need to prepend paths with 'files/' while looking at this table.
Number of files of given type in a given folder
Similarly to find no. of pdf files within a given folder:
select count(*) from oc_filecache where path like 'files/users/saurabh/%.pdf';
oc_activity
This table has information of past 1 year of activity provided activity app ( https://marketplace.owncloud.com/apps/activity ) is installed and enabled. Once that is done we can see activity from web UI or through desktop client also. However, for complex queries across the system for specific time periods, it is easier to query the DB directly. This table has following important columns:
- timestamp
- Time when the activity was performed. To convert it to / from human readable formats refer Date
- type
- This is either 'file_created', 'file_changed' or 'file_deleted'
- user
- User who has performed given change
- affecteduser
- If a shared file is changed then this contains the name of other user affected by this change. If the file is shared by many users, many repeated entries with different usernames in affecteduser can be seen in the table
- subject
- This is 'changed_self' if the file is changed by same user. In this case you might see same values for user, affecteduser. If the file was changed by some other user you can here see 'changed_by'
- file
- Path of file. This is with respect to 'user' in user column as each user might have the same file at different paths
Number of files deleted by a user within given time periods under a specific folder
To query no. of files deleted by saurabh within timestamps inside a given folder use: Example values:
- user
- saurabh
- after timestamp
- 1611513000 (2021-01-25 00:00:00)
- before timestamp
- 1612290600 (2021-02-03 00:00:00)
- path
- /saurabh/workspace/
select count(*) from oc_activity where user='saurabh' and affecteduser='saurabh' and timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%';
To see these files use:
select * from oc_activity where user='saurabh' and affecteduser='saurabh' and timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%';
See Convert date to different format using date command for date/time conversion to epoch and back.
To query no. of files deleted by various users in a given folder
To query no. of files deleted by various users in a given folder in entire 1 year of database logs use:
select count(*),user from oc_activity where affecteduser='admin' and type='file_deleted' and file like '/projects/%' group by user;
here files were owned by admin user and shared with other users. The user deleting the file could be anyone else and is listed in the output under user table. The path of file as per admin user is /projects/*.
owncloud.log
Note that it is possible that a request for deleting a large file/folder is received via desktop client / web interface but that request takes too much time to complete (Delete large folder) and hence request times out or ends due to memory error, etc. In such cases file would get deleted at filesystem level but not at DB level. The mismatch issues between DB and filesystem can be solved using 'occ files:scan' Refer CentOS 7.x Owncloud file cache and sharing. But from audit purposes it wont be clear which user performed this action as the oc_activity table will not have corresponding file_deleted activities log. To find out about such actions we can have a look at '/opt/owncloud-<version>/apps/owncloud/data/owncloud.log'
Here in log file you can see parameters such as
- method
- PUT / GET / DELETE etc.
- remoteAddr
- Remote IP from which action was performed
- user
- User who performed this action
- URL
- URL or path for which action was performed.
Hence to search for files deleted under projects folder we can try:
grep DELETE owncloud.log | grep '%projects%' | less
and scroll till the timestamp when file could have been deleted. Then you might find logs such as:
{"reqId":"6423f2fb-3f66-4442-a6fa-0c79228ba113","level":3,"time":"2021-01-22T05:11:12+00:00","remoteAddr":"136.185.194.16","user":"user1@example.com", \
"app":"PHP","method":"DELETE","url":"\/owncloud\/remote.php\/dav\/files\/user1@example.com\/projects\/project1", \
"message":"Allowed memory size of 536870912 bytes exhausted (tried to allocate 4096 bytes) at \/opt\/owncloud-10.0.10-4\/apps\/owncloud\/htdocs\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOStatement.php#105"}
indicating user user1@example.com made request to delete folder projects/project1 at 2021-01-22 at 05:11:12 GST time (convert to local time zone appropriately) and the deletion failed due to memory exhaustion. In such cases folder projects/project1 gets deleted from filesystem but not at DB making it hard to audit / diagnose / troubleshoot.
Home > CentOS > CentOS 8.x > Desktop tools or applications > Owncloud > Direct DB queries
