Site Resources Quota calculations

[Jeff F]

Is it possible to create a database report that would return a list of all sites within Sakai and the total storage used by each. I looked around and can't find this data stored within any of Sakai's database tables. How is space used calculated?

Resources -> Check Quota returns:

This site is currently using 1% (34.7 MB ) of its 2 GB quota.

[Brian Jones]

We wrote a query to do exactly this. While it was written for Oracle,
MySQL syntax should be similar. You'll need to input the same list of
site IDs into both IN clauses. The query will produce two duplicate
columns of site IDs, but you can just ignore one.

SELECT *
FROM (
SELECT DISTINCT(site_id)
FROM sakai_site
WHERE site_id IN ()
) a
LEFT OUTER JOIN (
SELECT s.site_id, TRUNC(SUM(c.file_size) / 1024 / 1024, 2) AS
megabytes
FROM sakai_site s
JOIN content_resource c
ON c.resource_id LIKE CONCAT(CONCAT('/group/', s.site_id), '/%')
WHERE s.site_id IN ()
GROUP BY s.site_id
) b
ON a.site_id = b.site_id
ORDER BY a.site_id asc;

Hope this helps you!
--
Brian Jones
Programmer/Analyst
Western Technology Services
Western University
bjon...@uwo.ca

[Jeff F]

Thank you for the SQL!  A very slight modification got it working for me. I still would like to calculate a percentage used.  Do you have any idea where the actual site quota is stored?  We have some sites that use more than the default in the configuration.

[Brian Jones]

If you're referring to the max size per site, this is set in
sakai.properties. See "content.quota".

Cheers,

--
Brian Jones
Programmer/Analyst
Western Technology Services
Western University

(519) 661-2111 x86969
bjon...@uwo.ca