您是否曾經遇到過 Confluence 伺服器空間不足的情況,而且您不知道誰上傳的附件最多?
要找到答案,您需要存取 PostgreSQL confluence 資料庫。
使用針對 PostgreSQL 資料庫的 SQL 查詢,您可以找到 Confluence 上的有用統計資料。
Confluence 中所有附件的總大小:
SELECT s.spaceid,
s.spacename,
sum(LONGVAL)
FROM contentproperties c
JOIN content co ON c.contentid = co.contentid
JOIN spaces s ON co.spaceid = s.spaceid
WHERE c.contentid IN
(SELECT contentid
FROM content
WHERE contenttype = 'ATTACHMENT')
AND c.propertyname = 'FILESIZE'
GROUP BY s.spaceid
ORDER BY SUM DESC
LIMIT 5;
輸出(空間名被替換):
包含最多歷史版本的頁面組合:
SELECT title,
MAX(VERSION)
FROM content
WHERE contenttype = 'PAGE'
GROUP BY title
ORDER BY 2 DESC
LIMIT 5;
結論:
Confluence 實例中最大的附件文件
SELECT DISTINCT c.contentid,
c.title AS attachmentTitle,
u.username AS uploadedBy,
co.title AS pageTitle,
cn.longval AS bytes
FROM CONTENT AS c
JOIN USER_MAPPING AS u ON u.user_key = c.creator
JOIN CONTENT AS co ON c.pageid = co.contentid
JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid
WHERE c.contenttype = 'ATTACHMENT'
AND cn.longval IS NOT NULL
ORDER BY cn.longval DESC
LIMIT 5;
結論:
購物籃中的頁數以及每個空間的購物籃中的頁面總大小:
SELECT Count(content.contentid) AS number_of_trashed_pages,
Pg_size_pretty(SUM(Pg_column_size(bodycontent.BODY))) AS trash_total_size,
spaces.spacename AS space_name
FROM bodycontent
INNER JOIN content ON (content.contentid = bodycontent.contentid)
INNER JOIN spaces ON (content.spaceid = spaces.spaceid)
WHERE bodycontent.contentid IN
(SELECT contentid
FROM content
WHERE content_status = 'deleted'
AND contenttype = 'PAGE')
GROUP BY space_name
ORDER BY trash_total_size
LIMIT 5;
結論:
每個使用者在所有頁面下載的附件總大小
SELECT u.lower_username,
sum(cp.longval) AS "size"
FROM content c1
JOIN content c2 ON c1.contentid = c2.pageid
JOIN user_mapping u ON c1.creator=u.user_key
JOIN contentproperties cp ON c2.contentid = cp.contentid
WHERE c2.contenttype='ATTACHMENT'
GROUP BY u.lower_username
ORDER BY sum(cp.longval) DESC
LIMIT 5;
結論:
PS 為這篇文章補充有用的 Confluence SQL 查詢
來源: www.habr.com