If we still want to exploit our Nextcloud installation more and we want to know how much each user is consuming… This is your post! It's going to look great because we're going to see it directly from our Grafana!

We'll use a script called 'user usage report'’ that we can download from the Nextcloud Apps website, we will download the version that corresponds with our Nextcloud installation, in this post we use Nextcloud 13. We leave it in the App directory within our Nextcloud server, for this, Something like this will help us, In the end we access the directory where the OCC binary is and make it executable:

[SourceCode]MV user_usage_report-1.0.1.tar.gz /VAR/www/NextCloud/Apps/
cd /var/www/nextcloud/apps
tar zxfv user_usage_report-1.0.1.tar.gz
RM user_usage_report-1.0.1.tar.gz
cd /var/www/nextcloud
sudo chmod +x occ[/SourceCode]

After that, we can try directly to see what this wonderful script gives us. To see the admin user data usage report we can run the following command and see its output:

[SourceCode]sudo -u www-data ./occ usage-report:generate admin
"admin","2018-08-20T20:35:50+00:00",-2,7518695,20,0,0,0[/SourceCode]

Ole! we already see in some way the use of a single user, Not bad, And if we want to see everyone? Well, removing the user 🙂

[SourceCode]sudo -u www-data ./occ usage-report:generate
"admin","2018-08-20T20:36:32+00:00",-2,7518695,20,0,0,0
"88133B49-A6A5-4035-83EB-3193BDD19E3B","2018-08-20T20:36:32+00:00",-2,7952664,32,1,0,16
"57D0CD27-A319-4A36-92D3-E541B7DD84C9","2018-08-20T20:36:32+00:00",-2,"",,0,0,0
"5BA5460C-FF2F-4DAC-8C1F-5427729DDD4D","2018-08-20T20:36:32+00:00",-2,7435791,12,0,0,0
"61F42891-B220-43AB-9568-4390753DDBF5","2018-08-20T20:36:32+00:00",-2,"",,0,0,0
"272407CC-6361-49F4-A3B5-F8A26FDDDE18","2018-08-20T20:36:32+00:00",-2,"",,0,0,0
"755784D0-F0AE-4DF9-BD88-E3A5B5BDD374","2018-08-20T20:36:32+00:00",-2,7435791,12,0,0,0
"BA886970-5134-45FD-8D42-D11DCDDE57A","2018-08-20T20:36:32+00:00",-2,"",,0,0,0
"A6E28617-BB75-4080-A7B3-9F22E4DD184A","2018-08-20T20:36:32+00:00",-2,"",,0,0,0
"3009D13B-0236-48D4-B771-604932DD47F1","2018-08-20T20:36:32+00:00",-2,"",,0,0,0[/SourceCode]

What I get out of it are the identifiers of the Nextcloud users, I have them synchronized with the Active Directory. All this can be exported in CSV and stop here and the post. You make the graphs as you want with an Excel and that's it. Since what we see is a CSV format with the following fields:

• User ID.
• Date of the report.
• If you have quotas enabled (-3 It would be unlimited, -2 Not fixed).
• Space consumed in your folder.
• Number of files on your home page.
• Number of Items Shared.
• Number of files created.
• Number of files viewed or downloaded.

In the case that concerns us, If you know where I'm going to shoot… my intention is to create a script that exports this information to a MySQL server, saving all this data in a table and then working with Grafana in a simpler way.

In my case I create this script that will do the job for me, I call it “/root/nextcloud_uso_a_mysql.sh” with the following content:

[SourceCode]cd /var/www/nextcloud/
sudo -u www-data ./occ usage-report:generate > /TMP/nextcloud_uso1.csv
aw '{GSUB(/\"/,"")};1’ /TMP/nextcloud_uso1.csv > /TMP/nextcloud_uso.csv
sed -i -e 's/88133B49-A6A5-4035-83EB-3193B4DD9D3B/Hector Herrero/g’ /TMP/nextcloud_uso.csv
sed -i -e 's/755784D0-F0AE-4DF9-BD88-E3A5B5DDD74/Seila Fernandez/g’ /TMP/nextcloud_uso.csv
sed -i -e 's/A6E28617-BB75-4080-A7B3-9F22E4DD8DA/David Guerrero/g’ /TMP/nextcloud_uso.csv
SCP /TMP/nextcloud_uso.csv pi@192.168.1.197:/Home/PI
mysqlimport -h SERVIDOR_MYSQL -uroot -pPASSWORD NOMBRE_BD –fields-terminated-by=',’ /home/pi/nextcloud_uso.csv[/SourceCode]

As we can see, I can hardly export the data to a CSV, I remove all the double quotation marks that bother him, and replace the identifiers that spit me out with the correct names of my AD users. In the end I upload the csv file to the MySQL server with scp and import it, it will take the table name from the name of the file we imported. We save the script and make it executable 🙂

And we can directly program it in cron, in my case, if it is run once a day it is enough for me and I have more than enough, So in this example I program it for the 00:01 I would say. Every day you will import new data into the MySQL table!

[SourceCode]crontab -e
1 0 * * * /root/nextcloud_uso_a_mysql.sh[/SourceCode]

The table I'm using in MySQL is called 'nextcloud_uso’ and is composed of user, Son of a, quota, space, Files, Shared, Created, Moved and Date, as you will see these are more than juicy data to then deal with them with Grafana… I leave you the script in case you want to create the table with the same structure as me:

[SourceCode]CREATE TABLE 'nextcloud_uso' (
'user' CHAR(40) NULL DEFAULT NULL,
'fetxa' CHAR(10) NULL DEFAULT NULL,
'quota' FLOAT NULL DEFAULT NULL,
'space' FLOAT NULL DEFAULT NULL,
'files' FLOAT NULL DEFAULT NULL,
'shared' FLOAT NULL DEFAULT NULL,
'created' FLOAT NULL DEFAULT NULL,
'moved' FLOAT NULL DEFAULT NULL,
'date' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COLLATE='latin1_swedish_ci’
ENGINE=InnoDB
ROW_FORMAT=COMPACT
;[/SourceCode]

And now all that's left is to visualize the data! Here are a couple of examples, where in the first, With a little cheese we will see the data that is currently using our data, in a graphic way and with a quick glance we will know who is the scoundrel who sucks all the space 🙂 And on the right I leave you a graph of how the use of data of our users would grow. But watch out! That in the table we have more curious facts! How to know who shares the most, Who works the most, etc, etc…

I leave you a hint of the queries I used in Grafana so that it looks the same, Or wow, you better! On a dashboard, Create:

• Cheese or Pie Chart: It would be a matter of adding the series that interest us against our MySQL Data Source, example of one of them:

[SourceCode]Select Date as time_sec, User as metric, espacio as value from nextcloud_uso where user = 'Hector Herrero’ Order by Date DESC LIMIT 1[/SourceCode]

• Gráfica o Graph: The same, add the series that we want to paint, example:

[SourceCode]SELECT espacio as value, "Hector Herrero" as metric, UNIX_TIMESTAMP(date) as time_sec FROM nextcloud_uso WHERE $__timeFilter(date) and user = 'Hector Herrero’ Order by Date Off[/SourceCode]

If you're a little lost with Grafana and need to know how to install it, Check out This post that can help you, then at most you will have to make a connector or Data Source against your MySQL server! Well, I hope you liked it! The one who does not have his environment under control is because he does not want to!!! There I leave it! Pimp, pimp…