{"id":87834,"date":"2020-07-11T01:42:00","date_gmt":"2020-07-10T23:42:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam"},"modified":"2020-07-11T01:42:00","modified_gmt":"2020-07-10T23:42:00","slug":"kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","title":{"rendered":"Where did the space on the Confluence server go? Or Confluence statistics on attachments","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Have you ever faced a situation where the Confluence server ran out of space, and you don't know who is uploading the most attachments?<\/p>\n

<\/p>\n

To find this out, access to the PostgreSQL database of Confluence is necessary.<\/p>\n

<\/p>\n

Using an SQL query to the PostgreSQL database, you can obtain useful statistics about Confluence.<\/p>\n

<\/a><\/noindex><\/p>\n

Total size of all attachments in Confluence:<\/h3>\n

<\/p>\n

SELECT s.spaceid,\n       s.spacename,\n       sum(LONGVAL)\nFROM contentproperties c\nJOIN content co ON c.contentid = co.contentid\nJOIN spaces s ON co.spaceid = s.spaceid\nWHERE c.contentid IN\n    (SELECT contentid\n     FROM content\n     WHERE contenttype = 'ATTACHMENT')\n  AND c.propertyname = 'FILESIZE'\nGROUP BY s.spaceid\nORDER BY SUM DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Output (spacename replaced):<\/p>\n
<\/p>\n
\"Where<\/p>\n
<\/p>\n
Pages with the most historical versions over time:<\/h3>\n
<\/p>\n
SELECT title,\n       MAX(VERSION)\nFROM content\nWHERE contenttype = 'PAGE'\nGROUP BY title\nORDER BY 2 DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Output:<\/p>\n
<\/p>\n
\"Where<\/p>\n
<\/p>\n
The largest attachment files in your Confluence instance<\/h3>\n
<\/p>\n
SELECT DISTINCT c.contentid,\n                c.title AS attachmentTitle,\n                u.username AS uploadedBy,\n                co.title AS pageTitle,\n                cn.longval AS bytes\nFROM CONTENT AS c\nJOIN USER_MAPPING AS u ON u.user_key = c.creator\nJOIN CONTENT AS co ON c.pageid = co.contentid\nJOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid\nWHERE c.contenttype = 'ATTACHMENT'\n  AND cn.longval IS NOT NULL\nORDER BY cn.longval DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Output:<\/p>\n
<\/p>\n
\"Where<\/p>\n
<\/p>\n
Number of pages in the trash and total size of pages in the trash by space:<\/h3>\n
<\/p>\n
\nSELECT Count(content.contentid) AS number_of_trashed_pages,\n       Pg_size_pretty(SUM(Pg_column_size(bodycontent.BODY))) AS trash_total_size,\n       spaces.spacename AS space_name\nFROM bodycontent\nINNER JOIN content ON (content.contentid = bodycontent.contentid)\nINNER JOIN spaces ON (content.spaceid = spaces.spaceid)\nWHERE bodycontent.contentid IN\n    (SELECT contentid\n     FROM content\n     WHERE content_status = 'deleted'\n       AND contenttype = 'PAGE')\nGROUP BY space_name\nORDER BY trash_total_size\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Output:<\/p>\n
<\/p>\n
\"Where<\/p>\n
<\/p>\n
Total size of attachments uploaded by each user on all pages<\/h3>\n
<\/p>\n
SELECT u.lower_username,\n       sum(cp.longval) AS \"size\"\nFROM content c1\nJOIN content c2 ON c1.contentid = c2.pageid\nJOIN user_mapping u ON c1.creator=u.user_key\nJOIN contentproperties cp ON c2.contentid = cp.contentid\nWHERE c2.contenttype='ATTACHMENT'\nGROUP BY u.lower_username\nORDER BY sum(cp.longval) DESC\nLIMIT 5;<\/code><\/pre>\n
<\/p>\n
Output:<\/p>\n
<\/p>\n
\"Where<\/p>\n
<\/p>\n
P.S. Please supplement the post with useful SQL queries for Confluence<\/p>\n
Source: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0423 \u0432\u0430\u0441 \u0431\u044b\u0432\u0430\u043b\u043e \u0442\u0430\u043a\u043e\u0435 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence \u0437\u0430\u043a\u043e\u043d\u0447\u0438\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e, \u0430 \u0432\u044b \u043d\u0435 \u0437\u043d\u0430\u0435\u0442\u0435 \u043a\u0442\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u0432\u0441\u0435\u0433\u043e \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u0435\u0442 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439? \u0427\u0442\u043e\u0431\u044b \u0443\u0437\u043d\u0430\u0442\u044c \u044d\u0442\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c \u0434\u043e\u0441\u0442\u0443\u043f \u043a \u0432 \u0411\u0414 PostgreSQL confluence. \u0421 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u043c \u043a \u0411\u0414 PostgreSQL \u043c\u043e\u0436\u043d\u043e \u0443\u0437\u043d\u0430\u0442\u044c \u043f\u043e\u043b\u0435\u0437\u043d\u0443\u044e \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u043f\u043e Confluence. \u041e\u0431\u0449\u0438\u0439 \u0440\u0430\u0437\u043c\u0435\u0440 \u0432\u0441\u0435\u0445 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439 \u0432 Confluence: SELECT s.spaceid, s.spacename, sum(LONGVAL) FROM contentproperties c JOIN […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":87835,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-87834","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Where did the space go on the Confluence server? Or Confluence statistics on attachments | ProHoster","description":"Have you ever faced a situation where the Confluence server ran out of space, and you don't know who is uploading the most attachments? To find this out, access to the PostgreSQL database of Confluence is necessary. Using SQL.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0443\u0434\u0430 \u0434\u0435\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence? \u0418\u043b\u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 Confluence \u043f\u043e \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u043c | ProHoster","og:description":"\u0423 \u0432\u0430\u0441 \u0431\u044b\u0432\u0430\u043b\u043e \u0442\u0430\u043a\u043e\u0435 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 Confluence \u0437\u0430\u043a\u043e\u043d\u0447\u0438\u043b\u043e\u0441\u044c \u043c\u0435\u0441\u0442\u043e, \u0430 \u0432\u044b \u043d\u0435 \u0437\u043d\u0430\u0435\u0442\u0435 \u043a\u0442\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u0432\u0441\u0435\u0433\u043e \u0437\u0430\u0433\u0440\u0443\u0436\u0430\u0435\u0442 \u0432\u043b\u043e\u0436\u0435\u043d\u0438\u0439? \u0427\u0442\u043e\u0431\u044b \u0443\u0437\u043d\u0430\u0442\u044c \u044d\u0442\u043e \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c \u0434\u043e\u0441\u0442\u0443\u043f \u043a \u0432 \u0411\u0414 PostgreSQL confluence. \u0421 \u043f\u043e\u043c\u043e\u0449\u044c\u044e SQL.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/kuda-delos-mesto-na-servere-confluence-ili-statistika-confluence-po-vlozheniyam","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-10T23:42:00+00:00","article:modified_time":"2020-07-10T23:42:00+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"87834","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:42:28","updated":"2022-09-30 17:43:09","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/87834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=87834"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/87834\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/87835"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=87834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=87834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=87834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}